merging data in 2 excel sheets into 1

[yenn]

I have multiple excel workbooks with data of products sold in different countries (1 workbook for each country).. I need to merge them into a access table to product a report of the sales in all countries/regions.

Any idea how i can do it? Is there any other way other than using access that will allow me to merge the data to produce a report?

Thanks!
yenn

 

[SBendBuckeye]

There are at least 2 ways to go about this.

1. Create a linked table to your Excel spreadsheets

2. Import the Excel spreadsheets into Access tables using
DoCmd.TransferSpreadsheet

Sorry I don't have time to give a couple detailed examples but hopefully this will at least get you started.

Good Luck!

 

[yenn]

I've already imported the excel spreadsheets into Access tables. But i realised that there are some formats other than data in the excel spreadsheets that i do not wish to import. Is it possible to import only a selection of the excel spreadsheet?

Also, i have no idea how can i merge the 2 tables into a report/ access table?

Thanks,
yenn

 

[SBendBuckeye]

The very last (I think) parmater in DoCmd.TransferSpreadsheet allows you to specify a named Range to import, although I have never used this feature.

I'm not sure I understand the problems you are having. Can you post some sample data showing what types of things you are trying to eliminate. Are the table formats the same or similar (eg column names, column sequence, data types, etc) or are they markedly different?

If they are the same format then UNION ALL will work:

SELECT Field1, Field2, Field3
FROM tbl1
UNION ALL
SELECT Field1, Field2, Field3
FROM tbl2
UNION ALL
SELECT Field1, Field2, Field3
FROM tbl3
ORDER BY Field1 DESC;

If the field names are not the same, you can specify
Order By with a column number instead of the name.

Good LucK!