This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.
Very complicated report - Need help
I hope I can explain the challenge. I am trying to write an Access report which has to show 52 rows of data. But first of all prior to the opening of the report is a form which asks the user to enter the current year. Example: "2012". Then going down the report vertically I need to show the week number in the first column. So I guess I need VBA code to loop 52 times but increment by weeks or 7 days each row. And then next to the week number I need a text box filled with a complicated formula. Here is my attempt at explaining the formula...
"Molds" = sum total of ([Qty] from "tblCast" divided by [Patterns_On] from "tblPartsMasters") for that week (week 1, week 2, week 3, etc.) where [Molder] from "tblCast" = "Airset Floor" and [Cust] = "ABCCompany". [Cust] is from "tblPartsMasters" joined to "tblCast" by the field [PartN] in "tblCast" and [Part_No] in "tblPartMasters". Both [PartN] and [Part_No] are text fields.
So the final report will look like:
Week Molds
1 4
2 16
3 23
4 7
etc through 52 weeks.
I suspect this question should be worth much more than 500 points but if anyone can assist I sure will appreciate it. I actually have to create 13 more text fields like the one I've explained horizontally on the report but if someone can help with this first one I think I can take it from there.
--Steve
"Molds" = sum total of ([Qty] from "tblCast" divided by [Patterns_On] from "tblPartsMasters") for that week (week 1, week 2, week 3, etc.) where [Molder] from "tblCast" = "Airset Floor" and [Cust] = "ABCCompany". [Cust] is from "tblPartsMasters" joined to "tblCast" by the field [PartN] in "tblCast" and [Part_No] in "tblPartMasters". Both [PartN] and [Part_No] are text fields.
So the final report will look like:
Week Molds
1 4
2 16
3 23
4 7
etc through 52 weeks.
I suspect this question should be worth much more than 500 points but if anyone can assist I sure will appreciate it. I actually have to create 13 more text fields like the one I've explained horizontally on the report but if someone can help with this first one I think I can take it from there.
--Steve
Asked:
4-
4
- +2
1 Solution
This would be much easier if you could provide a copy of the database. Just remove any private information first.
Will be good if you upload DB with some sample data.
You need query as report source. As I've adviced you in previous question, you need table with numbers from 1 to 52 if you like to show all weeks, independently from data for this week exists or not. In first query - calculate week number and group by this number. In other column - calculate Molds. In second query left join table witn numbers (1-52) and your query. If you like to have 0, use nz([Molds],0) for molds column.
You should also take in mind that fist and last week in year may have less then 7 days.
You need query as report source. As I've adviced you in previous question, you need table with numbers from 1 to 52 if you like to show all weeks, independently from data for this week exists or not. In first query - calculate week number and group by this number. In other column - calculate Molds. In second query left join table witn numbers (1-52) and your query. If you like to have 0, use nz([Molds],0) for molds column.
You should also take in mind that fist and last week in year may have less then 7 days.
Attached... a VERY stripped down sample of the database.
SampleDatabase.zip
SampleDatabase.zip
May be you need something like this. As I can see you like to have all molders in report. You can use transform query. Check Mold calculation in qryMold. May be logic is wrong.
SampleDatabase.zip
SampleDatabase.zip
Some corrections:
1. Error in week calculation query.
2. You can have more then 52 weeks in a year. For example, last week of 2012 has number 53.
SampleDatabase.zip
1. Error in week calculation query.
2. You can have more then 52 weeks in a year. For example, last week of 2012 has number 53.
SampleDatabase.zip
To als315:
Using your last SampleDatabase can you help me get the total numbers in each total column also? I'm having trouble figuring out how you did this.
Also, is the form being used to determine which year's data to go after?
Thank you so much for what you've done already.
--Steve
Using your last SampleDatabase can you help me get the total numbers in each total column also? I'm having trouble figuring out how you did this.
Also, is the form being used to determine which year's data to go after?
Thank you so much for what you've done already.
--Steve
Hmm ... the last days of this December belongs to week 2013W01.
And ISO_WeekCount(Date) => 52
Year 2009 and 2015, however, had/will have 53 weeks.
And ISO_WeekCount(Date) => 52
Year 2009 and 2015, however, had/will have 53 weeks.
Public Function ISO_WeekCount( _
ByVal datYear As Date) _
As Byte
' Calculates number of weeks in year of datYear according to the ISO 8601:1988 standard.
'
' May be freely used and distributed.
' 2001-06-26. Gustav Brock, Cactus Data ApS, CPH
Dim bytISO_Thursday As Byte
' No special error handling.
On Error Resume Next
bytISO_Thursday = Weekday(vbThursday, vbMonday)
datYear = DateSerial(Year(datYear), 12, 31)
' Subtract one week if datYear is in week no. 1 of next year.
datYear = DateAdd("ww", Weekday(datYear, vbMonday) < bytISO_Thursday, datYear)
ISO_WeekCount = DatePart("ww", datYear, vbMonday, vbFirstFourDays)
End Function
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
Join & Write a Comment Already a member? Login.
Featured Post
This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.
Tackle projects and never again get stuck behind a technical roadblock.
Join Now