Merge and add values from multiple tables (1 Viewer)

[PeterWieland]

Hi,

I thought this would be really easy, but I am struggling to find a solution.

I have several part lists in seperate spreadsheets which I want to import into access as one table. Each spreadsheet has the same columns, but some parts appear in only one spreadsheet, some in several sheets. I want to combine all this data, creating a new row for unique parts, and adding the quantities for matched parts. The match will be on multiple fields, and I have imported the spreadsheets into Access 2007 as seperate tables of identical structure.

Sheet A
PartNo Colour Finish Qty
1 Y 10
2 Y 6
3 Bk St 7
7 Zn 2

Sheet B
PartNo Colour Finish Qty
1 Y 6
3 Bl 4
9 Gn 5

Sheet C
PartNo Colour Finish Qty
1 Y 5
3 Bk St 2
3 Bl 12
9 Gn 7

Result
PartNo Colour Finish Qty
1 Y 21
2 Y 6
3 Bk St 9
3 Bl 16
7 Zn 2
9 Gn 12

I've searched for hours, and have played about with Unions, Joins, Append queries and loads of other things but I'm nowhere near finding a solution.

Any help appreciated.
Pete

 

[Galaxiom]

I assume there are Nulls in the Finish field for those with only three values.

SELECT PartNo, Colour, Finish, SUM(Qty)
FROM (
   SELECT *  FROM SheetA
 UNION ALL
   SELECT *  FROM SheetB
 UNION ALL
   SELECT *  FROM SheetC
) AS X
GROUP BY X.PartNo, X.Colour, X.Finish
ORDER BY X.PartNo

 

[PeterWieland]

Thanks for that. Yes, there are nulls, but not just in the Finish field, there are nulls in the Colour field as well.

This seems to do the trick. There are a lot more fields involved in the full query, but this gives me the basic principle so I should be able to sort it from here.

Thanks again