I'm now in charge of our envelope inventory. It's spread out in a number of locations in the warehouse. I've given the locations unique labels and there are unique labels for each type of envelope as well.
I have an spreadsheet with a row for each location then I enter what type of envelope is in the location and the quantity.
What I'm looking for is a way to call up the data by the type label and easily total what quantities I have of that particular type of envelope from the various locations.
Spread sheet headers look like this:
Location Client Description Qty Inventory Date
I have Excel 2007 and Access 2003 to work with. This feels pretty simple, but I just can't see the solution.
I looked at the pivot table. I would love some help understanding how to get what I need into the table. When I tried to come up with totals, Excel gave me a count of how many locations a description of an envelope showed up.
I would like to get this into Access in the end.
I think what I want is something like the "GROUP BY" command in SQL, like I see here:
Put the envelope descriptions in "Drop column fields" and locations in "Drop row fields" (or vice versa). Put Quantity in the Data items.
Right-click in the middle of the table and select Field settings from the menu. You'll see that you can summarize the data by count, sum, average, etc.
You can also right-click on the location and description labels and choose which descriptions or which locations you want to see. You could put the Inventory Date as a page field and decide to see the stock of envelopes at all locations that did an inventory check during the last month (for example).
In Access, I would suggest you make three tables:
Locations (at least 2 fields:LocationID (primary key) and Location names, feel free to add state, region, etc.)
Envelope types (at least 2 fields: EnvelopeID (primary key) and Description, feel free to add categories, etc.)
Inventory (LocationID (foreign key), EnvelopeID (foreign key), Quantity and Date)
The ID fields are auto-numbered and will help when you do analyses, since numbers are easier to cross-reference than words.
Start with that, and once the data is in Access, we'll talk about querying the data