Multiple queries on table

[jillapass]

I am trying to create a report as follows, but don't know how to.



I have a list of bookings and I find all the bookings for a particular week.
No problem there.

Then, I want to use the client for each of these bookings and find out all the bookings previously for this client, but only for clients who have a booking for the specified week.

So, to clarify, for next week I have 127 bookings and for each of these bookings I want to see if the client has booked previously.

Currently I am just finding the bookings for the specified week, but don't know how to do the 2nd query.

I guess what I need is to do a 2nd WHERE statement which is different from the 1st one, but on the same table.
Thanks fir any help.

[macropod]

Hi jillapass,

Perhaps a simple COUNTIF against the main list for each booking will do. If the count is greater than 1, that indicates a prior booking.

[jillapass]

I can see what you are saying, but after I find the list of bookings for the week specificed with 'wk_com = ?' I only also need to find the entries where 'wk_com<?'.
Then I could use the count.
The trouble is that if I go straigt for 'wk_com<=?' then I also get the entries that do not have a booking for the specificed week.
I have been told I need to do a sub query, but I have never done one of these and wouldn't really know where to start.

[macropod]

Hi jillapass,

As I haven't seen your workbook, I have no idea what 'wk_com' relates to.

Can you attach a workbook to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab.

[jillapass]

Sorry, wk_com is a date of start of booking.
It wouldn't make much sense to attach the workbook as the data is in an SQL database so all the workbook contains is a list of bookings for a specified week with the client number and carer number, nothing else at all.
So, the select finds the bookings for a week, I then want to find any previous booking for clients that are already in this list, but these are in the same table.


The query I have is:

SELECT tblBookingsWarehouse.Wk_Com, tblBookingsWarehouse.CarerNo, tblBookingsWarehouse."Client Number"
FROM ch_warehouse.dbo.tblBookingsWarehouse tblBookingsWarehouse
WHERE (tblBookingsWarehouse.Wk_Com=?) AND (tblBookingsWarehouse.Type='Booking')
ORDER BY tblBookingsWarehouse.CarerNo

[macropod]

I'm not well versed in SQL, but it seems to me there should be an equivalent of tblBookingsWarehouse.Wk_Com (maybe tblBookingsWarehouse.Wk_Com=*) that applies to all records in the table. That should enable you to extract all records for the matched clients.