Very complicated report - Need help
Posted on 2012-12-30
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:
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.