Solved

Very complicated report - Need help

Posted on 2012-12-30
11
357 Views
Last Modified: 2013-01-04
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
0
Comment
Question by:SteveL13
11 Comments
 

Author Comment

by:SteveL13
ID: 38731032
One additional comment:  If the "Molds" answer is "0" then I need a "0" on the report.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38731039
How do you know what week each record belongs to?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38731042
This would be much easier if you could provide a copy of the database.  Just remove any private information first.
0
 

Author Comment

by:SteveL13
ID: 38731045
Peter57r:  Sorry.  By [Date] in "tblCast".
0
 
LVL 39

Expert Comment

by:als315
ID: 38731046
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.
0
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:SteveL13
ID: 38731061
Attached... a VERY stripped down sample of the database.
SampleDatabase.zip
0
 
LVL 39

Expert Comment

by:als315
ID: 38731111
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
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 38731137
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
0
 

Author Comment

by:SteveL13
ID: 38732661
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
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 38732662
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.

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

Open in new window

/gustav
0
 
LVL 39

Expert Comment

by:als315
ID: 38733260
Steve: what total value you like to use? You have Total in each line and in each column. Can you show expected result?

Form is used to calculate max week number in selected year and limit query to this number.
You should analyze Gustav's comment. May be you had to change week number calculation.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now