Using Query and finding specific records

[frankie13]

Hi guys

can someone tell me how i go about solving this problem:

say i have a table of customers and a table for orders. Say a customer can only have one orders, what i would like to be able to do is run a query whereby it finds the customer who only has the products i enter in the query.
################################
i.e. Customer A: Order = tyres, battery, lights
Customer B: Order = tyres, battery

How would i write my query where if i just want to find a customer who has tyres and battery for their order it only returns Customer B???
################################
many thanks
frankie13

 

[Fizzio]

You do this by using the criteria box of the query. However.......

If you want to change the products you want to filter on, then it means having to go in and change the criteria every time.

What is slicker is to use a multiselect list box to select the criteria and using that to populate the query - Takes a bit of VBA noggin but there are bound to be loads of examples if you search here for multiselect list box etc.

Get back if you cannot find what you are looking for.

 

[frankie13]

still going mad

thanks for your time, its appreciated in my hour of need. But this doesn't quite help solve my problem.

i cant write a query to return just the results i want. Maybe im over complicating it, or maybe im just missing a keyword, but its just not working.....

i've included a very crude database to highlight what im doing.

If you would be kind enough to view this then, let me know
a) How do i write a query which will show volunteers with Asthma (only) (this should be volID=2)
b) How do i write a query which shows volunteers with only Asthma and menopausal (only) (this should be volID=3)


Please help!!

many thank you's

 

[Fizzio]

You need to use 2 queries to be able to do this. Find Attached an example of this. You do need to sort out your structure as you should not use text as an ID - use an autonumber instead - you are violating the basic rules of normalisation and will run into problems later.....