Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Join problem in a query (Access 2000)

    I have 2 tables, customers and orders, connected with one to many relationship. My task is to enumerate all the clients having orders with order date > 1/1/2001 I cannot achieve it, since the inclusion of the where clause shows repeated clients depending on the order number. So i obtain about 2300 results,while my clients are only 500.Without the where clause i obtain the list with all the clients, and it shows the correct results for all the clients,however i want to include only the clients having an order given after 1.1.2001.Is to possible to join the tables in the query in such a way that to obtain the list of the clients gaving an order during year 2001? The folloiwing query gives me repeated result for the customers :

    SELECT customers.Customerid, orders.orderdate
    FROM customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)
    WHERE (((orders.orderdate)>#1/1/2001#));
    May i have some help how to reconstruct my query ?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join problem in a query (Access 2000)

    Try this
    SELECT DISTINCTROW customers.Customerid, orders.orderdate
    FROM customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)
    WHERE (((orders.orderdate)>#1/1/2001#));
    Francois

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Join problem in a query (Access 2000)

    Sorry, I was to fast.
    You have to remove orders.orderdate from the SELECT part of the query.

    SELECT DISTINCTROW customers.Customerid
    FROM customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)
    WHERE (((orders.orderdate)>#1/1/2001#));

    and why do you have two times (customers.Customerid = orders.customerid) ?
    Francois

Posting Permissions

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