Access Report Help - Grouping Data by Week Ending Date

[derekdeben]

Hi,

I have created a report that totals the number of days it took a product to ship by percentage by a date range for a specific location. My data comes from a query with the following headers:

[week ending date] [Packages Entered] [Entry Date] [Location] [Days In Transit]

I used a count iff to calculate the following headers in the report. This is a percent of the total number of packages.

[# of days <=4] [# of days <=5] [# of days <=6]

The report runs fine and gives me the info a need on a week to week basis but I need it to break out the data based on w/e. I am not sure how to go about this.

[W/E Date] [Packages] [Location] [# of days<=4] [# of days<=5] [# of days<=6]

23-Dec 500 New York 25% 50% 100%
16-Dec 475 New York 35% 75% 100%
Any help would be appreciated.

[nico5038]

Why not add these fields to your query:
select IIF(WE <= 4,1,0) as Count4, IIF(WE <= 5,1,0) as Count5, etc.

Now you can divide the Count4 and Count5 fields in the report with the value from <=6 (I assume that's the case for all) and you have your percentages.

Nic;o)

[derekdeben]

I have the % calculating. What I cannot figure out is how to have the report break the data out by w/e date. Currently if I select a date range of 12/2 - 12/30 my report does not break it out by week ending it sums everything together. How would I go about listing it by w/e ending date.

Thanks,

Derek

[nico5038]

You need to make the query a GroupBy query, or use the [= looking groeping button that will allow the creation of a groupheader and footer.

When you have a GroupBy query, make sure that the W/E is part of the GroupBy, when you have a normal query press the "E" looking button and use GroupBy from the combo for W/E and SUM for the others.

When still problems, please post you SQL so we can have a look.

Nic;o)

[derekdeben]

Hi,

I have the query as a group by but it is still not working. Here is the sql.

SELECT WeekEnding.[W/E], (DatePart("ww",[W/E])) AS WorkWeek, [112606_123006_R aw].ZIP_5_DIG, [112606_123006_R aw].ENTRY_UNIT, [112606_123006_R aw].STOPCLOCK_FLG, [112606_123006_R aw].DELIVERED_FLG, [112606_123006_R aw].PU_DATE, [112606_123006_R aw].ENTRY_DATE, [112606_123006_R aw].DAYS_C
FROM 112606_123006_R aw LEFT JOIN WeekEnding ON [112606_123006_R aw].ENTRY_DATE = WeekEnding.Date
GROUP BY WeekEnding.[W/E], (DatePart("ww",[W/E])), [112606_123006_R aw].ZIP_5_DIG, [112606_123006_R aw].ENTRY_UNIT, [112606_123006_R aw].STOPCLOCK_FLG, [112606_123006_R aw].DELIVERED_FLG, [112606_123006_R aw].PU_DATE, [112606_123006_R aw].ENTRY_DATE, [112606_123006_R aw].DAYS_C
HAVING ((([112606_123006_R aw].ENTRY_UNIT)=[Enter SCF]) AND (([112606_123006_R aw].ENTRY_DATE) Between [Enter Beginning Date] And [Enter Ending Date]));

[nico5038]

This groupby will make unique combinations of:
WeekEnding.[W/E], (DatePart("ww",[W/E])) AS WorkWeek and ZIP_5_DIG and ENTRY_UNIT and STOPCLOCK_FLG and DELIVERED_FLG and PU_DATE and ENTRY_DATE and DAYS_C
When you want just W/E, then you'll have to supply for the other fields an aggregation function like MIN(), MAX(), SUM(), etc.

Nic;o)

[derekdeben]

I tried both count & sum and it will not work???????????

[nico5038]

Try:

SELECT (DatePart("ww",[W/E])) AS WorkWeek, Sum( [112606_123006_R aw].DAYS_C)
FROM 112606_123006_R aw LEFT JOIN WeekEnding ON [112606_123006_R aw].ENTRY_DATE = WeekEnding.Date
GROUP BY (DatePart("ww",[W/E]))
HAVING ((([112606_123006_R aw].ENTRY_UNIT)=[Enter SCF]) AND (([112606_123006_R aw].ENTRY_DATE) Between [Enter Beginning Date] And [Enter Ending Date]));

It's the WeekEnding.[W/E] that's disturbing the grouping.

Nic;o)