Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: Date Help Please I'm Stump

    Have database when a New record is added [sdate] has a default of DATE()

    want to know 2 thing

    1. number of new records added for a month by area

    would just be
    SQL = "SELECT data.Area, DateSerial(Year([sdate]),Month([sdate]),1) AS DD, Count(data.HazID) AS Hcount
    FROM data
    GROUP BY data.Area, DateSerial(Year([sdate]),Month([sdate]),1);"

    think it right well look right still counting


    2. Total number of records @ the end of each month by area (new plus exiting records)

    SQL="??"



    but 2 has me Stump can it be done
    Last edited by myle; 02-25-04 at 07:00.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Could you please explain the difference between the two queries.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your first request wanted totals for "a month"

    consequently you need to specify which month you want, and the best way to do that is to use the first dat of that month and the first day of the following month:
    PHP Code:
    select data.area
         
    count(data.hazid) as hcount
      from data
     where sdate 
    >= '2004-02-01'
       
    and sdate <  '2004-03-01'
    group 
        by data
    .Area 
    your second request want totals for "each month"

    consequently, you need some way to determine which months

    this is not an easy query

    you could generate the distinct months with a separate query:
    PHP Code:
    select distinct
           year
    (sdate)  as yr
         
    month(sdate) as mth 
      from data 
    and then use this query to drive the totals query:
    PHP Code:
    select data.area
         
    yr
         
    mth 
         
    count(data.hazid) as hcount
      from distinctmonths
    left outer
      join data
     where sdate 
    dateadd("m",1,dateserial(yr,mth,1))
    group 
        by data
    .Area
         
    yr
         
    mth 
    Last edited by r937; 02-25-04 at 09:55.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Thanks r937

    Under stand it

    how to write it in msaccess

    well i thinking down the right track know
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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