multiple query criteria

  • Thread starter Thread starter olive
  • Start date Start date
O

olive

Let's start with the fact that I am an Access novice, so if this looks like I
am way off base, and cannot get any guidance here, just let me know.

What I have is a table listing vehicle inspection standards. A vehicle
tag number, and then 50 fields such as, wipers inop, headlight inop, etc.
My fields are numeric, because there is a point system used to determine a
pass/fail.
I input the tag number, and if the specific area is deficient, I enter
either a 10 or a 25 for point values.
What I end up with, is a table with tag numbers and then fields showing
points deducted from the overall 100%.

What I want to do is run a report that lists the vehicle tag number, and
only the fields identified with a numeric penalty (10 or 25). There will
never be all 50 fields on the report, at most, five or six of the areas will
be marked as having a deficiency. If I put "IS NOT NULL" in the design grid
for each of the fields, my report comes back empty. Is there a way to make
this work?
Any help would be much appreciated.
Thank You
 
Let's start with the fact that I am an Access novice, so if this looks like I
am way off base, and cannot get any guidance here, just let me know.

What I have is a table listing vehicle inspection standards. A vehicle
tag number, and then 50 fields such as, wipers inop, headlight inop, etc.
My fields are numeric, because there is a point system used to determine a
pass/fail.
Click to expand...

That is in fact a less than ideal table design. Each vehicle has many (50)
InspectionPoints; each InspectionPoint applies to many vehicles. This kind of
many to many relationship is better handled with three tables: Vehicles
(probably using the tag number or VIN as the primary key); a fifty row (today,
you might have more points next year if the legislature or agency adds some)
table of Points; and a Rating table with fields for the TagNumber, PointID,
and Points.
I input the tag number, and if the specific area is deficient, I enter
either a 10 or a 25 for point values.
Click to expand...

With the normalized design a subform with a combo box selecting the
InspectionPoint and storing the points for that issue would be ideal.
What I end up with, is a table with tag numbers and then fields showing
points deducted from the overall 100%.

What I want to do is run a report that lists the vehicle tag number, and
only the fields identified with a numeric penalty (10 or 25). There will
never be all 50 fields on the report, at most, five or six of the areas will
be marked as having a deficiency. If I put "IS NOT NULL" in the design grid
for each of the fields, my report comes back empty. Is there a way to make
this work?
Click to expand...

Not easily with your wide flat design. Trivially easy with the normalized
table.

Any chance you could correct the table design? If not post back, it's doable
with a UNION query; just a lot more work.
 
For some reason, my last two replies did not post. I hope they all don't
show up at once, if so, please forgive the repetitiveness (is that a word?)
Anyway,
I took your advice and corrected my table structure.

I now have three tables.

Table 1, Vehicles - has vehicle tag number, vin, and specific user

Table 2, PointID - has PointId (with my 50 inspection items), and the value
of each item 10 or 25points.

Table 3, Ratings - has three fields, TagNumber, PointID, and Points.

Now, I have table 2 and 3 related by pointId, and Table 1 and 3 related by
Tag Number.

Just to establish my next step.
I should create a form to input the tag#, inspection item, and points into
my ratings table. From this table I should be generating my queries and
reports?

If this sounds good, let me know and I will press on with making my form and
queries.
Thanks so much for the help
Olive
 
For some reason, my last two replies did not post. I hope they all don't
show up at once, if so, please forgive the repetitiveness (is that a word?)
Anyway,
I took your advice and corrected my table structure.

I now have three tables.

Table 1, Vehicles - has vehicle tag number, vin, and specific user

Table 2, PointID - has PointId (with my 50 inspection items), and the value
of each item 10 or 25points.
Click to expand...

50 rows, not 50 fields... right??
Table 3, Ratings - has three fields, TagNumber, PointID, and Points.
Yep!

Now, I have table 2 and 3 related by pointId, and Table 1 and 3 related by
Tag Number.

Just to establish my next step.
I should create a form to input the tag#, inspection item, and points into
my ratings table. From this table I should be generating my queries and
reports?
Click to expand...

Yes. It's a *bit* redundant to have both the PointID and the Points in the
Ratings table, since in principle you could just store the PointID and use a
query to look up the points; but I'm guessing that the number of points might
change over time, or even be a variable (a trivial, minor or serious violation
of an inspection point might score 5, 10 and 25 points), if not now then in
the future.

If so you'll need a smidgen of VBA code or a one-line macro to record the
points. I'd see a Form based on your Vehicles table, with a Subform based on
Ratings. On the Subform you would have a Combo Box or Listbox based on the
PointID table, with the PointID, a text description of the point (unless the
person using the database has all 50 ID's memorized), and the Points value.
The combo's Control Source would be the PointID, and you could put a macro or
VBA code in the combo's AfterUpdate event to push the combo's Points value
into the Points field on the subform:

Private Sub cboPoints_AfterUpdate()
If Not IsNull(Me!cboPoints) Then ' did the user select a row?
Me!Points = Me!cboPoints.Column(2) ' the third field, it's zero based
End If
End Sub
 
Back
Top