Need help getting the desired results out of a query

[Michalec]

Hello

I am new to access and trying to design an inventory tracking database. Most is done but I am having trouble with a query.
I have the following:

Table 1
Fields: User / Phone / etc

Table 2
Fields: Computer / User / etc

Table 3
Fields: Printer / User / etc

I have a query that will give me the user, the computer, and the printer in a list. The problem is that it is combined and if the user has more than one computer or printer it lists the other twice. For Example

User Computer Printer
Testusr PC1 Print1
Testusr PC1 Print2
Testusr PC1 Print3

I would like it to return the data seperated instead but can't seem to figure out how to do it. example of what I want:

Testusr PC1
Testusr Print1
Testusr Print2
Testusr Print3

Any help would be apreciated. Sorry if my description is fuzzy or if this has already been answered. I couldn't really figure out how to keywoord tis problem.

Thank You

 

[lespaul]

this query will report what you're looking for:

SELECT USER, Phone FROM Table1
UNION
SELECT USER, Computer FROM Table2
UNION 
SELECT USER, Printer FROM Table3

The reason you get the "multiples" is because of the JOIN, read the article linked below for more on JOINS and how they work.

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

 

[Darrylles]

Hi Mic,

What are you intending to use the query for? A report, a form?

The returned data above is correct, in fact your expectation is also returning 'duplicate' info (Testuser 3 times).

If user has 2 pcs and a different printer for each pc, then you would want :

Testusr PC1  Print1
Testusr PC2  Print2

No?

If you want it exactly as you have shown, then:

create 2 queries: one that selects the user and the computer, the other selects the user and the printer then join them with UNION in a third query:

qry1: SELECT testusr, computer AS hardware FROM table1;
qry2: SELECT testusr, printer AS hardware from table2;

qry3: SELECT * FROM qry1 UNION SELECT * FROM qry2;

I'm not sure if you need to add the alias 'AS hardware', but better to be safe than sorry.

Hope this helps,

Regards,

Darrylle















Never argue with an idiot, he'll bring you down to his level - then beat you with experience.

 

[Michalec]

Thank You very much. It worked perfectly. Also, thanks for the links. I have never worked with SQL so it looks like I am going to get to learn some stuff.

Thanks Again