Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Question Unanswered: access 2003 count problem

    I am trying to get a count of all records using a right join with a date filter.

    example:

    total of all products that was ordered between a certain date range

    table1: Products
    ProductID
    ProductName

    table2:Orders
    CustomerID
    ProductID
    OrderDate
    Amount

    Results:

    MS Windows XP 100
    Mac OSX 0
    MS Vista 50

    I want all products in the list even if the value is zero.
    I been working on this for an hour and the only way I found is to use 2 queries. is there any other way?
    thanks
    Last edited by Chris_IT; 06-09-09 at 01:32. Reason: incorect word, change total to count

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Don't use right joins - they suck and make your SQL difficult to read.

    In Access you could use a derived table for this (or sub selects, but I prefer the derived table). More simple SQL (but less efficient to run) is to use a condition in your select instead of the where clause, e.g.
    Code:
    SELECT ProductName, NZ(COUNT(Iif(orderdate BETWEEN [Date Start] AND [Date End], "yay for pootle", NULL)), 0) AS ProductCount
    FROM Products LEFT OUTER JOIN Orders ON ..... blah blah
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    thanks, and your right, the right joins are just a quick easy method for counting but does not do a good job.
    will let you know when I try the code.

  4. #4
    Join Date
    Jun 2009
    Posts
    3
    the code work fine, must of needed coffee at the time becuase I work as a database administrator. Im used to doing sql with visual studio 98 & dot net framework. last time I used access was in 97.

Posting Permissions

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