Latest Date Query (1 Viewer)

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
Hi, So i have a query that i have set "Max" on the following fields to bring just back the latest records for the customer, however this is not working as its being back the latest in each field which do not match


CustomerAC
StartDate
EndDate
Order Date

would like to see results as follows

CustomerAC then show the latest StartDate and latest End date but must be > Start Date or Null and then again same for the order date
 

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
Should i great a Max Query for each of the date ranges and then pull together in a new query?
 

conception_native_0123

Well-known member
Local time
Today, 00:23
Joined
Mar 13, 2021
Messages
1,826
CustomerAC then show the latest StartDate and latest End date but must be > Start Date or Null and then again same for the order date
can you please clarify what you mean by this? the max() function is what is called an aggregate. aggregates group records by fields that are not included in the function. so, that's probably why your result was incorrect.
 

conception_native_0123

Well-known member
Local time
Today, 00:23
Joined
Mar 13, 2021
Messages
1,826
personally, I'm not sure I'm understanding what you need. can you upload the query so someone can take a look?
 

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
see results showing end date before start date
CustomerAC
Order Date
Start Date
End Date
141161445​
27/10/2012​
25/10/2012​
24/09/2012​
so its showing the last end date which was before the new contact date
 
Last edited:

conception_native_0123

Well-known member
Local time
Today, 00:23
Joined
Mar 13, 2021
Messages
1,826
what constitutes the latest record for the customer? order date, start date or end date? in the first post you said:
So i have a query that i have set "Max" on the following fields to bring just back the latest records for the customer, however this is not working as its being back the latest in each field which do not match


CustomerAC
StartDate
EndDate
Order Date

so now I understand. sorry about that! if you write a query like this:
Code:
select customerAC, max(order date), start date, end date from table
group by customerAC, start date, end date
then the query will only return records for every customer that is the most recent order date. is that what you're wanting?

furthermore, are you writing the query with the query builder or writing code by hand?
 

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
what constitutes the latest record for the customer? order date, start date or end date? in the first post you said:


so now I understand. sorry about that! if you write a query like this:
Code:
select customerAC, max(order date), start date, end date from table
group by customerAC, start date, end date
then the query will only return records for every customer that is the most recent order date. is that what you're wanting?

furthermore, are you writing the query with the query builder or writing code by hand?
thanks help full so what i trying to do is show the results of all customer by "last order date", "Last Start Date" and then "End Date" - but only shoe if greater than the "Start Date" and if not then show as null? - thats is that - OH Think i got it working if i change Max on "End Date" to Group?
 
Last edited:

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
no now thats bring back older dates so somehow need to same only show if end date is null or greater than start date?
 

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
So i add this to the end date criteria so see if this worked...

>Max([Table1].[Start Date]) Or Is Null

The results bring back old start and end dates and then the newer start . how can i only use the newest start date?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:23
Joined
May 7, 2009
Messages
19,169
create first the Max query (qryMax):
Code:
SELECT yourTable.customerAC,
Max(yourTable.[order date]) AS [MaxOforder date],
Max(yourTable.[start date]) AS [MaxOfstart date],
Max(yourTable.[end date]) AS [MaxOfend date]
FROM yourTable
GROUP BY yourTable.customerAC;
now create another Query from this query:
Code:
SELECT customerAC, [Maxoforder date] As [Order Date],
    [MaxOfstart date] As [Start Date],
    IIF([MaxOfstart date] > [MaxOfend date], Null, [MaxOfend Date]) As [End Date]
FROM qryMax;
 

conception_native_0123

Well-known member
Local time
Today, 00:23
Joined
Mar 13, 2021
Messages
1,826
So i add this to the end date criteria so see if this worked...

>Max([Table1].[Start Date]) Or Is Null

The results bring back old start and end dates and then the newer start . how can i only use the newest start date?
from everything I've read from you, go ahead and give arne's solution a try. I'm having a little bit of a hard time following what you need. let us know if that doesn't work ok? thanks.
 

Number11

Member
Local time
Today, 05:23
Joined
Jan 29, 2020
Messages
607
create first the Max query (qryMax):
Code:
SELECT yourTable.customerAC,
Max(yourTable.[order date]) AS [MaxOforder date],
Max(yourTable.[start date]) AS [MaxOfstart date],
Max(yourTable.[end date]) AS [MaxOfend date]
FROM yourTable
GROUP BY yourTable.customerAC;
now create another Query from this query:
Code:
SELECT customerAC, [Maxoforder date] As [Order Date],
    [MaxOfstart date] As [Start Date],
    IIF([MaxOfstart date] > [MaxOfend date], Null, [MaxOfend Date]) As [End Date]
FROM qryMax;
will give it ago :)
 

Users who are viewing this thread

Top Bottom