By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,644 Members | 2,439 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,644 IT Pros & Developers. It's quick & easy.

MS Access 97 Find Duplicates Query w/o the Wizard

P: n/a
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
Share this Question
Share on Google+
1 Reply


P: n/a
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.