I am just learning Access and have been tasked with creating a database for system access. We need a report that shows what systems have been in a pending status for 0-30 days, 31-60 days, 61-90 days, and 91+ days. I am soooo close to getting this but I’m at a dead end. For the examples below I will just use NFC and eOPF as the systems (there are five systems in all).
So the report would read like this:
Pending Date...0-30...31-60...61-90...90+
NFC....................1.........4..........8........1
eOPF..................2.........5..........1........0
I have the table setup with a status field for each group (i.e. NFC Status = “Pending” or “Complete”)
I have another field that has a date submitted (i.e. NFC Submitted Date = “XX/XX/XXXX”)
This is what I have done. I am only sharing this to give an idea of the direction I was heading. If there is a better way please let me know.
I created two quires (NFC Requests & eOPF Requests)
The first row field in the query is the status (i.e. NFC Status) with Criteria =”Pending”
The second row is the 0-30 days with the field of NFC 0 - 30 Days: Sum((IIf([Systems Access - Table]![NFC Form Date Submitted]>Date()-31,1,0)))
The third row is NFC 31 - 60 Days: Sum((IIf([Systems Access - Table]![NFC Form Date Submitted]Date()-61,1,0)))
Etc, etc.
So I have two quires that give me that data I need and they work. Now, I need to figure out a way to combine the quires by changing the fields that calculate NFC and eOPF separately and calculate them together.
I tried to build a summary request report but it wants to push the totals for eOPF over and I can’t line them up.
It looks like this:
Pending Date...0-30...31-60...61-90...90+...0-30...31-60...61-90...90+
NFC....................1.........4...........8........1
eOPF..............................................................2.........5.........1.........0
I’m getting frustrated and could really use some help.
- Justin