If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Migrate Excel into Access Database for query purposes HELP!
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. |
#2
|
|||
|
|||
Migrate Excel into Access Database for query purposes HELP!
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 |
#3
|
|||
|
|||
Migrate Excel into Access Database for query purposes HELP!
Thanks Rumkus, I will try and let you know, this will take a bit of time, so
please be patient. Thanks again, Champ " wrote: 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 |
#4
|
|||
|
|||
Migrate Excel into Access Database for query purposes HELP!
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 "Champ" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|