MAX function not working?

[dusanv]

Consider the following table:


SSN Record Name Member EffDate ENREFFDT
123456789 GCRDEVNT WORK 6/6/2002 20020603
123456789 GCRDEVNT WORK 6/8/2002 20020608
555555555 GCRDEVNT WORK 5/8/2002 20020603
555555555 GCRDEVNT WORK 6/3/2002 20020608

I want to have a query that will output only records with the most recent EffDate, like this:


SSN Record Name Member EffDate ENREFFDT
123456789 GCRDEVNT WORK 6/8/2002 20020608
555555555 GCRDEVNT WORK 6/3/2002 20020608

I've read several older threads on how to do this and the common suggestion was to use the MAX function on the date field and it works well. However, now I need to make my query more sophisticated so that I can specify a date range with query returning a top dated record in that range.

Any way to acomplish this? Simply using MAX doesn't work -- what I'm finding is that if I specify <#6/8/2002# for EffDate criteria, no record will be returned for 123456789 SSN above! It looks like MAX function is performed first and only then the filtering.

As always, any suggestions appreciated.

Dusan

 

[Darrylles]

Hi Dusanv,

If you're saying that you expect records from the selection that you have listed above to be returned with the criteria that you have supplied, then it can't happen.

You have criteria stating: 'give me records EARLIER than 06/08/2002' (<#6/8/2002#), which it will do (leaving out both 12345678 records). It will then apply the MAX filter to those results.

I may have interpreted this wrongly, so I suggest that you create a query that uses the prompt for the date-range - this you can check returns the set of records that you want initially.
Then create a query with your MAX filter that uses the first query as input.

Regards,

Darrylle



&quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot;

 

[dusanv]

Darrylle,

I took out the MAX from query and it did return 6/3/2002 record for 123456789 SSN (because 6/3 IS earlier than 6/8). I still think MAX is executed first and then the results are filtered according to specified criteria. I'm wondering if there is a way to reverse this order of exectuion.

Dusan

 

[JJOHNS]

You need to create a nested query. I've made this mistake before. Set your query up like this:

SELECT SSN, Record Name, Member, EffDate, ENREFFDT
FROM MyTable
WHERE MyTable.ENREFFDT = (SELECT MAX(ENREFFDT) FROM MyTable);

 

[dusanv]

That didn't work either...your query would return only the 6/8 records so filter <6/8/2002 will return null.

The only way I figured how to do this is to:

1. Create a query that will filter according to specified criteria.

2. Create another query that uses the query above and apply MAX to EffDate field.

Dusan