Max Date doesn't seem to give maximum date

[janieavis]

If I run this query I can see there are 2 records with the dates 10 August 2009 and 24 August 2009:

SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value, dbo_JobMain.VisitDate
FROM dbo_JobMain
WHERE (((dbo_JobMain.BpaMainPKey)="000000000040abeb") AND ((dbo_JobMain.JobDefinitionPKey)="001000000043pobf") AND ((dbo_JobMain.VisitDate) Between #8/1/2009# And #8/31/2009#))
ORDER BY dbo_JobMain.JobDefinitionPKey;

But when I try to group the query to get the maximum date using this:

SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value, Max(dbo_JobMain.VisitDate) AS MaxOfVisitDate
FROM dbo_JobMain
GROUP BY dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value
HAVING (((dbo_JobMain.BpaMainPKey)="000000000040abeb") AND ((dbo_JobMain.JobDefinitionPKey)="001000000043pobf") AND ((Max(dbo_JobMain.VisitDate)) Between #8/1/2009# And #8/31/2009#))
ORDER BY dbo_JobMain.JobDefinitionPKey;

It gives me the 10 August as the maximum date instead of the 24 August. And it also only shows a Max date record when the dbo_JobMain.Value is different for multiple dbo_JobMain.JobDefinitionPKey records.
Does anyone know why this might be happening? I think it might be related to the grouping?
Thanks! Any ideas much appreciated as it's driving me slightly crazy.

[FishVal]

WHERE clause and HAVING clause work quite differently.
WHERE clause criteria is being applied before grouping while HAVING clause criteria is being applied after grouping. Try to use in the 2nd query the same filtering method as in the 1st one.

[janieavis]

Hi Fishval!
Thanks for your help, but I can't seem to get your suggestion to work. When I click on the Access "Totals" button to enter Max for the date field it seems to convert WHERE to HAVING in the SQL statement, and if I try to edit the SQL itself (and change HAVING to WHERE I get a message saying "Syntax error (missing operator) in query expression..."
Any further ideas?
Thanks again.

[FishVal]

@janieavis
WHERE and HAVING close are not in the same place in SELECT expression syntax. Check Access help for a proper syntax.

[NeoPa]

Janie,

Posting SQL is best not done as a Paste and forget operation. It's very difficult for people to work with when simply splurged into a post. Please note that the CODE tags are mandatory also.

Moving on, have a try with this and see if it makes more sense to you. Remember, as the WHERE clause is processed before the grouping, it must deal only with incoming data. The HAVING clause, on the other hand, only has access to the processed data after the grouping has occurred. [VistDate] (from db0_JobMain - incoming data) cannot be used in a HAVING clause for that reason. It would make no sense either of course, but you may not be in a position to see that so easily yet.

SELECT   [BpaMainPKey],
         [JobDefinitionPKey],
         [Value],
         Max([VisitDate]) AS MaxOfVisitDate
 
FROM     dbo_JobMain
 
WHERE   (([BpaMainPKey]='000000000040abeb')
  AND    ([JobDefinitionPKey]='001000000043pobf')
  AND    ([VisitDate] Between #8/1/2009# And #8/31/2009#))
 
GROUP BY [BpaMainPKey],
         [JobDefinitionPKey],
         [Value]

[janieavis]

Thank you for your help.
Sorry I didn't know about pasting SQL directly into a post so I won't do that again. I also didn't realise there was a WHERE selection in the TOTAL row of the Access query so now I understand the difference between GROUP BY and WHERE in an aggregate query... thank you for helping me with that. I now have the correct maximum date displaying.

The problem I am now having is that I was hoping the query would display only the [Value] for the Maximum date but it shows the maximum date for each [Value]. Is there any way to just display the value that corresponds to the maximum date rather than having to group by it?

I have tried to use a separate sub-query to identify the maximum date and then pull in the corresponding [Value] but there is so much data to process that the query crashes.
Thanks again,
janie

[NeoPa]

So, you want the Max([VisitDate] within the grouping [BpaMainPKey],[JobDefinitionPKey] and the Value associated with the record that has that [VisitDate] yes?

[janieavis]

Hi NeoPa,
Yes! that's exactly what I'm trying to do!
Thanks again,
janie

[NeoPa]

For that sort of query I normally use a technique where I join the fields together in a string; find the maximum; then extract the data I'm interested in from the resultant string and convert it if required.

In this case that would translate to :

SELECT   [BpaMainPKey],
         [JobDefinitionPKey],
         Max([VisitDate]) AS MaxOfVisitDate,
         Val(Mid(Max(Format([VisitDate],'yyyymmddHHnnss') &
                     [Value]),15)) AS MaxVal
 
FROM     dbo_JobMain
 
WHERE   (([BpaMainPKey]='000000000040abeb')
  AND    ([JobDefinitionPKey]='001000000043pobf')
  AND    ([VisitDate] Between #8/1/2009# And #8/31/2009#))
 
GROUP BY [BpaMainPKey],
         [JobDefinitionPKey]

[janieavis]

Thank you, thank you, thank you !!!!!!
That works a treat!!!!!
janie
:-)

[NeoPa]

You're welcome Janie. I'm glad that's answered your question :)