A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Min/Max Dates and Group By



 
 
Thread Tools Display Modes
  #1  
Old May 18th, 2010, 03:35 PM posted to microsoft.public.access.queries
Andy
external usenet poster
 
Posts: 941
Default Min/Max Dates and Group By

I have a small table of data:

Start End Appointment Date
11-Sep-09
26-Feb-10
26-Apr-10
10-Jun-09 12-Mar-10
10-Jun-09 26-Apr-10
10-Jun-09 12-Nov-09 11-Sep-09
10-Jun-09 12-Nov-09 12-Nov-09
06-May-10 06-May-10

I am tring to find the maximum date for each combination of start/stop.
Applying a max function to the appointment date in a query returns just two
records, not four

Start End MaxOfAppointment Date
10/06/2009 26/04/2010
10/06/2009 12/11/2009 12/11/2009

The minimum function returns four rows and it all seems correct for min?!
Why is this not working for Max please?
  #2  
Old May 18th, 2010, 03:56 PM posted to microsoft.public.access.queries
Daryl S[_2_]
external usenet poster
 
Posts: 881
Default Min/Max Dates and Group By

Please post your SQL so we can help you out. Go into query design and select
SQL View, then copy/paste into your reply.

--
Daryl S


"Andy" wrote:

I have a small table of data:

Start End Appointment Date
11-Sep-09
26-Feb-10
26-Apr-10
10-Jun-09 12-Mar-10
10-Jun-09 26-Apr-10
10-Jun-09 12-Nov-09 11-Sep-09
10-Jun-09 12-Nov-09 12-Nov-09
06-May-10 06-May-10

I am tring to find the maximum date for each combination of start/stop.
Applying a max function to the appointment date in a query returns just two
records, not four

Start End MaxOfAppointment Date
10/06/2009 26/04/2010
10/06/2009 12/11/2009 12/11/2009

The minimum function returns four rows and it all seems correct for min?!
Why is this not working for Max please?

  #3  
Old May 18th, 2010, 04:06 PM posted to microsoft.public.access.queries
Andy
external usenet poster
 
Posts: 941
Default Min/Max Dates and Group By

Thanks Daryl. I have re-written the query and it now works - strange. The SQL
is completely the same both before and after and the data hasnt changed!



"Daryl S" wrote:

Please post your SQL so we can help you out. Go into query design and select
SQL View, then copy/paste into your reply.

--
Daryl S


"Andy" wrote:

I have a small table of data:

Start End Appointment Date
11-Sep-09
26-Feb-10
26-Apr-10
10-Jun-09 12-Mar-10
10-Jun-09 26-Apr-10
10-Jun-09 12-Nov-09 11-Sep-09
10-Jun-09 12-Nov-09 12-Nov-09
06-May-10 06-May-10

I am tring to find the maximum date for each combination of start/stop.
Applying a max function to the appointment date in a query returns just two
records, not four

Start End MaxOfAppointment Date
10/06/2009 26/04/2010
10/06/2009 12/11/2009 12/11/2009

The minimum function returns four rows and it all seems correct for min?!
Why is this not working for Max please?

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:56 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.