Querying Multiple tables for similar values

[nateDiggs]

Hello--

I have an Access database set up with a "Users" with a unique "UserIDNumber." I have several tables that contain records indicating whether the user has a certain piece of software installed. These tables are related to the "Users" table by the "UserIDNumber." I want a user of the application to be able to open the query, enter in a UserIDNumber and have the Query show in a table what software the user in question has installed. How do I query multiple tables and have the answers put into a single table? Thanks in advance for the help!

Nate

 

[lespaul]

you join the related tables and write a query:

SELECT *
FROM Users
INNER JOIN Table1 on Users.UserIDNumber = Table1.UserIDNumber
INNER JOIN Table2 on Users.UserIDNumber = Table2.UserIDNumber
WHERE UserIDNumber = [Please enter your UserID];

you can display the query results without having to make another table

HTH

Leslie

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

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[nateDiggs]

The only problem with that is I want to display the information kept in Table1 and Table2 not the information kept in Users.

 

[nateDiggs]

Ok, I understand better now that I've tried it but when I add in the second Inner Join statement I get a syntax error in my from clause.

 

[PHV]

SELECT *
FROM [!]([/!]Users
INNER JOIN Table1 on Users.UserIDNumber = Table1.UserIDNumber[!])[/!]
INNER JOIN Table2 on Users.UserIDNumber = Table2.UserIDNumber
WHERE UserIDNumber = [Please enter your UserID];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nateDiggs]

It puts all the correct matches into one row, I need each match on a seperate row. Any way to do this?

 

[PHV]

Any chance you could post some input samples, your actual SQL code, your actual result with the above input and expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

I think you may need to define a parameter for this to work:


SELECT * FROM Table1 WHERE UserID = [Please enter userID]
UNION
SELECT * FROM Table2 WHERE UserID = [Please enter userID]
UNION
...

for each table that you need the information from and that will give you a record for each match on it's own line.

 

[nateDiggs]

SELECT *
FROM (tblUniqueUsers INNER JOIN tblBIQueryAdmin ON tblUniqueUsers.UserIDNumber = tblBIQueryAdmin.UserIDNumber) INNER JOIN tblBIQueryUser ON tblUniqueUsers.UserIDNumber = tblBIQueryUser.UserIDNumber
WHERE (((tblUniqueUsers.UserIDNumber)=[Please enter a UserIDNumber]));

The tables are setup like this:

tblUniqueUsers has all the user information including UserIDNumber which is the primary key. tblBiQueryAdmin and tblBIQueryUser both contain installation information (date of installation, time the program wans last used, etc) a unique primary key and UserIDNumber as a foreign key.

 

[nateDiggs]

That last suggestion did the trick. Thanks for the responses!