Help with Query Filter, Please!

Pienuts

Registered User.
Local time
Today, 15:43
Joined
May 2, 2014
Messages
106
Hey all, hopefully this is a quick one!

I have a query with fields:
InspectionType (integer)
RndInspecNum (integer)

I have a table with fields :
RndInspecNum (integer)
2 Year (Boolean)
5 Year (Boolean)
Decennial (Boolean)

and I would like to filter the query so that if
InspectionType = 4 then only return if TRUE in the 2 Year field for that RndInspecNum
InspectionType = 5 then only return if TRUE in the 5 Year field for that RndInspecNum
InspectionType = 7 or >=9 then only return if TRUE in the Decennial field for that RndInspecNum

What would be the best way to do this? I had a messy field in the query that looked like this:

Code:
[SIZE=3][FONT=Times New Roman]IIf([tblInspection].[InspectionType]=4,DLookUp("TwoYear","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]=5,DLookUp("FiveYear","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]=7,DLookUp("Decennial","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),IIf([tblInspection].[InspectionType]>=9,DLookUp("Decennial","ZZLookupPercentages","RndInspNum = " & [RndInspNum]),-1))))[/FONT][/SIZE]
But it slowed the query to a CRAWL. I’m sure there is a way - as the data is all right there – but I’ll be damned if I can figure out how to fit it together!

Thanks in advance! Hopefully I have provided enough info for you!
 
Multiple DLookups is the culprit. A function with a DAO lookup would be considerably faster. Dlookups are kknown to be glacially slow. Also, you can probably achieve the same result with a "left join" in your query looking up the desired value.

From what you've posted, I can't tell you more explicitly how to do this.
 
What else do you need to know to assist me further? The "left join" is what I'd love to do - I'm just not sure how.

I can join the RndInspNum to the boolean table, but I can't pull the correct boolean value from table based on the InspectionType without needing dlookups to determine which field's boolean to grab. Did I design the table inefficiently?
 
Dlookups generally have no place in a query. A query allows you to JOIN data sources together so all the data you need in a query can usually be made avialble by brining that datasource into the query itself.

Also, I think you have a larger problem. Your second table doesn't sound structured properly. You shouldn't have numerated field names (Inspection1, Inspection2, Inspection3, etc.) and that's what your 3 Boolean fields really are. I believe the structure for that table should be:

RndInspecNum: link to other table
InspecTimeFrame: 2, 5 or 10

Then if data is in that table for a particular timeframe it resolves to true. If there's no record for that time frame the result is equivalent to false.

I'm pretty sure that's how it should be, but I'd like to hear more about what your data represents and how your business uses it.
 
Thanks, plog - I was starting to wonder if that's closer to how I should have structured it.

Let me give you a little insight on the data, then!

The inspections we are talking about are on sites. For each site in my database there is a construction date; the site is then inspected every 2, 5, 10 then every subsequent decade after construction.

Our inspectors, however, are often short-staffed or otherwise engaged, so they often need to only inspect certain percentages of each type of inspection. For example, currently they are inspecting 100% of 2-year-old sites, 70% of 5-year-old sites, and 30% of every other inspection due.

To achieve this, I assigned a random number (RndInspNum) to all sites, an integer ranging from 1-20(depicting 5% intervals). I then created a table (ZZLookupPercentages) which has a RndInspecNum field, and then Boolean fields for 2 Year, 5 Year, and Decennial (10, 20, 30, etc.). This table currently has a TRUE value for all records in the 2 Year field (100%), the 5 Year field has TRUE for RndInspecNum 1-14 (70%), and the Decennial field is true for RndInspecNum 15-20 (30%).

I'm thinking now that the structure of the table should look more like
RndinspecNum (int)
InspectionType (int)
ToInspect (Boolean), perhaps automatically populated by a form where the user determines the percentages of each inspection type
Year (int), so that the past percentages can be recorded for posterity

The query we have been discussing shows the upcoming inspections for the next year. It pulls the details of the site and inspection and filters out the sites dictated by the ZZLookupPercentage table.

Does that clear up the mud a little? Also, If you know of a better way to handle these percentages I would love to hear! I'm kind of flying by the seat of my pants here! ;)
 
You kinda lost me in all your database jargon. Can you construct a plain english paragraph that describes what you are trying to do? Pretend databases don't exist, just tell me what it is you hope to achieve. for example (and this may be wrong):

We need a way to randomly select 70% of our sites to inspect. And then a way to mark those inspections as complete.

Again, no data jargon, just explain what you need like I'm a 6th grader.
 
Okay, I will try! Sorry about the wall of text earlier; I'm used to having to explain to insane detail here!

I need to determine each year what percentage of 2 Year, 5 Year, and Decennial Inspections I will be doing. Then I need to randomly select that percentage of sites and use it as my list of inspections to complete for the year.
 
No problem and good description.

You said, "I need to determine what percentage...". Is that a logical process or is it just a number externally arrived at? By logical, I mean can you look at your data and know what the percentage is going to be?

Also, using your current structure what does a True value in [5 Year] denote? Does it mean the inspection occured or should occur? Do you even keep track of which ones have occured?
 
Yes, My inspection tracking is all done through an inspection table mostly seperate from what we're discussing.
No, the percentage is disctated by the lead inspector at the beginning of the inspection season. They mostly just come up with numbers by looking at their current staff and workload, but it seems pretty random - nothing logical to let the DB do it for them.
 
You really could just use one table to identify which ones should be completed and which ones are complete. I believe you only need a table of this structure to do both:

Inspections
Inspection_ID, autonumber, primary key
Inspection_Year, number, identifies what year inspection is to be completed
ID_Site, number, foreign key to Site table describing what the site is
Inspection_Type, number/text, identifies if 2, 5, 10 year inspection
Inspection_Complete, Boolean/Date, Identifies if/When inspection was complete

That's really all you need to do all those things. The key is that this table only gets records for Sites that are to be inspected. If a site isn't in the table, it wasn't identified to be inspected. Then when its complete you can either just check a box to mark it complete (Inspection_Complete=Boolean) or have the user enter the date it was complete (Inspection_Complete=Date). Then if there is no date in that field you know it hasn't been completed.

At the beginning of the year you would run an UPDATE query which would apply the percentages to your Site table and populate Inspections with the new year's inspections. This would be a decently complex query, but I'm talking broad strokes right now and we can go into those details later.
Using that structure it actually becomes really easy to do a totals query to see how many inspections have been scheduled/completed. You just use the SUM and COUNT functions of SQL to do so and can group by Inpsection_Type and filter by Inspection_Year.
 
Sorry - I got caught up in the weekend!
Hmm... Interesting - I have most of that data in my current Inspection table.
I have been udating that table (through vbs at login) by marking any inspections 6 months overdue with an inspection status as "Cancelled" and generating a new inspection for their next scheduled inspection.
I like where you are going with this, though - currently, there are some pending inspections in my table which aren't for 9 years, and I've never loved wasting table space with that (as well as all those "cancelled" inspections).
So, just to make sure I am on the same page, you are suggesting running an Update query once per year before the field season, and, using the percentages dictated at that time, populate the Inspection table with only the inspections that will actually be occurring? I like that idea very much!
 
So, just to make sure I am on the same page, you are suggesting running an Update query once per year before the field season, and, using the percentages dictated at that time, populate the Inspection table with only the inspections that will actually be occurring?

Yes, that's it exactly. No inspection needed, no record.
 
Awesome, that is a way better idea. I'm going to wrap my head around how to accomplish this, then! If you come up with any function ideas let me know! I have a feeling I'm going to need it.

If it helps, here is how my Inspection table is currently constructed:
tblInspection
InspectionID, autonumber, pk
InspectionStatus, text, Completed, Pending, or Cancelled
InspectionDueDate, date, Date Inspection is to be expected (I currently do 1 Jun for Scheduled inspections but I need the specificity of an actual day for some other inspection types)
InspectionDate, date, date inspection occured
InspectionType, Number, refers to table of inspection types - 4 is 2 Year, 5 is 5 Year, 7 is 10 year, 9 is 20, 10 is 30 year, and +10 years for every number after, currently ending at 100 Year (although I'm just now wondering if I should just have a Decennial type to blanket cover them all - I could use a simple query to determine the actual age)
SITEID, number, fk

And the rest is actual data collected during the inspection.
 
I've created a query to give a list of each Site's next scheduled inspection, and I need to build another to filter it by the inspection year (I was hoping for one query, but I couldn't use the year criteria in the first due to the aliases, and the formula was too long for Access when I tried to replace the aliases in the one field).

I've run into a problem, though - when trying to use a year as filter criteria on the second query, I keep getting a Data Type Mismatch error - I've tried 2016, "2016", and even Year(#1/1/2016#). All are giving me the same error. Do you know what the issue is?

Here's the SQL for the first query:
Code:
SELECT tblSite.SITEID, Nz(DMax("ConstructionDate","tblUSID","SITEID = " & [tblSite].[SITEKey]),"Unknown") AS ConstructionDate, IIf([ConstructionDate]="Unknown","",IIf(Year(Date())-Year([ConstructionDate])<=2,"2 Year",IIf(Year(Date())-Year([ConstructionDate])<=5,"5 Year",-10*Int((Year(Date())-Year([ConstructionDate]))/-10) & " Year"))) AS strNextInspectionType, IIf([strNextInspectionType]="","",DateSerial(Year([ConstructionDate])+Left([strNextInspectionType],InStr(1,[strNextInspectionType]," ")-1),6,1)) AS NextInspectionDate, DLookUp("ID","ZZLookupInspectionTypes","InspectionType = '" & [strNextInspectionType] & "'") AS NextinspectionType, Year([NextInspectionDate]) AS InspectionYear
FROM tblSite
ORDER BY tblSite.SITEID;
 
I don't follow. I don't see '2016' in that query any where. Nor do you have a WHERE clause in that SQL.
 
Sorry, I have an InspectionYear Field at the end of that query which is just year([NextInspectionYear]), returning just the year of that field. I'm trying to filter the InspectionYear field in the second query, because it won't let me filter with aliases (as, I understand, Access does the WHERE before setting the aliases).

This is what the (broken) second query looks like:
Code:
SELECT qryNextScheduled.SITEID, qryNextScheduled.ConstructionDate, qryNextScheduled.strNextInspectionType, qryNextScheduled.NextInspectionDate, qryNextScheduled.NextinspectionType, qryNextScheduled.InspectionYear
FROM qryNextScheduled
WHERE (((qryNextScheduled.InspectionYear)=2016));
 
Remove the criteria from the second query and run it. If it doesn't run, the first query is the problem one. If it does run, what datatype is InspectionYear? If it's data aligns to the left of the cell it is text, if it aligns to the right it is numeric.

The criteria for text fields need to be enclosed in quotes.
 
It aligns to the right, and when I remove the criteria from the second query it works fine. :-/
 
Can you post a sample of the database?
 
I will try to make a dummy DB to reproduce this.
I tried converting the Year() to a Right() to change the field to a text and use quotation marks to filter and it works! It's SUPER slow, but gets the job done - although I would be curious to find out why it wasn't working as a numerical field.

So, now the first query SQL looks like this:
Code:
SELECT tblSite.SITEID, Nz(DMax("ConstructionDate","tblUSID","SITEID = " & [tblSite].[SITEKey]),"Unknown") AS ConstructionDate, IIf([ConstructionDate]="Unknown","",IIf(Year(Date())-Year([ConstructionDate])<=2,"2 Year",IIf(Year(Date())-Year([ConstructionDate])<=5,"5 Year",-10*Int((Year(Date())-Year([ConstructionDate]))/-10) & " Year"))) AS strNextInspectionType, IIf([strNextInspectionType]="","",DateSerial(Year([ConstructionDate])+Left([strNextInspectionType],InStr(1,[strNextInspectionType]," ")-1),6,1)) AS NextInspectionDate, DLookUp("ID","ZZLookupInspectionTypes","InspectionType = '" & [strNextInspectionType] & "'") AS NextinspectionType, Right([NextInspectionDate],4) AS InspectionYear
FROM tblSite
ORDER BY tblSite.SITEID;
and the second looks like this:
Code:
SELECT qryNextScheduled.SITEID, qryNextScheduled.ConstructionDate, qryNextScheduled.strNextInspectionType, qryNextScheduled.NextInspectionDate, qryNextScheduled.NextinspectionType, qryNextScheduled.InspectionYear
FROM qryNextScheduled
WHERE (((qryNextScheduled.InspectionYear)="2016"));
I feel like I'm getting close to making this work - I just have to figure out how to make the random selection of percentages work!
 

Users who are viewing this thread

Back
Top Bottom