Questions

How can I arrange this data?

+
0 Votes
Locked

How can I arrange this data?

stepsimon
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 is pretty simple, but I just can't see the solution.

Thanks
  • +
    0 Votes
    Al Kostiuk

    In Excel there are a few ways to do this.
    1. You can filter your data by type and sort by location. You would have to manually select the Qty cells to generate a total. Use the AutoSum feature in the Status bar (bottom right).
    2. Use the DSUM function to total based on input type. You can setup a drop down field with the set of types to pick on as the input value.
    3. Use a Pivot table to total by type and location. You will need to refresh the table as you update the inventory.
    In Access you can create a Crosstab query which will generate the totals similar to the Pivot table in Excel. A form with a Type dropdown could also generate a total based on a query.

    +
    0 Votes
    stepsimon

    Thanks. These look promising.

    +
    0 Votes
    stepsimon

    The DSUM is working great! Now to work on the Access Crosstab.

    Thanks again.

    +
    0 Votes
    AStoerm

    Al,

    If you are working with multiple spreadsheets and multiple locations I would use Access. The advantage is is versitility and adaptability. You would import all of your spreadsheets and then run queries to sort the data. Then run reports that you can print or email if people want to view all the data. If you have ever used access this would be a very easy build. If not, you may want to stick with Excel until you have time to convert to a true Database.

    -AStoerm

  • +
    0 Votes
    Al Kostiuk

    In Excel there are a few ways to do this.
    1. You can filter your data by type and sort by location. You would have to manually select the Qty cells to generate a total. Use the AutoSum feature in the Status bar (bottom right).
    2. Use the DSUM function to total based on input type. You can setup a drop down field with the set of types to pick on as the input value.
    3. Use a Pivot table to total by type and location. You will need to refresh the table as you update the inventory.
    In Access you can create a Crosstab query which will generate the totals similar to the Pivot table in Excel. A form with a Type dropdown could also generate a total based on a query.

    +
    0 Votes
    stepsimon

    Thanks. These look promising.

    +
    0 Votes
    stepsimon

    The DSUM is working great! Now to work on the Access Crosstab.

    Thanks again.

    +
    0 Votes
    AStoerm

    Al,

    If you are working with multiple spreadsheets and multiple locations I would use Access. The advantage is is versitility and adaptability. You would import all of your spreadsheets and then run queries to sort the data. Then run reports that you can print or email if people want to view all the data. If you have ever used access this would be a very easy build. If not, you may want to stick with Excel until you have time to convert to a true Database.

    -AStoerm