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.
7 9010
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)
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
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)
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_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]));
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)
I tried both count & sum and it will not work???????????
Try:
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.
Nic;o)
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: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |