473,387 Members | 1,859 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Max Date doesn't seem to give maximum date

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]

10 6584
FishVal
2,653 Expert 2GB
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
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
2,653 Expert 2GB
@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
32,556 Expert Mod 16PB
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
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
32,556 Expert Mod 16PB
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
Hi NeoPa,
Yes! that's exactly what I'm trying to do!
Thanks again,
janie
Oct 19 '09 #8
NeoPa
32,556 Expert Mod 16PB
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
Thank you, thank you, thank you !!!!!!
That works a treat!!!!!
janie
:-)
Oct 20 '09 #10
NeoPa
32,556 Expert Mod 16PB
You're welcome Janie. I'm glad that's answered your question :)
Oct 20 '09 #11

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

Similar topics

4
by: Christine | last post by:
I am having the strangest problem. I use the Date function in several of my forms and modules in an Access 2000 mdb. Lately, wherever in my code (in this one mdb) I use the Date function, it...
1
by: tshad | last post by:
I have been trying to set up a range validator and regular expression for my dates. I tried this: <asp:RangeValidator runat="server" ControlToValidate="AbsentFrom1" MinimumValue="12/31/1950"...
5
by: Kermit Piper | last post by:
Hello, I am comparing two date values, one from a database and one that has been converted from a hard-coded string into an actual Date type. So far so good. The problem I'm having is that one...
12
by: Assimalyst | last post by:
Hi, I have a working script that converts a dd/mm/yyyy text box date entry to yyyy/mm/dd and compares it to the current date, giving an error through an asp.net custom validator, it is as...
2
by: crackerbox | last post by:
I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script...
17
by: Petyr David | last post by:
Just looking for the simplest. right now my perl script returns an error messge to the user if the date string is invalid. would like to do this before accessing the server. TX
3
by: miss time | last post by:
Hi please tell me the wrong in this code becaus it not compile with me the question depend in other class that i do it before and this class her: public class GField { private int value;...
4
Sandboxer
by: Sandboxer | last post by:
I want to be able to program Access to provide for me, by individual day, what my contract obligations are to my customers. Will Access recognize all the individual days in between a date range...
1
by: guilhermelemmi | last post by:
I'm creating a function that will take a start date, a period duration (in months)and a maximum date, and will calculate the periods start and end dates until reaching the maximum date. For...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.