Solved Query only show Lastest Date Records (1 Viewer)

Number11

Member
Local time
Today, 05:46
Joined
Jan 29, 2020
Messages
228
Hi,
So I am looking for a way to have the query only show the latest appointment date and not show all booked

I have these fields...

Customer ID | Date Booked | Appointment Date | Appointment Time
123|24/07/2020|03/08/2020|9:00
123|27/07/2020|08/08/2020|11:00
123|07/08/2020|13/08/2020|9:45

Just want to show the latest appointment Date - is that possible?
 

arnelgp

error reading drive A:
Local time
Today, 13:46
Joined
May 7, 2009
Messages
11,114
Select [customer id], max([appointment date] + [appointment time]) as maxApointment from
yourTable group by [customer id];
 

Number11

Member
Local time
Today, 05:46
Joined
Jan 29, 2020
Messages
228
Select [customer id], max([appointment date] + [appointment time]) as maxApointment from
yourTable group by [customer id];

Thanks for your help so this is my query at the moment..

SELECT DISTINCT Master.[Customer ID], [Booked].[ Customer ID], [Booked ].[Booked On], [Booked].Date, [Booked].Window

FROM [Booked] INNER JOIN Master ON [Booked].[ Customer ID] = Master.[ Customer ID]

WHERE ((Booked].[Booked On])>#7/1/2020#));
 

arnelgp

error reading drive A:
Local time
Today, 13:46
Joined
May 7, 2009
Messages
11,114
Query1:
SELECT Booked.[Customer ID], Max([Date]+[Window]) AS MaxDate
FROM Booked
GROUP BY Booked.[Customer ID];


Query2:
SELECT Master.[Customer ID], Booked.[Book On], Booked.Date, Booked.Window
FROM MASTER LEFT JOIN BOOKED ON MASTER.[CUSTOMER ID] = BOOKED.[CUSTOMER ID]
WHERE BOOKED.[DATE] + BOOKED.WINDOW = (SELECT MAXDATE FROM QUERY1 WHERE
[CUSTOMER ID] = [MASTER].[CUSTOMER ID]);
 

Number11

Member
Local time
Today, 05:46
Joined
Jan 29, 2020
Messages
228
Ok thanks so i need to create 2 querys to get this to work, i was going to rename the table Booked to Book App, will that work?
 

Users who are viewing this thread

Top Bottom