Hi,
I'm rather new to Access and VBA. It's been well over 10 years since I've dabbled with it (i.e. create a couple simple applications to solve some smaller issues), so I'm re-learning some aspects, but largelylearning aspects for the first time because I need to solve more complex issues.
The problem I need to solve is, I need to create a report that returns multiple percentages into the report. The percentages are to be calculated against a query that I created, and a value that I will need to derive. The complex parts are...
1) That I need to compare each unique entity's (i.e. each unique Facility's) most recent Inspected_Date, against that same entity's previous record's ReInspect_Date.
2) Then get the COUNT of the number of occurrences where the most recent Inspected_Date EXCEEDS the ReInspect_Date. I nwill eed to do that for each unique Facility entity that exists in the Query.
3 - 5) Then get the PERCENTAGE of Inspections that were peformed LATE by DIVIDING the COUNT of the number of occurrences where the most recent Inspected_Date EXCEEDED the ReInspect_Date by the total record COUNT from the original Query by, and then return the PERCENTAGE into an Access Report's text box.
NOTE: We use Access 2010 for the front end, and SQL Server for the Back end.
Attached is the complete visual example to demonstrate what I’m attempting to do.
This is what I've pondered doing so far, but I'm not sure how to execute:
A) Do a self join on the Query:
Please let me know if I should provide any more details, or if I just explained this poorly and should try again.
Any advice would be helpful.
Thanks in advance,
Travis
I'm rather new to Access and VBA. It's been well over 10 years since I've dabbled with it (i.e. create a couple simple applications to solve some smaller issues), so I'm re-learning some aspects, but largelylearning aspects for the first time because I need to solve more complex issues.
The problem I need to solve is, I need to create a report that returns multiple percentages into the report. The percentages are to be calculated against a query that I created, and a value that I will need to derive. The complex parts are...
1) That I need to compare each unique entity's (i.e. each unique Facility's) most recent Inspected_Date, against that same entity's previous record's ReInspect_Date.
2) Then get the COUNT of the number of occurrences where the most recent Inspected_Date EXCEEDS the ReInspect_Date. I nwill eed to do that for each unique Facility entity that exists in the Query.
3 - 5) Then get the PERCENTAGE of Inspections that were peformed LATE by DIVIDING the COUNT of the number of occurrences where the most recent Inspected_Date EXCEEDED the ReInspect_Date by the total record COUNT from the original Query by, and then return the PERCENTAGE into an Access Report's text box.
NOTE: We use Access 2010 for the front end, and SQL Server for the Back end.
Attached is the complete visual example to demonstrate what I’m attempting to do.
This is what I've pondered doing so far, but I'm not sure how to execute:
A) Do a self join on the Query:
- Add a colum called IncrementID to the first self join Query (which is sorted in Facility Order ASC, and then Inspected_Date DESC), and populate the new column with an auto incremented value from 2 to the total record count.
- Add a colum called IncrementID to the second self join Query (which is sorted in Facility Order ASC, and then Inspected_Date DESC), and populate the new column with an auto incremented value from 1 to the total record count.
- Then link the Queries (i.e. the self join) by IncrementID, and it should link the Current record with the previous record. At which point I can now compare the Inspected_Date from the most recent records, against ReInspected_Date from the previous records, but now they're all in the same record because of the self join and Incremental IDs.
- LOOP: Loop through the Facilities to get all the unique Facilities.
- NESTED LOOP: For each Facility, Loop through their corresponding Inspection records to get the most recent record (sorted on Inspected_Date DESC) and the previous record. Then compare the Inspected_Date from the most recent record against the ReIsnpect record fromt the previous records, and determine if the Inspected_Date EXCEEDS the ReInspect_Date, and IF it does then keep a COUNT of when that occurs in a variable called "Late Inspection COUNT".
- Then DIVIDE the "Late Inspection COUNT" but the Total Count of all records in the original Query, multiply the result by 100 (to get the percentage), and display it in an MS Access Report's text box.
Please let me know if I should provide any more details, or if I just explained this poorly and should try again.
Any advice would be helpful.
Thanks in advance,
Travis