472,798 Members | 1,271 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,798 software developers and data experts.

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 8913
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

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

Similar topics

by: mark | last post by:
How do I get all fields on one page of a report? I have a report that has a column for each day of the week and 6 records for each day. I need each weekday's records returned on only one detail...
by: Brian Coy | last post by:
I am creating a database to track scrap on a daily basis at my plant. I have to provide a weekly scrap report with the amount of each part scrapped per day. I have the basic database set up, and...
by: Mike MacSween | last post by:
tblCourses one to many to tblEvents. A course may have an intro workshop (a type of event), a mid course workshop, a final exam. Or any combination. Or something different in the future. At...
by: Bob | last post by:
I wish to group data on a report by week, month and year. Crystal reports has this ability as a built in function. Is there a quick way to do this in Access97/2000 VBA reports ? Thank you in...
by: manning_news | last post by:
I've got a report that's not sorting correctly. I build a SQL statement and assign it to the recordsource in the Open event, sorting the data the way the user chooses. The user can choose up to 3...
by: sara | last post by:
I have reports that run from a form where the user can choose a date range, or they run automatically for a week in the "Weekly Reports" option. I created 2 queries and 2 reports - one query...
by: Patonar | last post by:
Hi, I have a query which works out which orders are due to be delivered in a particular week. I would like to display this by week commencing which i can do with the following WHERE condition in...
by: Johna4969 | last post by:
I need to create a report using data in a date field as a header. The field is a week ending date with a corisponding hours worked for an employee. The report needs to list the fields as follows; ...
by: prakashwadhwani | last post by:
Hi !! I'm about to develop a new project for a client. Should I go about it in Access 2003 or 2007 ? Purchasing it either for me or for my client is not a major consideration here ... what I'd...
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
by: erikbower65 | last post by:
Here's a concise step-by-step guide for manually installing IntelliJ IDEA: 1. Download: Visit the official JetBrains website and download the IntelliJ IDEA Community or Ultimate edition based on...
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
by: Rina0 | last post by:
I am looking for a Python code to find the longest common subsequence of two strings. I found this blog post that describes the length of longest common subsequence problem and provides a solution in...
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
by: lllomh | last post by:
How does React native implement an English player?
by: Mushico | last post by:
How to calculate date of retirement from date of birth
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.