469,327 Members | 1,242 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,327 developers. It's quick & easy.

Access Report Help - Grouping Data by Week Ending Date


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.
Jan 16 '07 #1
7 8633
3,080 Expert 2GB
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.

Jan 17 '07 #2
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.


Jan 17 '07 #3
3,080 Expert 2GB
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.

Jan 17 '07 #4

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_Raw].ZIP_5_DIG, [112606_123006_Raw].ENTRY_UNIT, [112606_123006_Raw].STOPCLOCK_FLG, [112606_123006_Raw].DELIVERED_FLG, [112606_123006_Raw].PU_DATE, [112606_123006_Raw].ENTRY_DATE, [112606_123006_Raw].DAYS_C
FROM 112606_123006_Raw LEFT JOIN WeekEnding ON [112606_123006_Raw].ENTRY_DATE = WeekEnding.Date
GROUP BY WeekEnding.[W/E], (DatePart("ww",[W/E])), [112606_123006_Raw].ZIP_5_DIG, [112606_123006_Raw].ENTRY_UNIT, [112606_123006_Raw].STOPCLOCK_FLG, [112606_123006_Raw].DELIVERED_FLG, [112606_123006_Raw].PU_DATE, [112606_123006_Raw].ENTRY_DATE, [112606_123006_Raw].DAYS_C
HAVING ((([112606_123006_Raw].ENTRY_UNIT)=[Enter SCF]) AND (([112606_123006_Raw].ENTRY_DATE) Between [Enter Beginning Date] And [Enter Ending Date]));
Jan 17 '07 #5
3,080 Expert 2GB
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.

Jan 17 '07 #6
I tried both count & sum and it will not work???????????
Jan 17 '07 #7
3,080 Expert 2GB

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

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

Jan 17 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Brian Coy | last post: by
7 posts views Thread by manning_news | last post: by
9 posts views Thread by prakashwadhwani | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.