Populating weekly reports

[somar]

Hi,

So I am working on this project where I have to generate weekly reports for an entire month Based on the inputs of Month and Year.

So For example If I specify: August 2014, the form figures out there are 6 weeks in August and then generate 6 reports (1 for each weeks with specific data for each week).

The way I am currently doing this is by running some nested loops to go through each day of the week, figure out if there is data and then populate each text box. (I have attached a PDF of the report so you can get an idea of what is looks like, Each box is a unique text box).

The problem I am having is that the reports take a long time to load (I know this is caused by the loops). I was trying to figure out if there is a faster way to do this? Thank you.

The code I am using is:

 
Dim FirstDayName As String
Dim i1 As Integer
Dim CurrentDate As Date
Dim HasBeenPopued As Integer
Dim S1, S2, D1, D2 As Integer
 
Option Compare Database
 
Private Sub AddData()
 
'Populating the report with information'
D1 = 1
 
Do While D1 <= 7
 
    DayNumber = CStr(D1)
    DateName = "D" + DayNumber + "Date"
 
    If IsDate(Me.Controls(DateName)) Then
        If DCount("*", "ManHoursQ", "[Dt] = " + DateName) > 0 Then
 
            S1 = 1
 
            Do While S1 <= 3
 
                ShiftNumber = CStr(S1)
                ShiftName = "D" + DayNumber + "S" + ShiftNumber
 
                If DCount("*", "ManHoursQ", "[Dt] = " + DateName + " AND [Shift] = " + ShiftName) > 0 Then
 
                    MHOName = "D" + DayNumber + "S" + ShiftNumber + "MHO"
                    MHDName = "D" + DayNumber + "S" + ShiftNumber + "MHD"
                    ADMNName = "D" + DayNumber + "S" + ShiftNumber + "ADMN"
                    MCTName = "D" + DayNumber + "S" + ShiftNumber + "MCT"
                    COMPName = "D" + DayNumber + "S" + ShiftNumber + "COMP"
                    QLName = "D" + DayNumber + "S" + ShiftNumber + "QL"
                    LABName = "D" + DayNumber + "S" + ShiftNumber + "LAB"
                    MNTName = "D" + DayNumber + "S" + ShiftNumber + "MNT"
                    ENGRName = "D" + DayNumber + "S" + ShiftNumber + "ENGR"
                    FACName = "D" + DayNumber + "S" + ShiftNumber + "FAC"
                    EXECName = "D" + DayNumber + "S" + ShiftNumber + "EXEC"
                    ACCTName = "D" + DayNumber + "S" + ShiftNumber + "ACCT"
                    ITName = "D" + DayNumber + "S" + ShiftNumber + "IT"
                    HRName = "D" + DayNumber + "S" + ShiftNumber + "HR"
                    SCName = "D" + DayNumber + "S" + ShiftNumber + "SC"
                    RNDName = "D" + DayNumber + "S" + ShiftNumber + "RND"
                    CUSTName = "D" + DayNumber + "S" + ShiftNumber + "CUST"
                    TOCName = "D" + DayNumber + "S" + ShiftNumber + "TOC"
                    MHFRName = "D" + DayNumber + "S" + ShiftNumber + "MHFR"
                    COFFName = "D" + DayNumber + "S" + ShiftNumber + "COFF"
                    NCNName = "D" + DayNumber + "S" + ShiftNumber + "NCN"
                    COMMName = "D" + DayNumber + "S" + ShiftNumber + "COMM"
 
 
 
                    If IsDate(Me.Controls(DateName)) Then
                        CritStr = "[Dt] = " + DateName + " AND [Shift] = " + ShiftName
                        Me.Controls(MHOName) = DSum("[MHOrd]", "ManHoursQ", CritStr)
                        Me.Controls(MHDName) = DSum("[MHDel]", "ManHoursQ", CritStr)
                        Me.Controls(ADMNName) = DSum("[Admin]", "ManHoursQ", CritStr)
                        Me.Controls(MCTName) = DSum("[MaterialControl]", "ManHoursQ", CritStr)
                        Me.Controls(COMPName) = DSum("[Compounding]", "ManHoursQ", CritStr)
                        Me.Controls(QLName) = DSum("[Quality]", "ManHoursQ", CritStr)
                        Me.Controls(LABName) = DSum("[Laboratory]", "ManHoursQ", CritStr)
                        Me.Controls(MNTName) = DSum("[Maintenance]", "ManHoursQ", CritStr)
                        Me.Controls(ENGRName) = DSum("[Engineering]", "ManHoursQ", CritStr)
                        Me.Controls(FACName) = DSum("[Facilities]", "ManHoursQ", CritStr)
                        Me.Controls(EXECName) = DSum("[Executive]", "ManHoursQ", CritStr)
                        Me.Controls(ACCTName) = DSum("[Accounting]", "ManHoursQ", CritStr)
                        Me.Controls(ITName) = DSum("[IT]", "ManHoursQ", CritStr)
                        Me.Controls(HRName) = DSum("[HR]", "ManHoursQ", CritStr)
                        Me.Controls(SCName) = DSum("[SupplyChain]", "ManHoursQ", CritStr)
                        Me.Controls(RNDName) = DSum("[RND]", "ManHoursQ", CritStr)
                        Me.Controls(CUSTName) = DSum("[CustomerService]", "ManHoursQ", CritStr)
                        Me.Controls(TOCName) = DSum("[TotalOnClock]", "ManHoursQ", CritStr)
                        Me.Controls(MHFRName) = DSum("[MHFR]", "ManHoursQ", CritStr)
                        Me.Controls(COFFName) = DSum("[COffs]", "ManHoursQ", CritStr)
                        Me.Controls(NCNName) = DSum("[NCNs]", "ManHoursQ", CritStr)
 
                        If [Forms]![ManHoursRepGen]![AgencyCombo] = "All" Then
                        Else
                            Me.Controls(COMMName) = DLookup("[Comments]", "ManHoursQ", CritStr)
                        End If
 
                    End If
 
                End If
 
                S1 = S1 + 1
 
            Loop
 
        End If
 
    End If
 
    D1 = D1 + 1
 
Loop
 
HasBeenPopued = 1
 
End Sub
 
Private Sub InitiateReport()
 
Dim i2 As Integer
 
'Clearing All Date Fields'
i2 = 1
Do While i2 <= 7
    DayNumber = CStr(i2)
    ControlName = "D" + DayNumber + "Date"
    Me.Controls(ControlName) = ""
    i2 = i2 + 1
Loop
 
'Figures out the Week name and number of the First and last day of the month'
CurrentDate = GWk1StartDate
FirstDayName = WeekdayName(Weekday(CurrentDate))
 
 
If FirstDayName = "Sunday" Then
    i1 = 1
End If
 
If FirstDayName = "Monday" Then
    i1 = 2
End If
 
If FirstDayName = "Tuesday" Then
    i1 = 3
End If
 
If FirstDayName = "Wednesday" Then
    i1 = 4
End If
 
If FirstDayName = "Thursday" Then
    i1 = 5
End If
 
If FirstDayName = "Friday" Then
    i1 = 6
End If
 
If FirstDayName = "Saturday" Then
    i1 = 7
End If
 
'Setting Dates'
Do While i1 <= 7
    DayNumber = CStr(i1)
    ControlName = "D" + DayNumber + "Date"
    Me.Controls(ControlName).Value = CurrentDate
    CurrentDate = CurrentDate + 1
    i1 = i1 + 1
Loop
 
'Setting Shift Number'
 
D2 = 1
 
Do While D2 <= 7
 
S2 = 1
 
    Do While S2 <= 3
        ShiftName2 = "D" + CStr(D2) + "S" + CStr(S2)
        Me.Controls(ShiftName2) = CStr(S2)
        S2 = S2 + 1
    Loop
 
D2 = D2 + 1
Loop
 
End Sub
 
Private Sub Report_Activate()
 
 
 
End Sub
 
 
Private Sub Report_Click()
 
If HasBeenPopued = 0 Then
 
    Call AddData
 
End If
 
End Sub
 
Private Sub Report_GotFocus()
 
End Sub
 
Private Sub Report_Load()
 
Call InitiateReport
 
End Sub
 
 
Private Sub Report_Open(Cancel As Integer)
 
HasBeenPopued = 0
 
End Sub
 
 

[Rabbit]

Use a query to return the data needed for each day of the month. There's no need for a lookup query for every single control. That's like running hundreds of separate queries to populate everything when all you need is 1.

The query just needs to return a week number and a day number for each row. Then you aggregate all your data by the week and day number. If you want blank rows for where there is no data but still want to represent it in the report, you just need to outer join a recordset that has all the combinations of week numbers and day numbers.

This is the general gist of what you will need to do. If you need further detail, we will need to know the table structures a lot better.

[jforbes]

If you really want to speed things up, you can use a Aggregate Query (A Select Query with a Group By clause) as the basis of your Report. I think the trick that needs to be figured out is how to Format the Date so that it gets Grouped correctly. It's been a while since I've done this, so something like:

SELECT 
  Format([Dt],"dd") AS [Day]
, ManHours.Shift
, Sum(ManHours.MHOName) AS SumOfMHOName
, Sum(ManHours.MHDName) AS SumOfMHDName
, Sum(ManHours.ADMNName) AS SumOfADMNName
FROM ManHours
GROUP BY Format([Dt],"dd"), ManHours.Shift;
 

[twinnyfo]

somar,

I think the key you are looking for is how to get the number of week within each month.

Using the advice from both Rabbit and JForbes, combine their solutions, using the code below for your query:

SELECT Format([TestDate],"yyyy") AS [Year], 
    Format([TestDate],"mmmm") AS [Month], 
    Weekday([Testdate]) AS DayOfWeek, 
    Day([TestDate]) AS MonthDay, 
    -Int(([DayOfWeek]-[MonthDay]+1)/7)+1 AS Week
FROM tblWorkHours;

Just replace "tblWorkHours" with your Table Name and "TestDate" with the name of your field that contains the date.

Here are the results for August:

Year  Month   DayOfWeek  MonthDay  Week
2014  August  6          1           1
2014  August  7          2           1
2014  August  1          3           2
2014  August  2          4           2
2014  August  3          5           2
2014  August  4          6           2
2014  August  5          7           2
2014  August  6          8           2
2014  August  7          9           2
2014  August  1          10          3
2014  August  2          11          3
2014  August  3          12          3
2014  August  4          13          3
2014  August  5          14          3
2014  August  6          15          3
2014  August  7          16          3
2014  August  1          17          4
2014  August  2          18          4
2014  August  3          19          4
2014  August  4          20          4
2014  August  5          21          4
2014  August  6          22          4
2014  August  7          23          4
2014  August  1          24          5
2014  August  2          25          5
2014  August  3          26          5
2014  August  4          27          5
2014  August  5          28          5
2014  August  6          29          5
2014  August  7          30          5
2014  August  1          31          6

Then, use your query results to poulate your report. It will be much wickedlier faster!!!

[somar]

So the issue isn't figuring out the dates and the week number, that I have figured out (And I may not be understanding what you are trying to explain here, I am pretty new at using access, and not very adept at it). The trouble I am having is figuring out how to populate the query with this and also how would I add code to a query (I know how to set criteria).

As Rabbit suggested I decided to upload the database I am working on so you guys can get a better idea of my table, report, and query is setup.

Thanks a lot for your responses so far, I really appreciate the help.

[zmbd]

somar
We do ask that you make the effort and show your work, attaching the database file is not usually needed nor desirable as most of the experts provide our help while at work; thus, usually unable to open the file.

Providing the detail for the tables in question can be provided:
[tbl_one]
[PK](autonumber)
[fields...}

[tbl_two]
[PK](autonumber)
[fk_tbl_one](numeric-long)
[example_name](text(25))
[fields...}

etc...

Writing Queries is a very basic skill set that you must master in order to really benefit from any database. Please work thru the following tutorial: Create queries for a new database

This is also a site that covers database development fairly well... please follow the link to the ACC2003 to learn about normalization and proper table setups... microsoft office access 2010 - power tutorials

Once you have written your SQL (either via the GUI-Query Editor or by using the SQL-View), please, cut and paste it here, format it using the [CODE/] button in the toolbar and we can most easily point you in the right direction.

As for writing "code" behind a query, that is a strange statement. One can write custom functions and use them within a query; however, that is the extent within Access.

[twinnyfo]

somar,

I did get a chance to look at your reports, and they are designed very poorly and will cause you many problems as you continue with this project. You have six identical reports, one for each potential week of the month and each report has hundreds of controls. This is a poor design. You need to redesign the reports so that each set of data cascades through the days of the weeks (even down to the shifts).

Each detail section of the report should cover one shift. You should have multiple header/footer combinations, with sums included in the footer.

Your summing groups should be:

Shift, Day, Week, Month, Year (if you have an annual report).

This can all be done on ONE report, setting parameters for the dates covered in you report generating form. I've played around with it, and it is doable, using the formulas we have provided thus far.

Let us know if you come across any snags.

[somar]

Hi, Thanks for taking a look at it. Yeah, I realize that this is a pretty bad design and that is the reason I am trying to get help to do this.

I think I understand how to make the get the report to populate by using the query (without running loops), what I am struggling with right now is figuring out how to make the report leave the blank fields for dates that do not contain any data (for example if there is no data for 8/3/2014, it should still show blanks for that date).

So I combined twinnyfo and jforbes code together to come up with this:

 
SELECT Format([Dt],"yyyy") AS [Year]
, Format([Dt],"mmmm") AS [Month]
, Weekday([Dt]) AS DayOfWeek
, Day([Dt]) AS MonthDay
, -Int(([DayOfWeek]-[MonthDay]+1)/7)+1 AS Week
, ManHours.Shift
, Sum(ManHours.[MHOrd]) AS SumMHO
, Sum(ManHours.MHDel) AS SumMHD
, Sum(ManHours.Admin) AS SumOfADMN
, Sum(ManHours.MaterialControl) AS SumOfMC
, Sum(ManHours.Compounding) AS SumOfCOMP
, Sum(ManHours.Quality) AS SumOfQL
, Sum(ManHours.Laboratory) AS SumOfLAB
, Sum(ManHours.Maintenance) AS SumOfMAINT
, Sum(ManHours.Engineering) AS SumOfENGR
, Sum(ManHours.Facilities) AS SumOfFAC
, Sum(ManHours.Executive) AS SumOfEXEC
, Sum(ManHours.Accounting) AS SumOfACCT
, Sum(ManHours.IT) AS SumOfIT
, Sum(ManHours.HR) AS SumOfHR
, Sum(ManHours.SupplyChain) AS SumOfSuC
, Sum(ManHours.[RND]) AS SumOfRND
, Sum(ManHours.CustomerService) AS SumOfCS
, Sum(ManHours.TotalOnClock) AS SumOfTOC
, Sum(ManHours.MHFR) AS SumOfMHFR
, Sum(ManHours.COffs) AS SumOfCOffs
, Sum(ManHours.NCNs) AS SumOfNCNs
FROM ManHours
GROUP BY Format([Dt],"yyyy"), Format([Dt],"mmmm"), Weekday([Dt]), Day([Dt]), -Int(([DayOfWeek]-[MonthDay]+1)/7)+1, ManHours.Shift;
 
 

But as I said, it does not give me the blank dates. I was thinking of making a query that generated all the dates of a month and then combining it with this one, would that be a good way to go about it?

[Rabbit]

Yes, you can do that to get the missing dates. Which is what I meant in the second paragraph of post #2. But you don't really need to create a "query" of valid dates. Just create a table with 31 rows numbered 1 to 31. Just remember to check first that the date that is created is a valid date. You can use the IsDate function for that.

[somar]

Hi Guys, I was finally able figure out how to get the reports to generate exactly how I needed them to without using any loops. Thanks a lot for all your help with this.