Query Criteria

[Hannah]

I am having problem creating this query. I have a table with about 5 fields
that have a drop down value list with (Complete, Incomplete, and N/A). I
would like to write a query to select all records that have "Incomplete" on
any of these fields and group them by another field called "Employee". This
"Employee" field is a lookup table on my FORM.

Your help is greatly appreciated.

 

[Jerry Whittle]

I hate to say it, but your table structure is wrong to return the data with a
nice, simple query. I’m assuming that your data looks something like this:

Employee Test1 Test2 Test3 Test4 Test5
Tim Complete Complete N/A N/A Incomplete
Jack InComplete Complete N/A N/A Incomplete

That’s known as committing spreadsheet. Instead of going across, you want to
go down like so:

Employee Tests Status
Tim Test1 Complete
Tim Test2 Complete
Tim Test3 N/A
Jack Test1 Incomplete

Here's the nice simple query based on the above table with even some sorting
of the records.

SELECT Employee, Tests
FROM TheTable
WHERE Status = "Incomplete"
ORDER BY Employee, Tests ;

For your table structure to work, you would need something like below. Plus
if you ever need to add another “testâ€, you will need to modify such queries,
forms, and reports.

SELECT Employee, Test1, "TheTest1"
FROM TheTable
WHERE Test1 = "Incomplete"
UNION ALL
SELECT Employee, Test2, "TheTest2"
FROM TheTable
WHERE Test2 = "Incomplete"
UNION ALL
SELECT Employee, Test3, "TheTest3"
FROM TheTable
WHERE Test3 = "Incomplete"
UNION ALL
SELECT Employee, Test4, "TheTest4"
FROM TheTable
WHERE Test4 = "Incomplete"
UNION ALL
SELECT Employee, Test5, "TheTest5"
FROM TheTable
WHERE Test5 = "Incomplete" ;

Then there’s the issue of the drop downs and lookup table in your table.
Depending on how they are linked, they might not show the Employee’s name,
but rather a number or something instead. Avoid lookups based on other tables
at table level.

 

[Hannah]

Hi Jerry,
Thank you for your instructions. I am new to Access Database. I need to
re-think the way that I have my tables setup and use your suggestions as a
guide.

Best regards,