+ Reply to Thread
Results 1 to 3 of 3

Historical 2d data

  1. #1
    Registered User
    Join Date
    01-04-2011
    Location
    St. Paul, Minnesota, USA
    MS-Off Ver
    Excel 2003
    Posts
    1

    Historical 2d data

    I have ten rooms that need to be kept stocked with pamphlets (about 25 different pamphlets, some could be added or removed in the future). I want to keep track of how many pamphlets remain in each room, how many I remove because they are tattered, and how many I add to replenish the stock. I gather these data on a weekly basis.

    Right now I'm using a chart with room numbers along the y axis and pamphlet titles along the x axis. Each cell in the table has space for the three numbers I mentioned previously. I print a new copy each week and fill it in by hand as I visit each room.

    I'd really like to create a spreadsheet to store the historical data (the goal is to identify trends in the popularity of various pamphlets so we know how many and how often to order more). In my head it's just a 3D table with the three axes "Room #", "Pamphlet Title", and "Date". I could create a separate sheet for each room or for each sheet to achieve three dimensions, but data entry wouldn't be pleasant. The best would be if I could just create a new sheet each week, but that leads to an ever increasing number of sheets, and that just doesn't seem like good design.

    I've started to create an Access database, but I was running into structural issues there as well (namely, adding or removing pamphlet titles would require redesigning forms and reports - not an elegant solution). I'm hoping someone here might have some thoughts that will lead me to a solution. Thanks.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2505 Win 11
    Posts
    24,719

    Re: Historical 2d data

    An Access database would work nicely here. I see the following

    Pamphlet Table
    ID--PK
    PamphletName
    OtherPamphletData
    You may want to have several fields of Pamphlet Data

    Room Table
    ID--PK
    RoomName

    InventoryTable
    ID--PK
    RoomID--FK
    PamphletID--FK
    Additions
    Withdrawals
    InvDate

    Create your tables, create your input form employing combo boxes for the Room and Pamphlets. Create your reports based upon Queries and in your inventory query you can create a running sums to have a balance.

    Post back in the Access Forum if you decide to pursue this and have further questions.

    Alan
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Historical 2d data

    Quote Originally Posted by alansidman View Post
    Post back in the Access Forum if you decide to pursue this and have further questions.
    @CDNSimpson, rather than the above, should you do opt to pursue exclusively in Access please PM a Moderator to have this thread moved to that Forum (rather than create another similar thread)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1