Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi, I'm trying to combine data from 2 tables. One has a date field used for month & year, the other for full date.

    The problem I'm having is that I want to group by month year & the table with the full date is pretty much stopping me.

    This is the query (in it's latest incarnation);
    SELECT ServiceImprovementRequest.DateRecorded, ServiceImprovementRequest.Counted, Count(ServiceImprovementRequest.AutoID) AS CountOfAutoID
    FROM ServiceImprovementRequest
    GROUP BY ServiceImprovementRequest.DateRecorded, ServiceImprovementRequest.Counted
    HAVING (((ServiceImprovementRequest.Counted)=True));


    Attached are the 2 tables
    Attached Files Attached Files

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

    SELECT DateRecorded-Day(DateRecorded)+1 AS YrMonth, ServiceImprovementRequest.Counted, Count(ServiceImprovementRequest.AutoID) AS CountOfAutoID
    FROM ServiceImprovementRequest
    GROUP BY DateRecorded-Day(DateRecorded)+1, ServiceImprovementRequest.Counted
    HAVING (((ServiceImprovementRequest.Counted)=True));

    As far as I can tell there is no need to include Counted in the result of the query since it will always be True (but that's for you to decide). If so, the query can be simplified to

    SELECT DateRecorded-Day(DateRecorded)+1 AS YrMonth, Count(ServiceImprovementRequest.AutoID) AS CountOfAutoID
    FROM ServiceImprovementRequest
    WHERE ServiceImprovementRequest.Counted=True
    GROUP BY DateRecorded-Day(DateRecorded)+1;

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

    I'm sorry, I missed out a critical item; the [turnover] field from tblUKturnover has to be included. So I've played around with different ways of doing it, but the fact that the date in ServiceImprovementRequest needs to be grouped into one month AND related to the [YrMonth] in tblUKturnover has me stumped.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I don't see the problem. Could you explain more clearly and precisely what you want to accomplish?
    It might help if you attached a stripped down and zipped copy of the database in Access 2000 or 2002-2003 format.

  5. #5
    4 Star Lounger
    Join Date
    May 2001
    Location
    Oxfordshire
    Posts
    456
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Apologies, I've never been too good at describing things like this, but here goes;

    I'd like to have a query that returns 3 columns;
    1) the month & year (grouped so that there is only one month each e.g. 012009, 022009,032009...)
    2) a count of how many records, for that month/year. From the ServiceImprovementRequest table, WHERE [counted] = True
    3) the related [turnover] from the tblUKturnover table

    This would show the last 24 records i.e. the last 2 years.

    I've included the tables & a WIP query, that might help with my explanation

    TIA
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd do this in two steps:

    1. Create a totals query based on ServiceImprovementRequest that groups by month and counts the number of records:

    SELECT DateRecorded-Day(DateRecorded)+1 AS YrMonth, Count(ServiceImprovementRequest.AutoID) AS CountOfAutoID
    FROM ServiceImprovementRequest
    WHERE (((ServiceImprovementRequest.Counted)=True))
    GROUP BY DateRecorded-Day(DateRecorded)+1;

    Save this as qryCountByMonth.

    2. Create a query based on qryCountByMonth and on tblUKTurnover, joined on YrMonth to return the count from the query and the turnover from the table:

    SELECT qryCountByMonth.YrMonth, qryCountByMonth.CountOfAutoID, tblUKtunover.Turnover
    FROM qryCountByMonth INNER JOIN tblUKtunover ON qryCountByMonth.YrMonth = tblUKtunover.YrMonth;

    See the attached version.
    Attached Files Attached Files

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

    It was that YrMonth: DateRecorded-Day(DateRecorded)+1 workaround I needed.

    Many thanks

Posting Permissions

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