Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count Zero As Zero (2003)

    Dear Friends,
    Am a novice to Access. I am struggling with this problem. I have attached a txt file which depicts where I am to where I want to be. Basically I want: IF the COUNT is Zero for a Month Year I want a Zero in my record against the Month Year. Right now, the Month Year record is OMMITTED from the Table. The effect is 'profound'. This changes the value of n for each Record. So the Average, STDEV etc for a range of, say, 12 months, changes for every record when it should be 12. I can fix for Avg by COUNT(*)/12 but for the STDEV, is for me impossible! I used to do this in Excel by filling in Zeros against the Month Year cell. Please help.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count Zero As Zero (2003)

    Is there a date range for this table (called tblA)?
    What you could do is to make up a table (called tblB) of entries of all months for the date range and showing zero as the count.
    Then create a query that selects all entries from tblA, next make a query selecting tblB entries where there are no tblA entries.
    Then create a union query of both of those queries.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count Zero As Zero (2003)

    What are the starting and ending month numbers? Your example shows months 7 - 11, so I can't really tell what the data might contain. Your query is based on a year and month from the data, so there really isn't anything that will "fill in" the missing dates using that kind of query. A crosstab query might be a possibility, since you can create column headings for a cross tab for fields that might not actually be there.
    Charlotte

  4. #4
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Zero As Zero (2003)

    Sorry for the date range ommission. It is July 2002 to Aug 2004. Regardless, I think you have solved my problem with your strategy. I will work it through. But.. if you can think of another way with this date range, please advise. Maybe, the solution will be much 'neater' than mine to frame the SQL query. Thank you.

  5. #5
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Zero As Zero (2003)

    Again, sorry. The date range is July 2002 to Aug 2004. My attempts with the crosstab query was not successful. It required 2 column headings i.e. year and below it months. My 'HELP' readings stated crosstab query takes in only one column heading. Again as in my response to patt, maybe another alternative. Maybe something via PivotTable? What I need to do is produce a numeric = Avg of Count over 12 months (even if Month Count = 0) + 2 * StdDev over 12 months (even if Month Count = 0). So here the applicable date range is September 2003 to Aug 2004. The extra range is just for 'historical' viewing. Thanks once again.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count Zero As Zero (2003)

    Are you using this for a report?
    If yes or even not for a report, why don't you do this in VBA just prior to what you wish to do?
    If you want some help on this please post.

  7. #7
    Lounger
    Join Date
    Dec 2001
    Posts
    48
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count Zero As Zero (2003)

    Dear Pat,
    No, this not for a report, screen views will suffice. Your suggestion for VBA sounds great. My problem is that I have not ventured into Access VBA. Excel VBA, yes. Probably this is just the time for some Access VBA! I will really appreciate some VBA code to do this. I will use it to expand my knowledge to do other tasks as well. To kind of shorten the learning curve, please advise where will I paste the code? Sorry for this basic question.Thank you. (Looking forward to your expertise. You have already helped a great deal!)
    Selva.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Count Zero As Zero (2003)

    What I would do in a form view is to make the union query (that I mentioned in a prior post) the source of the form.

    tblB holds just 12 records with the months 1 to 12 and count equal to zero.

    In the open event of the form I would put code to put the year into the table tblB (this holds year, month, count=0). Eg if the date range is Sep 2003 thru Aug 2004, then use an update query to change the year to 2003 for months Sep thru Dec, and to 2004 for Jan thru Aug.

Posting Permissions

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