Month year, summary query(2007)

Topic

New Reply

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

Reply | Quote

Replies

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;

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

Excellant!

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

Many thanks

Reply | Quote