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

Stop repeat records in query

P: n/a
T
My table has the following fields

ItemNo Name ImageName DateRecd
123 Foo 00123.jog 1/1/06
123 Foo 00FOO.jpg 10/11/06
123 FOO FOO123.jpg 7/5/04
456 bar 00456.jog 6/10/06
456 BAR 00BAR.jpg 7/5/00
456 Bar BAR456.jpg 11/23/06

How would I write / get a query to run and show me, the 2nd item
numbers, the 123 but only the most recent date so 10/11/06 and for
itemno 456 the item dated 11/23/06

Nov 29 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"T" <te****@wideopenwest.comwrote in
news:11**********************@80g2000cwy.googlegro ups.com:
My table has the following fields

ItemNo Name ImageName DateRecd
123 Foo 00123.jog 1/1/06
123 Foo 00FOO.jpg 10/11/06
123 FOO FOO123.jpg 7/5/04
456 bar 00456.jog 6/10/06
456 BAR 00BAR.jpg 7/5/00
456 Bar BAR456.jpg 11/23/06

How would I write / get a query to run and show me, the 2nd
item numbers, the 123 but only the most recent date so
10/11/06 and for itemno 456 the item dated 11/23/06
What do you mean: second item numbers, that's not a column in
the table.

I'm guessing you want

123 10/11/06 00FOO.jpg
456 11/23/06 BAR456.jpg

Create a summation query to group on ItemNo and max on daterecd,
then join that query back to your table on query.ItemNo
=table.ItemNo and query.MaxDateRecd = table.DateRecd


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 30 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.