Report Drill Down recommendations
I am looking for some feedback on a database we are setting up.
The database is an equipment tracking database tracking about 25 pieces of equipment. We have a table for equipment, transactions, and other required information. Data entry is performed on a List view where at piece of equipment has its own entry with 5 portal fields. The user selects a date that populates the transaction dates in each of the 5 portals. The user can then select a job and status.
Each piece of equipment will have a an transaction for each day of the week.
I wa trying to have essentially a drill down screen, where the each month has a button. If all of the entries were complete, the button would be green. If not, the button would be red. When the user hit a red button, they would be taken to a listing by week, where each week would be a button and a similar red/green conditional format would be used. If a week were selected, the user would be taken to the data entry screen.
I am trying to figure out the test for the conditional formatting. I have a calculated field to determine if a record is complete. I just need to check for each piece of equipment on each day in the date range (excluding weekends) there is a completed flag. If all dates and equipment have a completed flag, the button is green. If not, the button is red.
I have a tendency to overthink my databases and was wondering if anyone had any simple ideas. I was thinking of a filter based on the date range. If all pieces of equipment had a flag for each day, I would set a flag to 1. Any range with a 1 would be green, 0 would be red.