Compare weekly/monthly/yearly data

[umair434]

Hi,

I have a table which has list of activities that a user has done over the years. The following fields exist in the table:

ID
dte
Week#
EmployeeID
Activity1
..
..

..Activity10

how would i use develop a query that would give me the sum of records for Week 10 (for example) and sum of records for week 11 so they can be compared.

Or week 10 for 2011 vs week 10 for 2010 data!

I want the sum of records because there are multiple users who enter the data, but my team is only concerned with the total number of records entered!

thank you for your help

 

[PNGBill]

Start with a select query and when you have all the data then click the Group/totals symbol and group on the weeknumber.

You can include a derived Field in your query which will just be Year and you can group on this also which should give you the weeks for each year.

bit of trial and error is often required.

As you attempt this, post your sql for advice.

 

[umair434]

SELECT tblAfternoon.Week, Max(tblAfternoon.[Caseflow Shelving Available]) AS [MaxOfCaseflow Shelving Available], Sum(tblAfternoon.[Caseflow Shelving Actioned]) AS [SumOfCaseflow Shelving Actioned], Max(tblAfternoon.[High Cube Available]) AS [MaxOfHigh Cube Available], Sum(tblAfternoon.[High Cube Actioned]) AS [SumOfHigh Cube Actioned], Max(tblAfternoon.[Prewab Report Available]) AS [MaxOfPrewab Report Available], Sum(tblAfternoon.[Prewab Report Actioned]) AS [SumOfPrewab Report Actioned]
FROM tblAfternoon
GROUP BY tblAfternoon.Week;

this is the query i came up with now - but how would i allow the users to enter the weekfrom and weekto to see only those records! not in between them

for example - if user enters Weekfrom: 1 and weekto: 4 then he can compare the data for week 1 with week 4 only!

thanks!

 

[umair434]

oh yay! fixed that part - I will post further questions so I dig down into these reports further - have a good weekend

 

[PNGBill]

Good news. You could post the solution for others to learn.

When including sql / code, click the hash symbol and insert the code between the code blocks that will appear on the forum post form.

 

[Galaxiom]

There are issues with the data structure. The field dte (presumably the date) and the Week# represent denormalized data. The week should be calculated from the date. As it stands there can be a conflict between the date and the Week Number and that always means the data is denormalized.

The use of the # or any other special character in a field or object name should always be avoided.

The ten Activity fields should be recorded in a single field in a related table. Many problems querying data start with using multiple fields to store what should be in a single field.