Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Merge Multiple Tables With a Query?

Status
Not open for further replies.

nflutter

Technical User
Aug 7, 2005
34
GB
I have three tables which have the same structure but different datasets. Basically the tables hold data on products and i want to produce one list that gives me the style no and the total qty in stock, being the the sum of the individual entries on each table. Also within each table the style no may be listed more than once, on the final query i want just one entry showing the total of all entries from all tables. Hope this makes sense. Any help appreciated.
 
You can use a Union query:

[tt]SELECT Table1.IDNo, Table1.Count
FROM Table1
Union All
SELECT Table2.IDNo, Table2.Count
FROM Table2
Union All
SELECT Table3.IDNo, Table3.Count
FROM Table3;[/tt]

Which you can then group:

[tt]SELECT UnionData.IDNo, Sum(UnionData.Count) AS SumOfCount
FROM UnionData
GROUP BY UnionData.IDNo;[/tt]
 
OK bear with me I`m new with this, heres my SQL but I get "syntax error in from clause" message. I`m trying with just 2 tables at first called Lymington & Inventor. Field3 is style no. and field8 is the qty.

SELECT lymington.field3, Lymington.Field8
FROM Lymington
Union All
SELECT Inventor.Field3, Inventor.Field8
FROM Inventor

SELECT UnionData.Field3, Sum(UnionData.Field8) AS SumOfCount
FROM UnionData
GROUP BY UnionData.Field3;

any suggestions?
 
You need two queries, so in query two:

SELECT UnionData.Field3, Sum(UnionData.Field8) AS SumOfCount
FROM UnionData
GROUP BY UnionData.Field3;

UnionData is the name of query one. I did not make that clear, sorry.
 
Perfect! just what i needed, thanks for your help :)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top