Query/Filter to get multiple records of interest

  • Thread starter Thread starter William M. Bickley
  • Start date Start date
W

William M. Bickley

I have a table with the following fields:

Invoice Number
Product Number
Quantity
Unit Price
Total
etc.

An invoice number may appear in many records, each with a different
product number (i.e., one custmer buys several items).

Here's my problem: I'd like to find out what else a customer bought,
when they purchased a specific item (product number). In other words, I
want all the records for all invoices which include the product of interest.

Any advice or assistance would be appreciated.

Bill
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you want that for just one customer or all customers?

For just one customer:

PARAMETERS [Enter the Product Number:] Long,
[Customer ID?] Long;
SELECT *
FROM TableName As T
WHERE CustomerID = [Customer ID?]
AND [Invoice Number] IN
(SELECT [Invoice Number]
FROM TableName
WHERE [Product Number] = [Enter the Product Number:]
AND CustomerID = T.CustomerID)


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBK0iIechKqOuFEgEQLGnwCfZGSaBcejRgzfCzI+p33jrcktavMAoLmw
0ZWsvdE5M3VTnk3AR7t4Dhva
=T1CH
-----END PGP SIGNATURE-----
 
For all invoices...

Bill
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you want that for just one customer or all customers?

For just one customer:

PARAMETERS [Enter the Product Number:] Long,
[Customer ID?] Long;
SELECT *
FROM TableName As T
WHERE CustomerID = [Customer ID?]
AND [Invoice Number] IN
(SELECT [Invoice Number]
FROM TableName
WHERE [Product Number] = [Enter the Product Number:]
AND CustomerID = T.CustomerID)


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBK0iIechKqOuFEgEQLGnwCfZGSaBcejRgzfCzI+p33jrcktavMAoLmw
0ZWsvdE5M3VTnk3AR7t4Dhva
=T1CH
-----END PGP SIGNATURE-----

I have a table with the following fields:

Invoice Number
Product Number
Quantity
Unit Price
Total
etc.

An invoice number may appear in many records, each with a different
product number (i.e., one custmer buys several items).

Here's my problem: I'd like to find out what else a customer bought,
when they purchased a specific item (product number). In other words,
I want all the records for all invoices which include the product of
interest.

Any advice or assistance would be appreciated.

Bill
Click to expand...
Click to expand...
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Actually, that's easier & I should have seen it in the first post.

PARAMETERS [Customer ID?] Long;
SELECT *
FROM Invoices
WHERE CustomerID = [Customer ID?]

This assumes that TableName has the CustomerID column and all the
invoice data. It will return all invoices for the entered CustomerID.

If TableName does not have the CustomerID then you'd have to JOIN to a
table that has the CustomerID & the Invoice number. This example uses
a "junction table," Orders, that holds (& joins) CustomerIDs and
InvoiceNumbers.

PARAMETERS [Customer ID?] Long;
SELECT I.*
FROM (Customers As C INNER JOIN Orders AS O
ON C.CustomerID = O.CustomerID) INNER JOIN Invoices As I
ON O.InvoiceNumber = I.InvoiceNumber
WHERE C.CustomerID = [Customer ID?]

HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBV03oechKqOuFEgEQLPeQCeJlr+WmsvGNLYawG6qsIhXudxE5IAn14F
t9vmgpYlOGTt17ym9bitK2FD
=2P8N
-----END PGP SIGNATURE-----


For all invoices...

Bill
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you want that for just one customer or all customers?

For just one customer:

PARAMETERS [Enter the Product Number:] Long,
[Customer ID?] Long;
SELECT *
FROM TableName As T
WHERE CustomerID = [Customer ID?]
AND [Invoice Number] IN
(SELECT [Invoice Number]
FROM TableName
WHERE [Product Number] = [Enter the Product Number:]
AND CustomerID = T.CustomerID)


HTH,

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQBK0iIechKqOuFEgEQLGnwCfZGSaBcejRgzfCzI+p33jrcktavMAoLmw
0ZWsvdE5M3VTnk3AR7t4Dhva
=T1CH
-----END PGP SIGNATURE-----


William M. Bickley wrote:
Click to expand...
Click to expand...
 
Back
Top