Report

[cbende2]

Hello all,

So I'm trying to create a report. I have 6 fields (Field1-Field6) and all are date fields.

I'm trying to get a count of each field for every week of the year that has a value <> null.

So if I have 5 records, and 2/5 are dates 1/1/2014 and 1/3/2014, in the first cell under "Field1Name" there would be a sum of "2" since 2 records contain dates between 1/1/2014 and 1/8/2014.

In the report I would like something like this:

[June7]

This data structure appears to be non-normalized and will cause problems.

In a normalized structure would calculate a field that has week identifier and use that field for grouping records. DatePart() function will return a calendar week number. Or a custom function can calculate the date of first day of week that a given date falls in. What is your first day of week - Sunday?

[cbende2]

It's normalized, trust me on this lol. I'm tracking a process dealing with 401(k) plans, and there are several different fields (CensusReceivedDate, 5500CompletedDate, TestingReviewedDate, etc). What I'm trying to figure out is have week1-week52, and a count of how many records for each field fall into week1-week52. Obviously the value of the date must be <> null in order to raise the count by 1.

In a normalized structure would calculate a field that has week identifier and use that field for grouping records.

[cbende2]

EDIT: I figured I would add an example of what the data in my table looks like, maybe this will help with how the report should function(Assume Field7 is primary key, it is just not shown)....

Field1	Field2	Field3	Field4	Field5	Field6
1/1/2014	1/3/2014	1/3/2014	1/9/2014	1/13/2014	1/13/2014
1/3/2014	1/4/2014	1/5/2014	1/12/2014	1/15/2014	1/15/2014
1/8/2014	1/9/2014	1/9/2014	1/13/2014	1/13/2014	1/15/2014
1/12/2014	1/13/2014	1/13/2014	1/18/2014	1/20/2014	1/20/2014

Unbound report would be something like this....

Field1Name	Field2Name	Field3Name	Field4Name	Field5Name	Field6Name
=Sum(WHERE [Field1] (<> null) AND (Between [1/1/2014] And [1/8/2014]))	=Sum(WHERE [Field2] (<> null) AND (Between [1/1/2014] And [1/8/2014]))	=Sum(WHERE [Field3] (<> null) AND (Between [1/1/2014] And [1/8/2014]))	=Sum(WHERE [Field4] (<> null) AND (Between [1/1/2014] And [1/8/2014]))	=Sum(WHERE [Field5] (<> null) AND (Between [1/1/2014] And [1/8/2014]))	=Sum(WHERE [Field6] (<> null) AND (Between [1/1/2014] And [1/8/2014]))
=Sum(WHERE [Field1] (<> null) AND (Between [1/8/2014] And [1/15/2014]))	etc..	etc..	etc..	etc..	etc..
etc.. for every week of year	etc.. for every week of year	etc.. for every week of year	etc.. for every week of year	etc.. for every week of year	etc.. for every week of year

This is what the output would be:

Field1Name	Field2Name	Field3Name	Field4Name	Field5Name	Field6Name
2	2	2	0	0	0
2	2	2	3	2	1
0	0	0	1	2	3

Hopefully this helps explain it a little more.

[June7]

I still don't think that is normalized. Consider:

ID	DateValue	Category
1	1/1/2014	1
1	1/3/2014	2
1	1/3/2014	3
1	1/9/2014	4
1	1/13/2014	5
1	1/13/2014	6
2	1/3/2014	1
2	1/4/2014	2
2	1/5/2014	3
2	1/12/2014	4
2	1/15/2014	5
2	1/15/2014	6
3	1/8/2014	1
3	1/9/2014	2
3	1/9/2014	3
3	1/13/2014	4
3	1/13/2014	5
3	1/15/2014	6

Now build a query that calculates the week identifier for each record. Then do a CROSSTAB query with the Category as column header and the week identifier as row header and count of ID for data.

Otherwise, build a report with 312 textboxes to do the weekly calcs for each category.

[cbende2]

Ughh, I see now..

Rats... I think it may be a little late to convert the table to this...

But just for the sake of knowledge, what would the query look like?

[June7]

A UNION query can rearrange the data into a normalized structure. The weekly identifier calc can be included in the UNION. If the db is multi-year, will also need to calc year.

SELECT ID, Field1 AS DateValue, 1 AS Category, Year(Field1) As Yr, DatePart("ww", Field1) AS WkID FROM table
UN ION SELECT ID, Field2, 2, Year(Field2), DatePart("ww", Field2) FROM table
UN ION SELECT ID, Field3, 3, Year(Field3), DatePart("ww", Field3) FROM table
UN ION SELECT ID, Field4, 4, Year(Field4), DatePart("ww", Field4) FROM table
UN ION SELECT ID, Field5, 5, Year(Field5), DatePart("ww", Field5) FROM table
UN ION SELECT ID, Field6, 6, Year(Field6), DatePart("ww", Field6) FROM table;

Then use the UNION query as source for CROSSTAB. Apply filter criteria for the year and/or include the year as another row header field.

[cbende2]

Thank you, I'm kind of bewildered by this Union query, I've never used one before, can you explain what its doing? And how does it calculate the week?

[June7]

The UNION query is rearranging the fields. A UNION is usually used to combine tables but it can be used on one table as demonstrated.

DatePart() is an intrinsic function. Search Access Help or web for more info on it.

[cbende2]

Gotcha, Thanks June7

[June7]

I had error in the query for calculating year. Fixed the post.