Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    Tonsberg Norway
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query combination customer/sales (2000)

    I'm in a situation where I desperately need to solve a couple of queries. The logic seems quite straightforward, and a scenario which should be familiar to lots of people working with customers and sales databases. Hope somebody can help me on the way....

    The database has customers and sales tables, using customernumber (CNo) as relation field. I have one simple and one advanced example of things I'm trying to achieve:
    (1) List all customers who have NOT bought a specific product (TE2)
    Reason: Who can we send an offer for TE2 ?
    (2) List all customers who have NOT the above product (TE2), but who own another product (SRU)
    Reason: They need to own an SRU to have best use of TE2, although TE2 will work with some older alternatives like R31.

    CUSTOMERS table with four customer records:
    CNo Name
    12 Jim
    19 Joe
    33 Pete
    38 Bob

    SALES
    CNo ProdNo
    19 R31
    19 TE2
    12 SRU
    12 TE1
    38 SRU
    38 TE2

    Result (1): 12Jim and 33Pete (Pete hasn't bought anything, while Jim has only bought SRU and TE1)
    Result (2): Only 12Jim (38Bob has the SRU, but already own TE2. 33Pete still has nothing. 19Joe misses both conditions: Have no SRU, but own the TE2 )

    I have tried query building in Access, including query-to-new-table (temporary tables) - but now I'm a bit stuck. Not really shure what is the recommended course of action and the best steps from start to end for these two scenarios.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query combination customer/sales (2000)

    1) Create a query based on the Customers table, and add the following criteria for the CNo field:

    Not In (SELECT CNo FROM SALES WHERE ProdNo = "TE2")

    2) Create a new query based on the query from (1) and on the Sales table, joined on the CNo field.
    Add CNo and Name from the query, and ProdNo from the table.
    Set the criteria for ProdNo to "SRU" and clear the Show check box for this column.

    See attached demo.

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    Tonsberg Norway
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query combination customer/sales (2000)

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> I felt I was close - but not that it should be this easy. Thanks a lot for great help!
    Your example just opened several new roads to great data filtering and combination!

  4. #4
    New Lounger
    Join Date
    Jan 2002
    Location
    Tonsberg Norway
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query combination customer/sales (2000)

    As always, a situation pops up - that is not covered by the logic.
    If we allow a customer to own more than one product, the output changes from perfect to "usable". .....

    Let's say that in the Sales-table, I put another row for 12JIM - SRU (needed to modify the design, added another autonumber field as key).
    Then the output of the second query says:

    CNo Name
    12 Jim
    12 Jim

    And if a customer has 12 or 50.....
    Any suggestion how to get around this in a more elegant way ?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query combination customer/sales (2000)

    You can set the Unique Values property of the query to Yes. To set query properties, open the query in design view, click in an empty part of the upper half of the query window and activate the Properties window.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •