Re: Finding records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

One query method that may work:

SELECT C.CustID
FROM (Customers as C INNER JOIN Sales as S
ON C.CustID = Sales.CustID)
INNER JOIN Items as I
ON S.ItemID = I.ItemID
WHERE I.ItemName = "Paper 1"
AND NOT Exists(
SELECT S1.*
FROM Sales as S1 INNER JOIN Items as IB
ON S1.ItemID = IB.ItemID
WHERE S1.CustID = C.CustID
AND IB.ItemName = "Paper 2")

You could also do this with stacked queries.
--First query gets everyone that has bought paper2
--Second query uses that query in an unmatched query to get everyone that
has not bought paper 2, but has bought the other item
-- Third query uses the second query

Query - qBoughtThis
SELECT Sales.CustId
FROM Sales
WHERE ItemID = 5

Query - qGotOneNotOther
SELECT S.CustID
FROM Sales as S Left Join QBoughtThis as Q
ON S.CustId = Q.BoughtThis
WHERE S.ItemID = 8

Finally
SELECT C.CustId
FROM Customers as C INNER JOIN qGotOneNotOther as Q



"James" <James@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:89993CFC-AFE1-4695-AF7B-27B42ECE4698@xxxxxxxxxxxxxxxx
Hello,
I have three tables, one is Customer, one is Item and one is Sales.
Customers have custid which is uniqe, Items have itemid which is uniqe and
Sales has saleid which is also unique. They also have the usual fields
(name
address, price etc) Customer and Sales are linked on Custid and Sale and
Item
are linked on Itemid as they can only buy one item at a time.

I want to get a list of every customer who has bought a particular item,
but
not this other one, for example:
Each customer who bought Paper 1 over 6 months ago, but has not yet bought
Paper 2.

Paper 1 or Paper 2 will be in Item.Name but I can type in the itemid. I
just
need to find every custid that has itemid 1 but not itemid 2 in the linked
fields.

Any help would be greatly appreciated

Many thanks
James


.

Relevant Pages

  • RE: Records Quadrupling in Query - TableDesign Prob? Pls Help
    ... I have a similar problem with one of my workplace databases. ... I am trying to create a recordset from a query to display customer ... FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Aggregate string concatenation efficiency problem
    ... Column5 -- it looks like you want to list multiple consultants ... for each customer - on the same record. ... Here is my pseudo code for your query ... search, in this case, in Sales, a VIN or Stock#, and what is returned ...
    (comp.databases.ms-access)
  • Re: Query Problem
    ... Then use the un-matched query wizard. ... > production.tubing_install_amt, production. ... > inspection.contract_date)) INNER JOIN customerinfo ON ... >>>>> When I pull fields from main customer and billing it ...
    (microsoft.public.access.queries)
  • Re: Records Quadrupling in Query - TableDesign Prob? Pls Help
    ... design problem, so I'm posting it here in hope that someone can help. ... I am trying to create a recordset from a query to display customer ... FROM (tbl_Employees INNER JOIN ((tbl_Customers INNER JOIN ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Aging Invoice Report
    ... FROM (tblCustomers INNER JOIN Payments ... you had the fields customername and customeraddress in you query ... FROM (tblCustomers INNER JOIN Payments ON ... now, I am prompted for 2 parameter values, the customer name & customer ...
    (microsoft.public.access.queries)