Multiple queries on table

jillapass · #1 09-14-2012, 02:38 AM

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 · #2 09-16-2012, 04:14 AM

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 · #3 09-17-2012, 01:07 AM

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 · #4 09-17-2012, 01:14 AM

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 · #5 09-17-2012, 01:29 AM

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:

Code:

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 · #6 09-17-2012, 03:36 AM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
mdb/accdb, missing tables & queries	Peter J. Veger	Mail Merge	1	02-23-2012 10:42 PM
Mailmerge with Access queries	optiontips.in	Mail Merge	2	11-21-2011 03:12 AM
Table of contents with multiple coloured headings	chaka2487	Word	3	10-17-2011 01:18 PM
table spanning multiple pages 'upside down'	lj_eco	Word Tables	13	07-17-2011 04:39 PM
mail merge queries not displaying	donwalt	Mail Merge	0	09-05-2010 02:41 PM