Importing a Spreadsheet with more than one workbook

[newton1234]

Hi,

I have been given a large project, one stage is to import a spreadsheet which has 3 tabs (worksheets).

I need to be able to create a query but based on all the information created in the 3 tabs.

Does anyone know of a way of how to get the 3 tabs into 1 so I can create a single query result.

I am grateful for any help that can be given.

If you require further info please let me know.

 

[neileg]

AFAIK you can only import one tab at a time, though you could wrap this up in code to do all three. However, do you really need to import this? Would linking the tabs not work for you?

 

[newton1234]

Thanks, I thought with linking tables you can still only import one tab at a time ? and although all the tabs hold the same information there is no common link between them to build a query using the 3 seperate tables.

Thanks in advance for your help.

 

[Rabbie]

If there is no common link then the information can't be the same. Please elucidate.

 

[newton1234]

The 3 tabs hold Account Numbers, Dates, Time and Quantity.

Therefore these are all variable and never the same for all 3 tabs.

Would it be easier to create a macro in excel first which creates a 4th tab amalgamating the other 3 ?

Thanks

 

[newton1234]

Hi,

Thought I would post a reply as managed to do this by linking the 3 tabs seperately and creating a Make Table query on one and an append query on the other 2.

Thanks

 

[neileg]

I would have used a Union query as this works dynamically whereas a table is fixed.

 

[newton1234]

Thanks, I work with Business Objects and believe a union query would work better. However I dont seem to have this option in Query. I am running Access 2003. Can you advise where I can make this query ?

Thanks

 

[Rabbie]

Just open the Query design form in SQL View and then you can create your Union query.

 

[neileg]

The Access help file is pretty good on union queries.