Union Query

jmjbear1011

Registered User.
Local time
Yesterday, 22:42
Joined
Jun 17, 2012
Messages
19
Here is the scenario:
Database (that I inherited), is for QA purposes.. Fields-> Employee Name, Score1, Score2, Score3, Score4, etc..

What I am needing to do is write a query, that gives a separate record for the employee where any of the scores are a "2". I could write a union query but there are like 20 fields that are scored.

Is a union query the only way or is there a better way? As a FYI, fields are not added or subtracted from the table. So they are always the same.

Suggestions?
 
You are now experiencing the joys of denormalized data, so send some wilted flowers to the author of the db.

Unless the data is restructured, then I do not know of any better way than normalizing it on the fly by using a union query.

Update: Actually, I do.. You could find all employees with Score1="2" OR Score2="2" OR Score3="2" ....
 
Last edited:
Thank you for the help. I have used that method before to get all employees that do have a "2" score. But what I am needing is separate records. But say the score is such Employee: "Me", Score1: "2", Score 2:"2" this is how I need it... that is why I wrote union query to get separate results. I put a copy of the code below (sections is a table that references what type of score that it is)... is there a better way?

Employee Score
Me Score 1
Me Score 2


SELECT t1.Mid, t1.Date, Sections.Sections, t1.Val
FROM (select data.date, data.mid, iif(data.screen12 = 2,12,0) as val from data where data.screen12 = 2
union
select data.date, data.mid, iif(data.screen13=2,13,0) as val from data where data.screen13=2
union
select data.date, data.mid, iif(data.screen14=2,14,0) as val from data where data.screen14=2) AS t1 INNER JOIN Sections ON t1.val = Sections.Value
ORDER BY t1.Date DESC;
 
The better way is to normalize the data. As long as your data is not even in first normal form you will be fighting with it. Fix it once or deal with it every time you need to do something new.
 
Sorry, can you elaborate? I have attached a copy of a test db, for testing the process. The data table has the log for each QA that was performed. So the record has several different critieria including the scores. Should I be breaking the data up ?
 

Attachments

Screen data should be in a separate table with one row per value.

So the first table would be:
tbl1:
M_ID (autonumber PK)
YourDate
tbl2:
S_ID (autonumber PK)
M_ID (FK to tbl1
Screen

Date and mid are both poor choices for column names since they are both function names and so have the potential to cause problems in VBA.
 

Users who are viewing this thread

Back
Top Bottom