Query matching multiple reocrds

[samuka]

Hi,

Need help with writing the following query. With the below data.

On the left I have a order no and on the right a product.
You will notice that the order number are in pairs(some time even more than 2). What I need is to write a query that will give me a result where a order that has both a Hanset and a sim card. If it does not have both I do not want to know about it.

Like for the below I would only want to know about the first 4 records as the bottom two are from the same order but it only meets one criteria.

Data is in one table and each line is a record. Using Access 2000

Can any one help.

Thanks

Sam


Order Products
#1116217 Nokia 3120 Handset
#1116217 New GSM Service Macquarie Sim Card (32K)
#1116221 New GSM Service Macquarie Sim Card (32K)
#1116221 Nokia 3120 Handset
#1119105 Nok.Blutth Carkit Bndle-No Ant.(CK-7WS)
#1119105 Nokia 3120 Handset

 

[AlexMidd]

How do you designate (in the database) which ones have handsets and which have sim cards?

Have fun! )

Alex Middleton

 

[Pampers]

You can use "Like" in the critera field of the query

Like "*Handset*" Or Like "*Sim*"

Pampers
Just became father!

 

[samuka]

Thanks for your replies. Using Like "*Handset*" Or Like "*Sim*" Will not work as this only looks at product only. On the above data it would give you a answer of 5 records. Where as the bottom 2 records do not fit both criteria.

I need to look at the order records as well. Anything that has a handset and sim in the product(these will be in 2 seperate records) with the same order number is what I want to see, but if there is a handset on the order and no sim with the same order number I do not want to see that.

Im guessing you will have to use a variation of the Like "*Handset*" And Like "*Sim*" but you will have to combine it with a statement to have 2 of the same order number for 2 records.

How I dont know.

Thanks again.