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.

Thanks

If it's already in Excel, it looks like a simple Pivot Table may be the answer. Let us know if you want to have more info about settng the information up in Access.

Thanks for the reply.

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:

SQL GROUP BY Statement

But, how to translate that into working with Excel and Access has got me stumped.

In Excel pivot tables,

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

Thanks again for the reply.

Beneath is a link to an image of the tables I created in Access. Description is the Primary Key in the Description table.

I was mistaken in saying that the location field was unique as there maybe more than one type of envelope in a location. There is however only one description for each type of envelope.

Let me know if I should go ahead and populate these fields by importing data from my spreadsheet. I didn't want to get ahead of myself.

http://stephensimon.net/images/Tables.jpg

Below is a screenshot of the construction of the Pivot table. I was not clear on where the the Data items went. Have I got this right?

http://stephensimon.net/images/Spreadsheet%201.jpg

One thing I'm puzzled by is why I am only getting a count of the data entry for Qty and not seeing the data itself. (refer to the link below)

http://stephensimon.net/images/Spreadsheet%202.jpg

Let me know what's next. I'll be back in the morning.

Again, Thanks