Finding contacts by product ordered.

[At work]

I'm trying to get a list of contacts based on if they ordered a certain
product. I feel like the numbers returned should be a lot bigger then what
I'm getting...also, I'm getting mixed results.

Here's what I have.

contact table >OrderTable (has total amount of order and shipping
address)>Order Details (list of products ordered and qty's)
these are one to many relations. I also have my inventory table which has no
relations or constraints directly to any table.

Here's what I first tried:
contact table >OrderTable >Order Details + inventory

I linked the inventoryid from order details to the inventory table. I have 4
products I'm looking for and in the criteria of the inventory id I put "715"
or "716" or "1688" or "1689"

This returned 144 records (with some dupes, since a customer could have
ordered the same items more then once so that's fine).

However, if I only search for just one of the products it returned 265
results (again with some dupes).

I guess I need some time of and or in my criteria or is it something else
I'm doing wrong/the combination of how my tables are linked.. I did try and
or, but it said invalid syntax.

Mainly, just want a list of customers who've ordered these products.
Ultimately, although not necessary (would be helpful), it'd be nice to run a
total of all the orders placed by that customer, not the total$ but all
their orders of that one or many products to get a total of how many of each
(or how many combined of all products) they've ordered.

Thanks

 

[Ernie]

I would do this as two queries. First, you shouldn't need
your inventory table unless you also need to
report/display the description or cost information.

query 1: order details using the criteria you've already
specified, the columns you would need should include the
order id as well as the item id.

query 2: contacttable > ordertable > query1 whatever
columns you want to see, in the criteria for order id use
is not null

This should get you what you want.

HTH

 

[At work]

So, are you saying then to make one order details querie for each product I
have since there no "and or" expression.

Then take my first product queries and link those to my new query
contacttable>ordertable>query1+query2+query3+query4?

 

[At work]

Okay, well it didn't work with linking multiple queries like I had thought
you meant, I guess I'll just need to do one query for each product. This is
basically just as I had it before, except in 2 queries instead of one now.

However, I noticed something when you do it with 2 queries vs my one, after
running the query, I went to sort by name/contact id, after doing so, it
"removed" about 100 records, after scanning the list, it looks as if it went
ahead and removed duplicate entries...is that infact what happend? If so,
that's very nice because that was something I thought I was going to have to
do manually.

 

[At work]

Sorry to keep posting. I know the main objective is complete now, however,
it would be nice if I could take the duplicates, add the quanities ordered
on each one, then purge the list of dupes, yet keeping the total qties of
combined orders into a new cell or something.