multiple query criteria

[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

 

[John W. Vinson]

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.

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.

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?

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.

 

[KenSheridan via AccessMonster.com]

It is just about possible with your existing table. One way is a UNION query
as John mentioned, but with 50 separate columns this would be cumbersome.
There is another way, which is to manipulate the report's layout in code in
its module at runtime so that Null columns are hidden and those with data are
repositioned on a vehicle by vehicle basis. This is not trivial, however,
and once again the total number of columns involved would make the code
extensive. I would not recommend either approach.

The only sensible solution is to normalize the database design by
'decomposing' your table into a set of related tables along the lines John
has described. If each vehicle can have more than one inspection, however, e.
g. annually, then you'd need to go introduce one more table, so what you'd
have would be the following tables:

Vehicles
….TagNumber (primary key)
….<other columns representing vehicle attributes>

InspectionCategories
….CategoryID (autonumber primary key)
….Category

This table would have one row per category, e.g. 'wipers inop' etc.

Inspections
….InspectionID (autonumber primary key)
….InspectionDate
….TagNumber

The TagNumber in this is table a foreign key referencing the primary key of
Vehicles

InspectionDetails
….InspectionID
….CategoryID
….Points

The primary key of this table is a composite one made up of InspectionID and
CategoryID. Individually each of these columns is a foreign key referencing
the keys of Inspections and Categories respectively.

Each of the above tables represents an 'entity type' with the columns in each
table representing attributes which are specific to that entity type. This
is how a relational database works. The InspectionDetails table represents a
special kind of entity type because it also models a many-to-many-
relationship between Inspections and Categories. By having separate columns
for each inspection category in your table you are doing what is known as
'encoding data as column headings'. This breaks one of the fundamental rules
of the relational model, the 'information rule' which requires all data to be
stored as values at column positions in rows in tables and in no other way.
As you've found out this leads to practical difficulties in using the
database.

With a correctly normalized design as above creating a report becomes very
simple. All that's necessary is to join the tables in a query like so:

Vehicles---<Inspections---<InspectionDetails>---Categories

Include the columns from each table which you need in your report and base
the report on the query. Group the report first by Vehicle, then by
Inspection, with a group header for each and include the Category and Points
in the detail section. For each vehicle inspection the report will only
include those categories for which there are data. If you wish you can sum
the points in a text box in a group footer for the Inspection group to give
the total points per vehicle inspection.

If you have a manageable amount of data at present you might be able to
renter the data manually into the new model, being sure to fill the Vehicles
and Categories tables first, followed by the Inspections table, and finally
the InspectionDetails table. With a large amount of existing data you can
largely automate the process by using append and update queries, but with
your 50 separate columns, while not a difficult task, this would nevertheless
be a tedious one. Which ever is the case I would recommend that you bite the
bullet, though, as to persist with your current flawed design is only going
to give rise to more problems in the future. We shall of course be happy to
guide you through the process.

Ken Sheridan
Stafford, England

 

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

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

 

[John W. Vinson]

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.

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?

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