PeterWieland
Registered User.
- Local time
- Today, 17:07
- Joined
- Sep 20, 2000
- Messages
- 74
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
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