Report to return percentage of Total Count against a Complex Derived value (1 Viewer)

[Oarygone]

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:

• 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.

B) Somehow do a NESTED LOOP in VBA:

• 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

 

[plog]

Very good explanation and description. I achieved what you wanted with 1 subquery (which itself has 2 subqueries). The below code uses MyQuery as the name of your query, you will have to replace all instances of that with the name of your actual query. Here's the subquery:

SELECT C.Facility_ID, C.Inspected_Date, C.ReInspect_Date, (SELECT MAX(ReInspect_Date) FROM MyQuery WHERE Facility_ID=C.Facility_ID AND ReInspect_Date<C.ReInspect_Date) AS LastReInspection, IIf([Inspected_Date]>(SELECT MAX(ReInspect_Date) FROM MyQuery WHERE Facility_ID=C.Facility_ID AND ReInspect_Date<C.ReInspect_Date),1,0) AS LateInspection, (SELECT COUNT(Facility_ID) FROM MyQuery WHERE Facility_ID=C.Facility_ID AND ReInspect_Date>C.ReInspect_Date) AS InspectionNumber
FROM MyQuery AS C;

Paste that and name it 'LateInspections_sub1'. It determines if a test is late and calculates each record's order (so you can use the 2 most recent in counting late tests).


Then this SQL produces the result you want:

SELECT Sum(IIf([InspectionNumber]<=1,[LateInspection],0))/Count([Facility_ID]) AS x
FROM LateInspections_sub1;

Let me know if you find any issues or want anything explained.

 

[Oarygone]

Thank you plog!

I'm in the process of trying out your first query "LateInspections_sub1", which looks very promising, and thank you for providing it. However, something I didn't mention was that my original query returns over 100,000 records, and so I'm running into performances issues while running the query "LateInspections_sub1" and so far have seen the system freezing up and become unresponsive. I'll keep playing with it though and see if I can get it to go through... I might need to ask someone around the office about indexing something to make it run as it should (because 100K records isn't really all that much in my mind, so hopefully something they can tweak with indexes). I'll let you know if/when I am able to see the results... and thank you again for providing it. That query is a work of art, and I can learn a lot just by looking at it.

 

[plog]

Yeah, there's a lot of subquerying/processing involved in getting the data you want. If the indexing doesn't help you can turn your MyQuery from a SELECT query into a MAKE TABLE query. Then run my queries off that table.

That would make them run faster, but add a step for a human--the MyQuery would have to be run everytime to refresh the data.

 

[Minty]

You might consider putting one or even both of Plog's queries into a View on the SQL Backend and linking to it, that way the processing is done server side, and invariably runs a great deal quicker. (Plus indexes will help if not already there)

 

[Oarygone]

Thank you both!

I did end up creating a view on SQL Server and linking it to Access and that did the trick. That process was a learning curve for me, but the effort was well worth the end result because it handled the processing efficiently enough. Now I'll just need to create a few more queries (similar variety) and share them all in the same report, but thanks to this exceptional help I'm off and running and headed in the right direction. Thank you both, this is greatly appreciated.