Selcting records by date - But only one booking

[be_insane]

Hi all.



Been a while since I used access and trying to get my head back round things. Right I have two tables at the moment - one is bookings done by date - linked through to customers via customer ID.

Now what I am after is a simple query on bookings for bookings before a certain date where a customer has then not made a subsequent booking after that date.

IE want all customers who only made a booking before 2010 and not any since.

Can anyone help?

Thanks

[pbaldy]

You can use the first query here to get the last booking for each customer.

http://www.baldyweb.com/LastValue.htm

[be_insane]

Thanks for the reply - but not quite what I'm after...

right sample data below.

Client	OperatorName	Broch	wfDepDate	wfBookDate
1	Sunspot Tours Ltd (a)	WS	24-Jan-10	11-Jan-10
2	Transun Holidays (c)	WS	26-Jan-10	11-Jan-10
2	African Pride (c)	TM	01-Feb-10	20-Jan-10
3	Jetset (d)		01-Dec-10	06-Sep-10
3	Jetset (d)		19-Mar-13	31-Jan-13
4	Virgin Holidays Limited (d)	LH	03-Feb-11	11-Jan-11

So the client column is the unique client number which relates to a particular client (contact details in a different table).

So from the above we have four clients, client 1 has only made one booking in 2010, client 2 has made 2 bookings both in 2010, client 3 also 2 bookings one in 2010 the other 2013 and client 4 has done 1 in 2011.

What I need is to have a query that could find me all customers who booked in 2010 BUT have not made a booking after 2010. So in this case it would return clients 1 and 2 only.

any help would be appreciated.

You can use the first query here to get the last booking for each customer.

http://www.baldyweb.com/LastValue.htm

[be_insane]

Thanks PBaldy.

After a bit more thinking I realised I could use this 'last booked' approach to achieve what I needed to by combining it with another query which I define by a booking time range.

Now - further question - so same situation of multiple bookings for one person - is there an easy way to create new fields showing their total and average spend over all bookings?

thanks all.

[pbaldy]

Are you familiar with a totals query? You would group on the person, sum and average the amount field.

[be_insane]

Hi PBaldy. Thanks for your help - I think I have worked out a way of doing it - similar to the approach for the last booking date.

As mentioned been a while since I did databases and i'm slowing having to re-learn things!

[pbaldy]

No problem. Welcome back!