Merge Multiple Tables With a Query?

[nflutter]

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.

 

[Remou]

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]

 

[nflutter]

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?

 

[Remou]

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.

 

[nflutter]

Perfect! just what i needed, thanks for your help