469,307 Members | 2,288 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,307 developers. It's quick & easy.

MS Access 97 Find Duplicates Query w/o the Wizard

I work for a tool & die shop, and we have an Access DB that captures
work performed on each job we do. Each job could have 5 or more
entries for work performed on it. I am trying to create a query that
will only display the last activity performed, for each job.

Please note, I do NOT have the Find Duplicate Query Wizard, and need
to know how to create the Query with out the wizard.

The database table contains following data:

Job#
Date
Time Started
Time Ended
Employee
Function

Job# Date Start Ended Emp. Function
1001 08/15/04 08:00 12:00 JS Mill
1002 08/15/04 08:00 12:00 ES Cut
1001 08/20/04 09:00 12:00 KJ Grind
1002 08/21/04 08:00 12:00 HS Lathe

I would like the output of the query to be

Job# Date Start Ended Emp. Function
1001 08/20/04 09:00 12:00 KJ Grind
1002 08/21/04 08:00 12:00 HS Lathe

Any help in this would be appreciated.
Nov 13 '05 #1
1 2476
Erik,

Create a query based on your table. Be sure Date comes before Ended. Change
the query to a totals query by clicking on the Sigma (looks like capital E)
button on the toolbar at the top of the screen. Under Date, change Group By
to Max; under Ended, change Group By to Max. Run the query and it will give
you the last activit for each Job#.

BTW, Date is a reserved word and should not be used as a field name. Sooner
or later it will give you a problem.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Erik Schweitzer" <el******@hotmail.com> wrote in message
news:81**************************@posting.google.c om...
I work for a tool & die shop, and we have an Access DB that captures
work performed on each job we do. Each job could have 5 or more
entries for work performed on it. I am trying to create a query that
will only display the last activity performed, for each job.

Please note, I do NOT have the Find Duplicate Query Wizard, and need
to know how to create the Query with out the wizard.

The database table contains following data:

Job#
Date
Time Started
Time Ended
Employee
Function

Job# Date Start Ended Emp. Function
1001 08/15/04 08:00 12:00 JS Mill
1002 08/15/04 08:00 12:00 ES Cut
1001 08/20/04 09:00 12:00 KJ Grind
1002 08/21/04 08:00 12:00 HS Lathe

I would like the output of the query to be

Job# Date Start Ended Emp. Function
1001 08/20/04 09:00 12:00 KJ Grind
1002 08/21/04 08:00 12:00 HS Lathe

Any help in this would be appreciated.

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

30 posts views Thread by Andante.in.Blue | last post: by
1 post views Thread by Phil | last post: by
2 posts views Thread by Phil | last post: by
52 posts views Thread by Neil | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.