Excel wkbks.wkshts into access

[doug5jmp]

There is my computer that I will pull reports to.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
There are 5 locations entering current inventory (each with a wkbk)<o></o>
I than have a separate sheet for the backstock req'd for each inventory item<o></o>
I than a workbk that when all areas are saved will subtract needed backstock and current inventory and give the inventory needed for each location on a single page<o></o>
I than have a delivery made wkbk linked to current inventory and inventory needed for each location<o></o>
When deliveries are made to each location the delivery worksheet is filled and saved:<o></o>
Each location current inventory and inventory needed wkbks update<o></o>
<o></o>
Than the (warehouse) where the inventory is removed which is linked to the delivery wksheet...will reduce its current inventory<o></o>
Which I offset current inventory of the warehouse with its own deliveries.<o></o>
I reconcile inventory once a month on a separate wksheet as +/- current supplies...which updates the warehouse currently inventory<o></o>
<o></o>
The inventory list in column A --> "item List" heading is in A4 and the inventory items are listed below. <o></o>
All locations have the same inventory item list with the same style in each of the worksheets/workbooks. <o></o>
The dates than are listed in Row 4 with the (for example) current supplies entered by each location entered below the date, in the column.<o></o>
<o></o>
At this point I have all my count if's etc. working and the list of 19 wksheets that are linked auto populate correctly when information is entered.<o></o>
<o></o>
Introduction over: <o></o>
My First Problem is creating one location that will pull a report of all the information into one central wksheet where I can than create tables and graphs as in a pivot table.<o></o>
My Second Problem is being able to update the central wksheet when new dates are added into each location (New columns added which are currently empty) as they report in the future. [NOTE I am currently working with a excel forum member in creating a code module for the central wksht that does the stated.]
I would like to be able to see each date/column and Have a sum column for a running total.<o></o>

When I asked the above question I was referred to using Access. I can use excel fairly well, though I have never used Access. How do I create a database with the information in excel?

 

[doug5jmp]

So far my research has not presented me with any easy means of completing this task.

 

[revans]

How do I create a database with the information in excel?

By "information" do you mean you want to put the whole multi-site excel workbooks into Access or do you mean you want to create a report?

For the former, being an Access guy, I see your particular nail as fixable with an Access Hammer. I would ditch excel and replace everything with local Access front ends (forms, queries, reports) connected to an Access backend (tables). You can look for an inventory template so see if Microsoft provided you will a ready-made answer but most likely you'll have to customize. I suggest you import your sheets and try normalizing the best you can. I would not try to completely replicate Excel's rows, columns, sheets but rather look at how relational databases work.

For the latter option, you might simply import/link your sheets into Access and create some queries/reports. This can be somewhat of a pain if your data is scattered all over inside your workbooks and is not maintainable and easily broken (by adding new columns in Excel and so forth) but if you just want a report/graph etc this can be worked out.

hth,

Rich

 

[doug5jmp]

problems is...
I have never used Access. and I have already put the data into excel and placed a spreadsheet at each location.

This is inventory that has to be tracked from the main warehouse as its come in and at each location (where is will be used not sold)
Track when the item in each location is at a certain level (physical count taken each week and a back stock req'd formula used to find what is needed and the needed is sent to another sheet)
Show when the delivery from the warehouse has been done.
Update the current inventory at each location and at the warehouse.
Update the warehouse when deliveries (from vendors) are received.
Be able to enter an actual physical count of central storage every two to four weeks (due to having items removed from storage without being tracked) Thus reconcile the inventory with showing how far off each physical count and "software" estimated counts were.


Basically this is a building with 5 locations where inventory is stored in small amount which is pulled out of the central storage location (within the same building).

At this time the only way we know if an order is needed is when one of the 5 locations runs out, and then when we go to central storage to retrieve the inventory and find there is none, an order is than placed.

We are finding we run out a lot and have to wait on the vendor in order to restock the 5 locations of the item we ran out.

Logistically we can not have someone count as often as would be needed and need to be able to have each of the 5 locations do a weekly physical count and let the back stock req'd numbers determine what has to be ordered as the amount of back stock can adjust from month to month.

It seems to be a nightmare that I have created 19 linked excel files that do update automatically but I am having trouble pulling reports to a singular worksheet that will update as each date is put in each week in each locations...uhhhh!

 

[revans]

So, you need help linking to a number of excel sheets and using those links to generate reports for reorders?

Take a look at the link I provided for linking to excel in access. You should be able to get your data into access one way or another without too much trouble. I don't know what version you've got. In 2003 it's File| Get External Data| Link Tables. You can then go through the wizard to select a sheet. You'll repeat for each sheet.

Hopefully your sheets are similarly formatted so you could easily create queries for your data. Even if they're dissimilar the data can be extracted, it's just a bit more complicated--come back with a specific question at that point. Googling "Access Query Tutorial" will net you lots of links to bone up on how to make the queries you need.

To see what needs reordering a query might be all you need. You probaly won't need any forms. If a query isn't all what you want you can format your data all kinds of ways in a report. Again Googling "Access Report Tutorial" will net you lots of info on how to get started.

hth,

Rich

 

[doug5jmp]

Appreciate it, now over the next couple days I will be testing it...