Select query

[barbj1969]

Hi,

I have a table where I need to select the record where the booking end date for a Site does not have a booking following on. For example the first record for site AB020/E that ends on 22/03/2015 has a follow on booking in record 2 (same site - booking start date is previous booking end date plus 1). However record 2 for that site does not have a follow on booking.

Therefore out of this table I would want to select record 2, record 4, record 8, record 9 and record 10.

Can anyone help me with how I would write this please? Thanks for your time.

SITE	BOOKING START DATE	BOOKING END DATE	CAMPAIGN ID	CAMPAIGN NAME
AB020/E	24/03/2014	22/03/2015	5339	ABC PTY LTD
AB020/E	23/03/2015	20/03/2016	5339	ABC PTY LTD
AB020/W	16/06/2014	14/06/2015	5531	BCD EXPRESS
AB020/W	15/06/2015	12/06/2016	5531	BCD EXPRESS
AB030/E	19/05/2014	05/10/2014	4602	FRED BLOGGS
AB030/E	06/10/2014	02/11/2014	4602	FRED BLOGGS
AB030/E	03/11/2014	17/05/2015	4602	FRED BLOGGS
AB030/E	18/05/2015	15/05/2016	5900	FILLER
AB030/W	16/06/2014	14/06/2015	4748	SMITHS CAFE
AB040/E	09/09/2013	07/09/2014	4214	MAC'S DINER

[ssanfu]

You didn't give table or field names, sooooo..

I created a table "CAMPAIGN" (I really hate shouting) with the headings from the table above in proper case.
I created Query2 to get the max dates by site.

SELECT Campaign.Site, Max(Campaign.BookingEndDate) AS MaxOfBookingEndDate
FROM Campaign
GROUP BY Campaign.Site;

Then I created another query (Query4) adding Query2 and table "Campaign"

SELECT Campaign.Site, Campaign.bookingStateDate, Campaign.BookingEndDate, Campaign.CampaignID, Campaign.CampaignName
FROM Query2 INNER JOIN Campaign ON (Query2.Site = Campaign.Site) AND (Query2.MaxOfBookingEndDate = Campaign.BookingEndDate);

This is only one method..

Of course it would be easier if the table was normalized...

[barbj1969]

Thank you so much for your reply. I used to do a fair bit in Access but haven't done anything for about 9 years. Will have a play with that and also normalise the table. It was originally an excel spreadsheet but I didn't know how to write it in excel and thought I would have better luck with Access.