Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2001
    Thanked 0 Times in 0 Posts

    Get a Zero! (2003)

    Dear All,

    I have table as follows:

    Date Record

    I have this SQL query:

    Select Month(Date), Year(Date), Count(*), Count(*)/1500 AS Rate
    From Table
    Group By Date
    Order By Date

    There are months where there are zero records, say, Feb-2004. I want it as:

    2 2004 0 0

    Right now, February 2004 gets 'skipped'. How can I force the zeros?

    Thank you.


  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Royal Oak, Michigan, USA
    Thanked 0 Times in 0 Posts

    Re: Get a Zero! (2003)

    To do this requires a bit of trickery. Try the following:

    Create a table with two fields...Month and Year and populate the table with the year/month combinations you want

    Create a query off your original table to calculate Month(Date) and Year(Date)

    Create a query where you link the Month and Year from the new table to the original table and set the link so you return all records from the new table and only records from the original table that match. Now do a count on the month field from your original table in this query.

    By using the new table to pull all the month/year combinations you want and counting on the original table, you force February to appear (based on the new table) but since there are no records in the original table, you should receive a zero (if you receive a null, use the nz() function to convert it to zero.

    Hope that helps.

Posting Permissions

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