Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query; GROUP (2003+)

    Another of my silly questions;

    I want to query a table which has a date field & a simple numeric data field (amongst a couple of others). The query result needs to total the numeric field, & the date field grouped by month & I can't, for the life of me, remember how I did it before.

    Here's the current SQL;
    <font color=blue>SELECT tblWH.[Month-Yr], Sum(tblWH.Count) AS Numbers
    FROM tblWH
    GROUP BY tblWH.[Month-Yr]
    HAVING (((tblWH.[Month-Yr]) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]));</font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query; GROUP (2003+)

    Try this:

    SELECT DateSerial(Year([Month-Yr]),Month([Month-Yr]),1) As TheMonth, Sum([Count]) As Numbers
    FROM tblWH
    WHERE [Month-Yr] Between [Forms]![frmRptSel]![TxtDtFrom] And [Forms]![frmRptSel]![txtDtTo]
    GROUP BY DateSerial(Year([Month-Yr]),Month([Month-Yr]),1)

    You can format the TheMonth column as mmm-yyyy or similar.

  3. #3
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query; GROUP (2003+)

    That did the trick.

    Thank you very much

  4. #4
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query; GROUP (2003+)

    An extension of this has been landed in my lap & I'm sorry to say it's beaten me (again!).

    It's been requested to have a list/graph of the totals (per month) of each [reason].

    I tried making a Crosstab out of it, but if there are records on different days of any particular month, I get that many records!

    Below is what I've tried so far;

    <font color=blue>PARAMETERS [forms]![frmRptSel]![TxtDtFrom] DateTime, [forms]![frmRptSel]![txtDtTo] DateTime;
    TRANSFORM Sum(tblMedi.Count) AS SumOfCount
    SELECT tblMedi.MonthYr
    FROM tblMedi
    WHERE (((tblMedi.MonthYr) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]))
    GROUP BY tblMedi.MonthYr
    PIVOT tblMedi.Reason;
    </font color=blue>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query; GROUP (2003+)

    Use the expression from my previous reply.

  6. #6
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query; GROUP (2003+)

    Oh. I've tried this;

    <font color=blue>
    PARAMETERS [forms]![frmRptSel]![TxtDtFrom] DateTime, [forms]![frmRptSel]![txtDtTo] DateTime;
    TRANSFORM Sum(tblMedi.Count) AS SumOfCount
    SELECT tblMedi.MonthYr
    FROM tblMedi
    WHERE (((tblMedi.MonthYr) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]))
    GROUP BY DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1)
    PIVOT tblMedi.Reason;
    </font color=blue>

    But I'm getting "You tried to execute a query that does not include the specified expression 'MonthYr' as part of an aggregate function'.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query; GROUP (2003+)

    Try

    PARAMETERS [forms]![frmRptSel]![TxtDtFrom] DateTime, [forms]![frmRptSel]![txtDtTo] DateTime;
    TRANSFORM Sum(tblMedi.Count) AS SumOfCount
    SELECT DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1) AS TheMonth
    FROM tblMedi
    WHERE (((tblMedi.MonthYr) Between [forms]![frmRptSel]![TxtDtFrom] And [forms]![frmRptSel]![txtDtTo]))
    GROUP BY DateSerial(Year([tblMedi]![MonthYr]),Month([tblMedi]![MonthYr]),1)
    PIVOT tblMedi.Reason;

  8. #8
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query; GROUP (2003+)

    Cheers mate.

    That's working now

Posting Permissions

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