Filtering info from 2 different tables

[nate_03]

I have 3 tables called:

Dept_Name
D_Table
L_Table

I have duplicate field names for D_Table and L_Table called:

Make_Model
Date_Purchased
Invoice_Total

I am trying to create a query (eventually a report) that lists the Dept_ID (from Dept_Name) the Make_Model, Date_Purchased and Invoice_Total. I would like to extract the info from the D_Table and L_Table but have the data show up under the same column. There are no duplicate entries in either table.

While creating the query I am coming across the problem of having 2 columns containing the same kind of info

eg. D_Table.Make_Model, L_Table.Make_Model
D_Table.Date_Purchase, L_Table.Date_Purchased
D_Table.Invoice_Total, L_Table.Invoice_Purchased

I would love it if I could have a row for each field

Make
Date
Invoice

Any help would be greatly appreciated,

Nate

 

[Pat Hartman]

You need to use a union query.

Select * From D_Table
Union ALL Select * From L_Table;

For the future, you should consider having all your data in a single table rather than two tables. Use a Type field to indicate a "D" type record or an "L" type record.

 

[nate_03]

I have tried different variations but am getting an error in the FROM clause.

 

[MattS]

Can you post the SQL?

 

[nate_03]

Sure

Select * From Desktop Table
Union ALL Select * From Laptop Table
FROM [Department Table] INNER JOIN (([Employee Table] INNER JOIN [Desktop Table] ON [Employee Table].[User Name] = [Desktop Table].[User Name]) INNER JOIN [Laptop Table] ON [Employee Table].[User Name] = [Laptop Table].[User Name]) ON [Department Table].[Dept ID] = [Employee Table].[Dept ID];

 

[MattS]

OK. Firstly, if your table/field names have spaces in (try not to do this - if you must use underscore _ ), you have to include them within brackets, i.e. [Desktop Table].

On to the SQL. You're selecting everything from the Desktop table, and everything from the Laptop table. What does the department table do?

You include a FROM clause for the department table, but are selecting nothing from it.

 

[nate_03]

Ok there are 4 tables i need to draw information from.

Dept, Employee, Desktop and Laptop

I need to draw Dept ID from Dept, User Name from Employee and then Make Model, Date Purchased and Value from the Desktop and Laptop tables. These 2 tables unfortunately have the exact same field names. Herein is where the problem lies.

I cannot seem to draw that info (Make Model, Date Purchased and Value) from the Destop Table and Laptop table and conslolidate it under single respective field names.

Thanks for you help and time. Still enduring the heat wave England?

 

[MattS]

If I understand the structure of your tables correctly, the SQL should look something like this:

SELECT Dept.[Dept ID], Employee.[User Name], Desktop.[Make Model], Desktop.[Date Purchased], Desktop.Value
FROM (Desktop INNER JOIN Employee ON Desktop.[User Name] = Employee.[User Name]) INNER JOIN Dept ON Employee.[Dept id] = Dept.[Dept ID]

UNION ALL SELECT Dept.[Dept ID], Employee.[User Name], Laptop.[Make Model], Laptop.[Date Purchased], Laptop.Value
FROM (Laptop INNER JOIN Employee ON Laptop.[User Name] = Employee.[User Name]) INNER JOIN Dept ON Employee.[Dept id] = Dept.[Dept ID];

If this isn't quite right, create a new query using just the Desktop, Dept, and Employee tables. Get the fields you want, then view the SQL. Copy and paste it, add UNION ALL, and change the Desktop references to Laptop.

HTH,

Matt.

NB: Heatwave? Already fading into the distant memory!!

 

[nate_03]

Your second suggestion worked but it did'nt do what I wanted it to do. Instead it is querying duplicate User Names. It should be listing all users.

I am thinking of just merging the 2 tables together for now so I can run a report of a query.

Thanks for you help though.

 

[MattS]

Merging the two tables into one and having a type field with D or L in, like Pat suggested, would certainly solve your problem.

I don't quite understand why the union query does not give you what you want though. If you want to get it working that way, stick the tables in a small mdb, zip it, post it here, and I'll take a look.

Matt.

 

[R2D2]

You may want to just use "UNION" rather than "UNION ALL". UNION ALL will include duplicates; UNION won't.