Return only greatest date matching criteria

[SerenityNet]

I have a table with say two fields, Item and EffectiveDate. I will have data as follows.

Item   EffectiveDate (mm/dd/yy)
One    04/01/2003 (record 1)
One    04/04/2003 (record 2)
One    04/06/2003 (record 3)
One    04/03/2003 (record 4)
Two    04/05/2003 (record 5)
Two    04/04/2003 (record 6)

I only want to return the records with the most recent (greatest) effective date that is less-than or equal-to today, for each item. If "today" were 04/05/03 (mm/dd/yy), in the above example, I would want to return record 2 for item One and record 5 for item Two. I won't have duplicate effective dates for the same item. How do I do this?

Thanks in advance,
Andrew

 

[Jon K]

Put these in three columns in the query grid of a Totals query (in query design, click on the Totals button so that a Total: row is displayed in the grid):-

Field: Item
Total: Group By
Show: check

Field: EffectiveDate
Total: Max
Show: check

Field: EffectiveDate
Total: Where
Show: uncheck
Criteria: <=Date()


How did you put the Item and EffectiveDate nicely in two blocks in your post? I can't put more than one space between two adjacent words in my posts. Any extra spaces that I put are stripped.

 

[SerenityNet]

thanks

Thanks! I'll give your suggestion a whirl tomorrow morning. I just caught this before going to bed.

As to your question, I use two tricks.
1) Set the font to a proportional font, like "courier new".
2) Instead of using the space-key, use the code of a non-breaking space - i.e. & n b s p ; (without the spaces between the six characters).

 

[Jon K]

Thanks. That's really cool!

 

[SerenityNet]

it kind of works


Well, it almost works. I suppose I should have told you that I have other fields (like Price and EndDate) So as soon as any one of the other fields is different then it shows both records.

If the item code is the same then I only want to show the item code record with the greatest date. I don't care about the other fields.

How can I do this?

 

[SerenityNet]

image of DB

Maybe this will help clarify what I'm trying to do.

Here is an image of what I'm working with and trying to accomplish.

 

[Jon K]

You will need a series of three queries.

I have attached a DB to show the queries. You can open the DB and run Query3.

(The DB is in Access 97 format. If you use Access 2000 or 2002, choose Convert and save as a new name when the DB is opened for the first time.)

 

[SerenityNet]

thank you - but headed home to bed now

Whoa, Jon! Thank you!

I just took a last peek at the forum before heading home and off to bed. I've already downloaded the file and I'll examine it tomorrow. I'll give a full reply then.

Again, thank you.
Andrew

 

[SerenityNet]

Thank You ! ! !

Thank you. Your example led me right through what I needed to do. Of course, I had to make some tweaks, due to ever changing requirements. But with your example as a learning tool, it was easy. - And I never would have figured it out on my own.

Thanks again,
Andrew