Query by Form

[william]

Hello Everyone,

I'm using Access 2000 and am hoping someone can point me in the right
direction.

I've developed a room inspection database for someone at a hotel. The
database has an inspections table with about 30 fields. Each of the 30
fields represents an item in a room where a "condition" value will be
entered. For example, the [carpet] field will contain a value of good,
excellent, replace, paint, etc.

The manager would like to have a query form where she can select up to
five items from the inspection table, (carpet, toilet, walls, sink,
bed, etc.) and then select a corresponding condition. For example, she
would like to see rooms that need both the carpet replaced and the
walls painted by having the results in a subform datasheet. She can
then have the option of printing the results. She must have the option
of selecting more than one room item and its corresponding condition.

I thought about having the query form with five combo boxes containing
all 30 fields from the inspection and five combo boxes containing the
different conditon values. For example:

Parameter 1: Carpet Conditon1: Replace
Parameter 2: Walls Condition2: Paint


Or

Parameter1: Bed Condition1: Excellent
Parameter2: Sink Condition2: Good

But I don't know how those variables could get passed to the inspection
query. It's as though the query form needs code that says--search the
Inspections table, find the [Carpet] field, return those records where
the [Carpet] field has "replace" for the value.

If anyone can point me in the right direction with some suggestions, I
would certainly appreciate it.

Thanks for your help.

William

 

[John Vinson]

Hello Everyone,

I'm using Access 2000 and am hoping someone can point me in the right
direction.

I've developed a room inspection database for someone at a hotel. The
database has an inspections table with about 30 fields. Each of the 30
fields represents an item in a room where a "condition" value will be
entered. For example, the [carpet] field will contain a value of good,
excellent, replace, paint, etc.

You're violating relational principles here by storing data (items) in
fieldnames. "Fields are expensive, records are cheap". See below...

The manager would like to have a query form where she can select up to
five items from the inspection table, (carpet, toilet, walls, sink,
bed, etc.) and then select a corresponding condition. For example, she
would like to see rooms that need both the carpet replaced and the
walls painted by having the results in a subform datasheet. She can
then have the option of printing the results. She must have the option
of selecting more than one room item and its corresponding condition.

With a properly normalized table structure this is easy - with your
current "wide-flat" table it's much more difficult. You have in
reality a Many (Inspections) to Many (items) relationship. I'd
strongly suggest the following table structure:

Inspections
InspectionID Primary Key (autonumber)
RoomNo
InspectionDate
<any other fields concerning the general condition of the room, say>

Items
ItemID Primary Key (autonumber)
Item Text <e.g. Paint, Carpet, Bedframe....>

Conditions
InspectionID <link to Inspections>
ItemID <link to Conditions>
Condition <link to a small lookup table of conditions>


You'ld use a Form based on Inspections with a subform based on
Conditions, allowing you to do just as you describe - select an item
from a combo box, and enter the condition of that item from another.
It would be very straightforward to base a Report on a query joining
all three tables to display the condition of each item.


John W. Vinson[MVP]

 

[william]

Thanks John. I don't know what I was thinking! You are absolutely
right and I changed my table structure accordingly.

I'll probably re enter all of the inspections so far (over 300) in the
new table. Before I do I, is there a way I can get them into the new
table without manually re entering them?

Thanks again,

William

 

[John Vinson]

Thanks John. I don't know what I was thinking! You are absolutely
right and I changed my table structure accordingly.

I'll probably re enter all of the inspections so far (over 300) in the
new table. Before I do I, is there a way I can get them into the new
table without manually re entering them?

A "Normalizing Union Query" can move all the data in one operation.
It's a bit obscure but a useful technique; you'll need to use the SQL
window to create the query since the grid can't handle it.

You'ld edit the query to something like

SELECT InspectionID, RoomNo, "Carpet" AS Item, [Carpet] AS Condition
FROM wideflattable
UNION ALL
SELECT InspectionID, RoomNo, "Draperies", [Draperies]
FROM wideflattable
UNION ALL
SELECT InspectionID, RoomNo, "Wallpaper", [Wallpaper]
FROM wideflattable
<etc>

Save this query and then base an Append query upon it into your new
tall-thin table.

John W. Vinson[MVP]

 

[william]

Thanks again John. I will give that a try.

 

[william]

Hello Again,

I'm at the point where I'm building my query by form but I'm stuck
again. The underlying query works well based on the new normalization.
But I don't know how to pass along the parameters from the form to the
query now. I built some code behind a "Show Results" button. It's
working when the user only selects one Inspection Item and it's
corresponding condition (all combo boxes). But I want the user needs
to be able to select up to 5 Items and their 5 corresponding
conditions. Could I just put something in the underlying query? I do
that for something simple like a date range, but not sure how to do it
in this case.


Here's the code I have behind the Show Results button. I trimmed it
down to only include the relevent part:

If Me.cboInspectionItem1 & "" <> "" Then
strWhere = "[InspectionItem] = '" & Me.cboInspectionItem1 & "'"
End If
If Len(Me.cboCondition1 & "") > 0 Then
If Len(strWhere) > 0 Then
strWhere = strWhere & "And [Condition] = '" &
Me.cboCondition1 & "'"
End If
End If

Forms(Me.Name)("sfrmInspectionQuerySubform").Form.Filter = strWhere
Forms(Me.Name)("sfrmInspectionQuerySubform").Form.FilterOn = True

Set rst =
Forms("frmInspectionQueryForm").sfrmInspectionQuerySubform.Form.Recordset
If rst.RecordCount = 0 Then
MsgBox "There were no records that matched the criteria. Click OK
to search again.", vbOKOnly, "Room Database"
Forms(Me.Name)("sfrmInspectionQuerySubform").Form.FilterOn = False

Your help is greatly appreciated.

William

 

[william]

Sorry, one other question.

I'm also trying the Normalization Union Query to move all the data when
I discovered a problem. The query works except for the new
InspectionItemID. The new InspectionConditions table has the
InspectionItemID (autonumber) as well as the InspectionItem (text).

I can only create the ItemDescription in the union query. Is there a
way to add the InspectionItemID in a WHERE statement?

For example, this is one of the statements I have now in the query:

SELECT InspectionID, Room, "Carpet" AS InspectionItem, [Carpet] AS
Condition,
[CarpetComment] As ConditionComment
FROM tblInspectionsOld

I'd like to get the InspectionItemID from the new Conditions table also
in the same query if possible. Something like:

SELECT InspectionID, Room, "Carpet" AS InspectionItem, [Carpet] AS
Condition,
[CarpetComment] As ConditionComment
FROM tblInspectionsOld
SELECT [InspectionItemID] AS InspectionItemID
FROM tblInspectionItems
WHERE [InspectionItem].[tblInspectionsOld] =
[InspectionItem].[tblInspectionItems];

This statement results in a "Syntax Error in FROM Clause." message.

What am I doing wrong here?

Thanks,

William

 

[John Vinson]

I'm also trying the Normalization Union Query to move all the data when
I discovered a problem. The query works except for the new
InspectionItemID. The new InspectionConditions table has the
InspectionItemID (autonumber) as well as the InspectionItem (text).

The "new item" isn't in the table - it's just on a form.

I'd strongly suggest using the Normalizing query to populate a
normalized table, and then change your form to use the new table; and
do away with the wide-flat table altogether.

John W. Vinson[MVP]

 

[william]

That's exactly what I'm trying to do here. I'm trying to populate a
new normalized table. The normalization query works great. However,
when I run the append query from the normalization query it won't let
me because it says the data types (for InspectionItem) aren't the same.
The normalization query uses ItemText. In order for the new table
structure to work, the Conditions table and Items table are linked
using ItemID which is autonumber.

 

[william]

John,

I'm also confused about something you said which is:

The "new item" isn't in the table - it's just on a form.

I created the new table structure as you recommended in your first post
as follows:

Inspections
InspectionID Primary Key (autonumber)
RoomNo
InspectionDate
<any other fields concerning the general condition of the room, say>


Items
ItemID Primary Key (autonumber)
Item Text <e.g. Paint, Carpet, Bedframe....>


Conditions
InspectionID <link to Inspections>
ItemID <link to Conditions>
Condition <link to a small lookup table of conditions>

I created a Conditions table as listed above. Was that supposed to be
a form?

William

 

[John Vinson]

That's exactly what I'm trying to do here. I'm trying to populate a
new normalized table. The normalization query works great. However,
when I run the append query from the normalization query it won't let
me because it says the data types (for InspectionItem) aren't the same.
The normalization query uses ItemText. In order for the new table
structure to work, the Conditions table and Items table are linked
using ItemID which is autonumber.

Please post the SQL view of the normalization query (the UNION query
and the Append query) and indicate the datatypes of the corresponding
fields in both the old wide-flat table and the new normalized table.
Do you have Lookup Fields in the wide-flat? If so what you see isn't
really what's there. And have you actually filled in the
InspectionItem table? You may want a Form set up to edit and add
inspection items, but it's the table that you're using, not the Form.

You may need to - first - create and fill the InspectionItems table;
your UNION query will then need to join the (new) InspectionItems
table to the Append query, joining the ItemText field, to pick up the
corresponding ItemID.

John W. Vinson[MVP]