Select ALL data from query/table??

[Hammer45]

This CAN'T be that tough!!!!

I have a query that looks at a table and tells me which records have data in any of 8 fields (SQL code below):

SELECT Requirements.RQ_User1, Requirements.RQ_User2, Requirements.RQ_User3, Requirements.RQ_User4, Requirements.RQ_User5, Requirements.RQ_User6, Requirements.RQ_User7, Requirements.RQ_User8
FROM Requirements
WHERE (((Requirements.RQ_User1) Is Not Null)) OR (((Requirements.RQ_User2) Is Not Null)) OR (((Requirements.RQ_User3) Is Not Null)) OR (((Requirements.RQ_User4) Is Not Null)) OR (((Requirements.RQ_User5) Is Not Null)) OR (((Requirements.RQ_User6) Is Not Null)) OR (((Requirements.RQ_User7) Is Not Null)) OR (((Requirements.RQ_User8) Is Not Null));

What I need to do now is compare this conglomeration of data against a table with a single column in it: Data Points. I have searched every place I could think of, but haven't found ANY way to simply get all the data from the query, put it in a single column, compare it to the Data Points table, then show me where the differences are.

I would think this would be a piece of cake!!! Sorry for the frustration, but I obviously don't know enough to get over this (seemingly) minor hurdle.

Any help?

 

[lespaul]

You want something like this to combine all the fields into a SINGLE output field (you may have to change it to +):

SELECT RQ_User1&RQ_User2&RQ_User3&RQ_User4&RQ_User5&RQ_User6&RQ_User7&RQ_User8
FROM Requirements
WHERE (((RQ_User1) Is Not Null)) OR (((RQ_User2) Is Not Null)) OR (((RQ_User3) Is Not Null)) OR (((RQ_User4) Is Not Null)) OR (((RQ_User5) Is Not Null)) OR (((RQ_User6) Is Not Null)) OR (((RQ_User7) Is Not Null)) OR (((RQ_User8) Is Not Null));

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Ed2020]

Ah, but it *can* be tough, because it appears that your data is not normalised correctly and this is one of (many) problems that correct normalisation avoids.

You can achieve what you want with a UNION query:

SELECT RQ_User1 FROM Query1
   UNION
SELECT RQ_User2 FROM Query1
   UNION
SELECT RQ_User3 FROM Query1
...etc...

Ed Metcalfe

Please do not feed the trolls.....

 

[randy700]

How about...

Query1

SELECT User1 & User2 & User3 & User4 & User5 & User6 & User7 & User8 As AllUsers
FROM Requirements

Query2
Use the Find Unmatched Query Wizard to get the differences between Query1 and your DataPoints table.

Randy

 

[Hammer45]

Thank you all for your posts - I will try them out to see which one works best for my needs. As far as normalization: you're right - the price of being an only-touch-Access-once-in-a-blue-moon kind of user (and not coming from a programming background).

Nonetheless, I REALLY appreciate these forums and your quick responses. They are a lifesaver to people like me.

 

[Hammer45]

BTW, the SQL code I posted above was generated by Access - so I am wondering why it chose the "Requirements.RQ_User1, Requirements.RQ_User2..." type of statement instead of "RQ_User1, RQ_User2...." type?

Things that make you go "Hmm...

 

[lespaul]

Access automatically adds the table name to the beginning of the field name:

[tt]
Requirements.RQ_User1[/tt]

however you only need the table name qualifier if the field you are selecting is in TWO tables. In a normalized table you may have:

[tt]
Employee
EmpID
Name
Address

EmpContacts
EmpId
ContactType
ContactName
[/tt]

If you want to write a query that has all an employees emergency contacts you would have to join into the EmpContacts table. If in your query you had selected the field EmpID, you would have to indicate WHICH EmpID, the one from Employee or the one from EmpContacts. In that situation you HAVE to add the table name qualifier, but if your query is only selecting from a single table, it's not needed.

HTH

Leslie

 

[Hammer45]

Ok, the "+" did not work at all and the "&" simply concatenated all the fields in each record into one record. What I need is for each field in each record of the original table to be a distinct (omit duplicates), separate record in one column. Does that make sense?

I'm not sure how to implement the 'Union' suggestion: what do you mean by "FROM Query1"? I don't have a "Query1" in my existing list of queries - does this create it?

Thank you again for your help.

 

[lespaul]

Your original post:

to simply get all the data from the query, put it in a single column,

I took that to mean instead of a result set like this (which is what the original query returned):
[tt]
User1 User2 User3 User4
abc def ghi jkl
[/tt]

that you wanted:
[tt]
AllUsers
abcdefghijkl[/tt]

Which is what the concatenation does....

Now you're saying:

the "&" simply concatenated all the fields in each record into one record.

If that's not what you wanted, could you provide a graphical representation (like I did above) of what you DO want?




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Hammer45]

Certainly (using your example above):

User1 User2 User3 User4
abc def ghi jkl
mno pqr stu vwx

Result desired:

AllUsers
abc
def
ghi
jkl
mno
pgr
stu
vwx

Does that explain it better?

 

[randy700]

SELECT RQ_User1 FROM Query1
UNION
SELECT RQ_User2 FROM Query1
UNION
SELECT RQ_User3 FROM Query1
...etc...

You have to substitute your own table/query names.

SELECT RQ_User1 FROM Requirements
UNION
SELECT RQ_User2 FROM Requirements
UNION
SELECT RQ_User3 FROM Requirements
etc

Randy

 

[Hammer45]

WooHoo!! It worked great! Thanks for your help! Now I'll try the "Find Unmatched Query" to get my differences between this result set and the reference table Data Points.

Thanks again.