Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Thanked 0 Times in 0 Posts

    join in a query (Access 2000)

    I have a query thaty enumerates the Customer number and the Company name from 2 tables Customers and Orders and it is the following:

    SELECT DISTINCTROW Customers.Customerid, Customers.CompanyName
    FROM Customers RIGHT JOIN Orders ON Customers.Customerid = Orders.customerid;

    Now i wanted I wanted to include one more field in my query, namely payment, from the table orders, in order to include only those customers who have paid. My query looks loike this :
    However my query repeats the customers several times. For example Customer number 3 appears
    4 times in my second query, while in my first query the cusomers are enumerated in ascending order.
    Can i obtain a list for my customers who have paid and these customers do not repeat themselves in the query?

  2. #2
    Star Lounger
    Join Date
    Oct 2001
    Western Massachusetts, USA
    Thanked 0 Times in 0 Posts

    Re: join in a query (Access 2000)

    Markus, try something like this and see if it returns what you need.
    SELECT DISTINCTROW Customers.Customerid, Customers.CompanyName, Orders.Payment
    FROM Customers LEFT JOIN Orders ON Customers.Customerid = Orders.customerid
    WHERE ((Not (Orders.Payment) Is Null));

Posting Permissions

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