clinthicum
Heery db
- Local time
- Today, 17:18
- Joined
- Jan 11, 2010
- Messages
- 3
I'm baffled by how to generate a query (preferably without SQL or VBA) that will generate the results I'm looking for.
I'm attaching an access file to help show what I'm trying to do.
Scenario:
- collecting data on various conditions of a building
- the inspector is provided a list of items to look for
- for each item he takes notes as to whether the condition is acceptable (good), or un-acceptable (bad or critical), etc.
- the data is entered into the Access db and the executives want to see a report of only the items that meet the criteria of "Bad" or "Critical".
The challenge is that each item has it's on status field, it's not like this:
ITEM ITEM STATUS
paint good
carpet bad
pipes critical
roof critical
but more like this:
SITE ITEM1 ITEM1STATUS ITEM2 ITEM2STATUS
1 paint good carpet bad
Etc.
So I basically need a query that will study the tables and when ever it sees the status of "Bad" or "Critical" it pulls out the item's related comment. For instance, if "pipes" are "critical", then in a report output the comment for that which might be "The pipes are in horrible condition and may cause a leak in the next year, replacement is encouraged". etc.
The results that I'm trying to achieve are an executive report of only the items that are "bad" or "critical".
maybe this is a cross tab query (first) and then followed by another query to just look for "bad' and "critical". Another way to explain it...would be that I want to convert all columns like "1status", "2status", "3status" to just be a consolidated list of "status" !
Any help is appreciated.
I'm attaching an access file to help show what I'm trying to do.
Scenario:
- collecting data on various conditions of a building
- the inspector is provided a list of items to look for
- for each item he takes notes as to whether the condition is acceptable (good), or un-acceptable (bad or critical), etc.
- the data is entered into the Access db and the executives want to see a report of only the items that meet the criteria of "Bad" or "Critical".
The challenge is that each item has it's on status field, it's not like this:
ITEM ITEM STATUS
paint good
carpet bad
pipes critical
roof critical
but more like this:
SITE ITEM1 ITEM1STATUS ITEM2 ITEM2STATUS
1 paint good carpet bad
Etc.
So I basically need a query that will study the tables and when ever it sees the status of "Bad" or "Critical" it pulls out the item's related comment. For instance, if "pipes" are "critical", then in a report output the comment for that which might be "The pipes are in horrible condition and may cause a leak in the next year, replacement is encouraged". etc.
The results that I'm trying to achieve are an executive report of only the items that are "bad" or "critical".
maybe this is a cross tab query (first) and then followed by another query to just look for "bad' and "critical". Another way to explain it...would be that I want to convert all columns like "1status", "2status", "3status" to just be a consolidated list of "status" !
Any help is appreciated.
