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

Max Date doesn't seem to give maximum date

P: 5
If I run this query I can see there are 2 records with the dates 10 August 2009 and 24 August 2009:
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value, dbo_JobMain.VisitDate
  2. FROM dbo_JobMain
  3. WHERE (((dbo_JobMain.BpaMainPKey)="000000000040abeb") AND ((dbo_JobMain.JobDefinitionPKey)="001000000043pobf") AND ((dbo_JobMain.VisitDate) Between #8/1/2009# And #8/31/2009#))
  4. ORDER BY dbo_JobMain.JobDefinitionPKey;
But when I try to group the query to get the maximum date using this:
Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value, Max(dbo_JobMain.VisitDate) AS MaxOfVisitDate
  2. FROM dbo_JobMain
  3. GROUP BY dbo_JobMain.BpaMainPKey, dbo_JobMain.JobDefinitionPKey, dbo_JobMain.Value
  4. HAVING (((dbo_JobMain.BpaMainPKey)="000000000040abeb") AND ((dbo_JobMain.JobDefinitionPKey)="001000000043pobf") AND ((Max(dbo_JobMain.VisitDate)) Between #8/1/2009# And #8/31/2009#))
  5. 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.
Oct 9 '09 #1

✓ answered by 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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [BpaMainPKey],
  2.          [JobDefinitionPKey],
  3.          Max([VisitDate]) AS MaxOfVisitDate,
  4.          Val(Mid(Max(Format([VisitDate],'yyyymmddHHnnss') &
  5.                      [Value]),15)) AS MaxVal
  6.  
  7. FROM     dbo_JobMain
  8.  
  9. WHERE   (([BpaMainPKey]='000000000040abeb')
  10.   AND    ([JobDefinitionPKey]='001000000043pobf')
  11.   AND    ([VisitDate] Between #8/1/2009# And #8/31/2009#))
  12.  
  13. GROUP BY [BpaMainPKey],
  14.          [JobDefinitionPKey]

Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Oct 9 '09 #2

P: 5
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.
Oct 9 '09 #3

FishVal
Expert 2.5K+
P: 2,653
@janieavis
WHERE and HAVING close are not in the same place in SELECT expression syntax. Check Access help for a proper syntax.
Oct 9 '09 #4

NeoPa
Expert Mod 15k+
P: 31,606
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT   [BpaMainPKey],
  2.          [JobDefinitionPKey],
  3.          [Value],
  4.          Max([VisitDate]) AS MaxOfVisitDate
  5.  
  6. FROM     dbo_JobMain
  7.  
  8. WHERE   (([BpaMainPKey]='000000000040abeb')
  9.   AND    ([JobDefinitionPKey]='001000000043pobf')
  10.   AND    ([VisitDate] Between #8/1/2009# And #8/31/2009#))
  11.  
  12. GROUP BY [BpaMainPKey],
  13.          [JobDefinitionPKey],
  14.          [Value]
Oct 9 '09 #5

P: 5
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
Oct 19 '09 #6

NeoPa
Expert Mod 15k+
P: 31,606
So, you want the Max([VisitDate] within the grouping [BpaMainPKey],[JobDefinitionPKey] and the Value associated with the record that has that [VisitDate] yes?
Oct 19 '09 #7

P: 5
Hi NeoPa,
Yes! that's exactly what I'm trying to do!
Thanks again,
janie
Oct 19 '09 #8

NeoPa
Expert Mod 15k+
P: 31,606
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 :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [BpaMainPKey],
  2.          [JobDefinitionPKey],
  3.          Max([VisitDate]) AS MaxOfVisitDate,
  4.          Val(Mid(Max(Format([VisitDate],'yyyymmddHHnnss') &
  5.                      [Value]),15)) AS MaxVal
  6.  
  7. FROM     dbo_JobMain
  8.  
  9. WHERE   (([BpaMainPKey]='000000000040abeb')
  10.   AND    ([JobDefinitionPKey]='001000000043pobf')
  11.   AND    ([VisitDate] Between #8/1/2009# And #8/31/2009#))
  12.  
  13. GROUP BY [BpaMainPKey],
  14.          [JobDefinitionPKey]
Oct 19 '09 #9

P: 5
Thank you, thank you, thank you !!!!!!
That works a treat!!!!!
janie
:-)
Oct 20 '09 #10

NeoPa
Expert Mod 15k+
P: 31,606
You're welcome Janie. I'm glad that's answered your question :)
Oct 20 '09 #11

Post your reply

Sign in to post your reply or Sign up for a free account.