Finding Multiple Products on contained in an invoice

[EDGE99]

All,

I am trying to build a query that will pull only specific records out of a transaction database. I only want to pull data from the database in which product_A and product_B are part of an invoice_id.

Example:
Invoice_id 1:
product_A

Invoice_id 2:
product_B

Invoice_id 3:
product_A
product_B

I only want the data from invoice 3 pulled as it contains both product A and B. Is there a way to do this?

 

[Golom]

Assuming that your transaction records are structured more or less as follows
[tt]tblTransactions
InvoiceID {PK}
ProductCode [red]<--- This would contain "Product_A"
or "Product_B" in different records[/red]
Other Fields
[/tt]
Then

Select * From tblTransactions

Where T.InvoiceID IN

(
   Select T.InvoiceID
   From (Select DISTINCT InvoiceID, ProductCode 
         From tblTransactions) As T
   Where ProductCode IN ('Product_A','Product_B')
   Group by T.InvoiceID
   Having Count(*)=2
)

 

[EDGE99]

I have tried several times to get this to work in Access but my in experience with Access is getting the best of me. Can you show me what this query would look like in Access?

Here is what my table looks like

Table: tbl_TransDB
invoice_id
product_id
product_desc
..
..
other fields

Here is some data

id invoice_id product_id product_desc list_price
1 100 10 Number 10 Widget $5,000.00
2 101 12 Number 12 Widget $3,500.00
3 101 11 Number 11 Widget $1,000.00
4 101 10 Number 10 Widget $5,000.00
5 102 13 Number 13 Widget $1,200.00
6 102 14 Number 14 Widget $10,000.00
7 103 10 Number 10 Widget $5,000.00
8 104 11 Number 11 Widget $1,000.00
9 104 12 Number 12 Widget $3,500.00
10 105 10 Number 10 Widget $5,000.00
11 105 13 Number 13 Widget $1,200.00
12 105 11 Number 11 Widget $1,000.00
13 106 11 Number 11 Widget $1,000.00
14 106 10 Number 10 Widget $5,000.00
15 106 13 Number 13 Widget $1,200.00
16 107 10 Number 10 Widget $5,000.00
17 107 11 Number 11 Widget $1,000.00
18 107 14 Number 14 Widget $10,000.00

 

[PHV]

And what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EDGE99]

An example would be to see if product 10 and 11 were sold on the same invoice. This would indicate that a Mega Widget was sold but we don't have a Mega Widget defined. So I would only want the records where invoices data contained both of these products. I do not need to see any other records. Does this make sense?

 

[PHV]

Something like this (SQL code) ?
SELECT A.*
FROM tbl_TransDB AS A INNER JOIN (
SELECT invoice_id FROM tbl_TransDB WHERE product_id IN (10,11)
GROUP BY invoice_id HAVING Count(*)=2
) AS B ON A.invoice_id = B.invoice_id

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EDGE99]

I can't seem to get that to work either. I have a mock DB that I could send you to create the query and I could review it that way and then modify as needed.