Migrate Excel into Access Database for query purposes HELP!

[Champ]

I have a HUGE Excel spreadsheet, 212 tabs at the bottom.
I want to import my Excel monster into Access.

I need to create a databse that will allow me to transfer my excel
masterpiece - used losely - into an Access database and do a count, by month,
of all of this data. The goal is to see how many hammers, when I ask-query,
were used on each month and each year, along with the cost, color, etc.

My information is as follows:

1st tab:
Jan 1990

2nd tab:
Feb 1990

3rd tab:
Mar 1990

4th tab:
Apr 1990

Through current Month and Year.

Each tab/sheet is as follows: (not all cells are the same on each tab/sheet
(cell A1 might be a Hammer on one sheet and a screw driver on the next sheet).

cell A1 cell B1 cell C1 cell D1 cell E1 cell
3092323 hammer green 2 $14.34 pr

cell A1 cell B1 cell C1 cell D1 cell E1 cell
4456782 tape yellow 47 $3.71 ea

and so on, to fill 65,536 lines down by 31 lines across.(lots of information)

I want to bring the information, All of the 31 lines/cells across, from each
of the 212 tabs/sheets in this workbook and import to an Access database, as
a count from the appropriate cells, as follows:

*Query *

In Jan 1989 how many hammers did we use, I want the other information to
follow.

Jan 1989

Part # Noun Color Qty. Cost Unit
Issue
4456782 tape yellow 47 $3.71 ea

and so on - basically I want to count, from each month tab and each year
tab, how many of each part went in each month and year, so I can build a
trend for inventory.

The cells in each page/tab; i.e. cell A1 on any given page is not the same
as any other page either. (that is due to some months that part was not used).

This is huge and I have no idea how to do it, so I do realize that this is
beyond what I know. Please help.

 

[rumkus]

Before importing your excel sheets I'd suggest you to do below

- copy your excel file for a backup
- on each sheet add a column as A column
- copy down your sheet name till last row on columnA ie. june 1999

Now your 212 sheets' first column are month/year column

- create an access db
- click "file"
- click "get external data"
- click "import"
- and import each sheet following the instructions

Now you have 212 tables in your access db with different names but the
same field names.
And we can talk access now.

- click "Queries" tab on your access windows
- click the top "Create query in design view"
- From the pop up asking tables pick your 2. table
- Highlight all fields from the table and drag them to the first field

Now you see all your fields are placed properly down

- click "Query" on top menu
- click "Append Query"
- as table name to be appended pick your 1. table and click "ok"
- click again "Query" and click "run"

What you have done is you added your second table to the bottom of
first table.
You can now change your first table's name to something proper.
And do the same for your 3. table to append to your newly named table.
And then 4. then 5. till the last one.
You have now learned how to make one big table out of many small ones.
You can delete the 211 tables now as we appended all to the first one.
Of course there are other ways to the same work but I think you'd
better follow the above for the moment.

Now you have one big table and nothing else.

- click "queries"
- pick your only table
- highlight all fields to drag and place below
- on top menu click " query" and "run"
- See all data is there, safe and ready to answer your questions

Now you can use access queries to obtain the desired lists out of your
big table.

Kind regards

 

[Champ]

Thanks Rumkus, I will try and let you know, this will take a bit of time, so
please be patient.

Thanks again,
Champ

 

[Larry Linson]

You would certainly not want 212 tables, each for a month/year, in Access.
You could easily start with a single table, adding month and year to the
record. You give us an example, but we don't know what the numbers in
Column A and Column D represent, nor exactly how you use the information,
nor what you intend to do similarly or differently.

What does a line in the spreadsheet represent... an inventory of a
particular part at the end of the time period? Something else?

It may be relatively simple, or a bit more complex, but, yes, you can move
your data into an Access database, maintain future data there. Some
operations you can be reading up on, if you are using "classic Access"
(Access 2003 or earlier), would be, on the menu, File | Get External Data,
and, in Help on Queries, Append Queries. Basically, you are going to Import
each worksheet, add the date information which you are now keeping in the
worksheet title on the tab, then append that to a large table. That can be
automated, but it's probably best not to try to leap feet-first into Visual
Basic for Applications code using the Access object model until you learn
how to accomplish the operations manually.

Larry Linson
Microsoft Office Access MVP