I have this maintenance database for my company that lets our maintenance team know what machines are up and running, what has tags requesting work, blah blah blah.
Whenever someone does any work to a machine, they fill out this status change form that logs what work was done, who did it, and the new status of the machine (up for production, fried, what have you). On this form is a subform so that the person can log their hours. This subform is linked to a table that simply stores the person's name, the hours they worked, and a record id linking the data to the status change record.
OK, I also have a report that can display each status change record. So the report shows what was done, which ticket (request for work to be done) the status change is associated with, and a little sub report that just spits out the data in the subtable I just described. So it looks like this:
Person Working Hour Worked
Jacob Marley 4
Bob Cratchette .24
And so forth for each status Change record.
Here's the twist. If we look at the report and ask it for all status changes for the week or month, we can easily come up with 10 pages of information. It would really help if I can put a summary in the Report Footer section that will break down each name that appears in the report and a sum of how many hours they worked, and then maybe a grand total of work at the very end. This way we can see how many hours our maintenance team logged that week or month so we can report it to budget.
This doesn't seem like it should be too difficult, but it's Monday and the brain's just not moving like it should.
How's this for an idea? If I create a table, call it Report Summary Table. On the original report's Open event, I can delete all the entries in this table so I know it's empty. Then, whenever a record is analyzed in the report, I can add the Person Working and Hours Worked into the table if it doesn't already exist, or simply add to the existing Hours Worked if the person is already in the table. Then I can base the report summary subreport on the Report Summary Table.
Pretty slick huh?
Well, first off, how and when can you get to a report's recordset? Also, if the values are dynamically added while the report is being created, will the end Report Summary look at the new table after the report is created (good values) or before (empty table).
And.. is there a simpler way? I tend to make things a little more complicated than they have to be.
You can do this by grouping the status change records by Person and Date and insert text boxes in the footers of those group sections with recordsource of "=Sum(HoursWorked)". Access is smart enough to sum over the appropriate groups.
To get this information in the report footer, I would suggest creating a subreport and doing what I explained above, and just hide the detail records. Or if you don't want to create a new report, you can do what I do:
All of my reports have a parameter input form that opens to select date ranges, etc. It also lets me change formatting of my reports. So I put checkboxes or radio buttons on the input form, and then in the report open event, I have it check the value of the input form and change different formatting of the report. For example, let's say you just wanted to create one report for your machine status, but you wanted either a detail or a summary report.
Create an input form frmdlgMaintenance
Create a checkbox chkShowDetails
I might be able to force Access to do what you described, but it's not quite that simple. See, the status change record doesn't store the person and hours in its own table because there could be multiple people logging hours on one status change record. (If I recruited Jimmy Hoffa to help me pour some concrete for 2 hours, I don't want to create a seperate record for both of us).
As a result, all of the labor data is shown in a sub report in the detail section of the status change report. The trick here is telling Access to summarize the HoursWorked for each individual PersonWorking accross all of the Status Change Records.
When I try to tell the Report Summary (subreport in the report footer) to base its data on the subreports, it just spits back all of the data in my LaborData table, so I get the PersonWorking and HoursWorked for every Status Change record in the db.
I don't need to worry about specific time period or anything like that. I have a filter page that takes care of all that.
Just like you described in your first post, I have a form wher theuser populates specific search criteria (date ranges, tool IDs, work Request IDs, etc.) and clicks checkmarks to view forms, summarized reports, detailed reports.
Based on what the user inputs, I create an sql statement and put it in the Filter parameter before opening the report/form.
So the records that are shown in the report are already categorized. I just need to summarize all the info in the sub reports at the end.
I'm still toying with my earlier idea. The report's detail_print event seems to trap a specific record. I'm still writing the VBA code, but I think I can open the LaborData table based on the individual record ID and pull all of the PersonWorking/HoursWorked for that record. Then, I can iterate through the resulting recordset and compare each name with the names in a Record Summary Table (emptied when the report opens). If the name is found, just add the HourWorked. Otherwise, I'll add the name and hours into the table and continue on.
We'll see if the Record Summary Subreport reads the Record Summary Table after the detail portions are generated or beforehand. Hopefully I can call a requery after the fact if that latter turns out to be true.
(This is a lot of tricky ADO navigation, and it could slow down if someone decided to view every Status Entry record in the db, so if you have another suggestion, I'll gladly try it out).
The report that I currently have shows every status record that results from the users query. So the detail section of the report (each record) shows the Status Change ID, The Work Request ID (multiple status changes exist for a single work request), all the fields of the individual Status Change record (what action was taken, any comments, the resulting tool status, date and time, ...). Each Status Change record also has a subreport that simply shows the people who worked and their hours, based on the LaborData table (LaborData Table has three fields, Status ID, Person Working, Hours Worked).
So I need to summarize each subreport based on the Person Working and sum up thier individual Hours Worked.
Typically, the same person will appear on most or all of the Status Change record (our maintenance supervisor), so I need it to recognize the name from subreport to subreport.
I think it might be easier to not think of it as trying to pull information from the subreports, but to create a new subreport with the same recordsource and group and summarize the information that way. This way you can group it just by person and not even worry about status change records and stuff like that.
I finished all the complicated ADO stuff and set a breakpoint so I could test it out. Opened a report, and BAM!... nothing happened. So I went back to the VBA screen thinking I just forgot the breakpoint, and everything was gone. Forgot that little Save button!!!
Really wanted that to work. Anyway, I'll see about your suggestion before I try to recode.
Anyway, I tried to put the subreport in the footer and simply base its records on the LaborData table. It is smart enough to group the records by people, but for some strange reason it only matched the last record on the report itself. So it only displayed the PersonWorking/HoursWorked data for the last Status Change record in the report.
Also, I recoded the complicated stuff I was trying earlier. It created the temporary table perfectly. So I changed the subreport (summary report in the footer) recordsource to the temporary table and I get absolutely nothing. The table is there, but the data is blank.
One of two possibilities... naturally, I have to erase all the data in the temporary table and re-create it based on whatever query results come up in the report. If the final subreport is generated before the detail_Print events occur, then it reads an empty table.
Or, if Access is attempting to link the data in the table to something on the main report itself and coming up empty because the two are not implicitly related.
As I said in my last post, the temporary report summary table gets populated perfect within the main report's Detail_Print event. However, the subreport in the footer, which is set to simply display the report summary table, is completely empty. When I open the subreport explicitly, however, it is fully populated with the correct PersonWorking/HoursWorked pairs.
I put debug.print statements through all the events in the report and subreport, and this is the order:
I was surprised to see the second footer_Format event occur after the detail_print events. This could be used to my advantage though. Can you do a requery or refilter on a report through code? I'm sure it's possible, I'm just not sure how. I can call this in the Footer_Print event and should be ready to roll!
I'm trying both ways at the same time. I included two subreports in the footer of the main report. One does what grrr suggested and has its recordsource set to the LaborData table with a grouping on PersonWorking (I haven't messed with the sums for the HoursWorked yet). Unfortunately, instead of basing its data on every record of the main report's detail section, it only seems to get the last one.
On the converse side, when I create a temporary table, it is opened first, and never gets the updated data after the last record is processed in the detail section.
To figure out what was going on, I put more debug statements in the code. The Report Summary Table based subreport gets one open event, right after the main reports open event, so it only sees the empty table.
The other subreport (based on LaborData and grouped by PersonWorking) has several open events, the last showing after the Footer_Print event, so it is filtering its records to match the the last record the main report sees.
If I can get the first report to behave like the second report, my problems are solved.
It's time to pack up for today, but I'll check on any advice first thing in the morning! Thanks for all the help!