Data - Import External Data
 

I may be approaching this problem the wrong way, so if you redirect me to
another solution, that is fine.

I have an Access Database that has 43,000 entries in it that qualify to be
filtered into this problem.

The raw data is a job number - JCJOB and an entry date - DATE.

I need to come up with an Excel query that groups by JCJOB and gives me the
most recent date of those records by that Job number.

To be even clearer, here is what my raw data looks like:

Job No. Date
g05112 02/15/06
g05112 02/23/06
g05112 03/15/06
j06050 01/23/06
j06050 01/31/06
j06050 01/31/06
j06050 02/05/06
j06111 12/15/05
j06111 12/31/05
p06023 01/23/06

I need a query in Excel to print only:

g05112 03/15/06
j06050 02/05/06
j06111 12/31/05
p06023 01/23/06

If I do this report in Seagate Crystal, my 43,000 entries is narrowed down
to 121 and it shows the job number and the maximum date associated with that
partcular job number.

That is what I need this query to display in Excel.

When I have been doing the query in Excel, it is bringing over all 43,000
entires and dumping them on Sheet 2.

Thanks for you help.

Data - Import External Data
 

You can try writing the query in Access first, then import the results of the
query into Excel. You can try out this query in Access:

SELECT [Job No.], MAX([Date])
FROM TableName
GROUP BY [Job No.]

This way you can control your data in Access and just use Excel as a client
to view the data.


"ToferKing" wrote:

I may be approaching this problem the wrong way, so if you redirect me to
another solution, that is fine.

I have an Access Database that has 43,000 entries in it that qualify to be
filtered into this problem.

The raw data is a job number - JCJOB and an entry date - DATE.

I need to come up with an Excel query that groups by JCJOB and gives me the
most recent date of those records by that Job number.

To be even clearer, here is what my raw data looks like:

Job No. Date
g05112 02/15/06
g05112 02/23/06
g05112 03/15/06
j06050 01/23/06
j06050 01/31/06
j06050 01/31/06
j06050 02/05/06
j06111 12/15/05
j06111 12/31/05
p06023 01/23/06

I need a query in Excel to print only:

g05112 03/15/06
j06050 02/05/06
j06111 12/31/05
p06023 01/23/06

If I do this report in Seagate Crystal, my 43,000 entries is narrowed down
to 121 and it shows the job number and the maximum date associated with that
partcular job number.

That is what I need this query to display in Excel.

When I have been doing the query in Excel, it is bringing over all 43,000
entires and dumping them on Sheet 2.

Thanks for you help.