Query for Report. 1

[lars7]

Hi,
I have a staff activity database i.e.; Over time, annual leave, sickness and I need to produce a monthly report. The information is recorded weekly with the weekending date entered as "dateID" and the staff grouped in locations. I have done a couple of queries to first group and them to sum the information. This is where I'm stuck, I am left with 4 weeks information for each location but I would like 4 weeks to show as 1 month.

Qryreport1 SQL:

SELECT tblRecords.LocationId, tblLocation.L_Location_Name, tblUnit.U_Unit_Name, tblRecords.DateId, tblmonths.fldmonths, tblRecords.HRS, tblRecords.OT, tblRecords.EX, tblRecords.LTS, tblRecords.STS, tblRecords.AL, tblRecords.ML, tblRecords.SL, tblRecords.CL, tblRecords.UL, tblRecords.OL
FROM tblmonths INNER JOIN (tblUnit INNER JOIN (tblLocation INNER JOIN tblRecords ON tblLocation.L_LocationID=tblRecords.LocationId) ON tblUnit.U_Unit_ID=tblLocation.L_UnitID) ON tblmonths.flddays=tblRecords.DateId
GROUP BY tblRecords.LocationId, tblLocation.L_Location_Name, tblUnit.U_Unit_Name, tblRecords.DateId, tblmonths.fldmonths, tblRecords.HRS, tblRecords.OT, tblRecords.EX, tblRecords.LTS, tblRecords.STS, tblRecords.AL, tblRecords.ML, tblRecords.SL, tblRecords.CL, tblRecords.UL, tblRecords.OL;

qryreport2 SQL:

SELECT qryreport1.LocationId, qryreport1.L_Location_Name, qryreport1.U_Unit_Name, qryreport1.DateId, qryreport1.fldmonths, Sum(qryreport1.OT) AS SumOfOT, Sum(qryreport1.EX) AS SumOfEX, Sum(qryreport1.LTS) AS SumOfLTS, Sum(qryreport1.STS) AS SumOfSTS, Sum(qryreport1.AL) AS SumOfAL, Sum(qryreport1.ML) AS SumOfML, Sum(qryreport1.SL) AS SumOfSL, Sum(qryreport1.CL) AS SumOfCL, Sum(qryreport1.UL) AS SumOfUL
FROM qryreport1
GROUP BY qryreport1.LocationId, qryreport1.L_Location_Name, qryreport1.U_Unit_Name, qryreport1.DateId, qryreport1.fldmonths;

Can anyone point me in the right direction with this.

 

[dhookom]

Can't your week ending date result in 4-5 weeks in a given month? For instance if your week ends on a Friday, there are 5 of them in March.

If you only want to group by the month of the week ending date, group by or sum your fields. You should Group By
Format(DateID, "yyyymm")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

What happens if you get rid of qryreport1.DateId in both the SELECT and GROUP BY clauses ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
I removed DateId completely. I now have the data in one line per location grouped by unit (which can consist of many locations)and month for my report. I know that weekending can overlap months but the managers are used to seeing reports in this way from the old system so I thought it best not to confuse them by changing. Thanks PHV another star from me on it's way.