Query to return different percentages? (1 Viewer)

Pienuts · Jun 4, 2014

Hello!
I was hoping to get a little assistance with a problem I am having.
This is the table I am working with:

tblInspection
InspectID - autonumber field -PK
InspectionType - long integer - either 2 Year, 5 Year, 10 Year, 20 Year... as well as some other irrelevant (for this question) types
InspectionStatus - string- either pending, complete or cancelled
InspectionDate - date for inspection

Because not all inspections need to be done in a certain year, I need some way (I'm pretty sure it's a query of some sort) to return a certain percentage of each type of inspection (100% for 2 year, 80% for 5 year, 60% for 10 year, 10% for anything over that). I would like them to be random, but, in the case of the 20+ year inspections, I would like to inspect a different random 10% each year.

If you could point me in the right direction, I would very much appreciate it!
Thanks!

burrina · Jun 5, 2014

I would create a form for this and bind the fields to the query. Then just enter your criteria.
Example: =Nz([Labor]/60/[JobPrice],0) 'What Percentage of the Job Price is Labor.

HTH

Pienuts · Jun 5, 2014

Hmm... Perhaps I wasn't clear.
I have a list of more than 20,000 sites which have scheduled inspections for 2,5,10,20,... years and due to time / personnel constraints not every inspection can be done every year. So the inspectors decide their capabilities each year (this year, it's 100% 2 year, 70% 5 year, and 30% everything else) and input these numbers somewhere (likely a form like you said), but then somehow the database has to mark a random X% of pending inspections so inspection maps and due date warnings can be generated, etc.
Is that a little clearer? I'm sorry; I find it terribly difficult to concisely pose questions on Access topics.

burrina · Jun 5, 2014

So basically you saying you want to get a percentage of those sites that have not been inspected within a certain years criteria correct?

1. Start by getting the inspections for whatever year you choose first.
2. I assume you have a field that says whether it has been inspected or not and date when it was!

Pienuts · Jun 5, 2014

I want a percentage of inspections marked for inspection during a year to be marked for actual physical inspection.
For example, right now with the table layout I mentioned in the first post, the database shows ~1130 inspections due this year. But this is ALL (100%) of the 2,5,10,20,30,40,50,60,70,80 and 90 year inspections that fall on this year.
From that, I need to "mark" 100% of the 2 year, 70% of the 5 and 30% of the rest as sites to ACTUALLY inspect this year.
The rest I will mark status as Cancelled and they will get a chance to be inspected the next time their schedule date comes up.

burrina · Jun 5, 2014

So what is it exactly that marks them as Inspected?

Pienuts · Jun 5, 2014

The InspectionStatus field.
Pending - not inspected yet
Complete - inspection comlpeted
Cancelled - Skipped inspection for a number of reasons, one being that it was not chosen as being one of the percentage insoected that year.

burrina · Jun 5, 2014

Can you post a demo copy with no sensitive data?

Pienuts · Jun 5, 2014

Hmm… Apparently not. I work for the government and the firewalls are a little outrageous here. I can try to take it home and do it, but they tend to frown on that.

Pienuts · Jun 6, 2014

No dice on being able to take it home. :-/

I have made it work (for now) by adding a field (PrctNumber)to the Site table with the numbers 1-20 spread out evenly amongst the sites, each number worth 5%. then I made a Percentage table with a field for PrctNumber and a boolean field for each of the different inspection types.So each year, I'll manually choose what numbers are TRUE for each type, and then I query out the False ones when listing the inspections due.

It's not clean, but it'll work for now and I can stop thinking about it for a bit!

Query to return different percentages? (1 Viewer)

Pienuts

Registered User.

burrina

Registered User.

Pienuts

Registered User.

burrina

Registered User.

Pienuts

Registered User.

burrina

Registered User.

Pienuts

Registered User.

burrina

Registered User.

Pienuts

Registered User.

Pienuts

Registered User.

Similar threads

Users who are viewing this thread