Results 1 to 9 of 9
  1. #1
    belliott4488 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    East Coast US
    Posts
    7

    Novice needs help with db for basic inventory tracking (inherited from Excel)

    I am developing a database to track the inventory of equipment owned by the astronomy club where I work. I hope a simple design will be adequate for this purpose. I'll summarize the basic design here, and then then if that looks okay, I'll post my questions next.

    == Design ==
    I have five main tables:

    Items - the main table with one record for every item in the inventory
    Categories - short list of general categories; 1 to n relationship to Items
    Subcategories - further refinement of categories; n to 1 relationship to Categories, 1 to n relationship to Items
    Transfers - this tracks the movement of each item; more below


    Locations - a list of possible locations of an item; can be the name of a club member, a room number, or a status such as "out for repair", "surplused", etc.

    These are the relationships I've defined:

    Click image for larger version. 

Name:	Screenshot db relationships.png 
Views:	39 
Size:	32.5 KB 
ID:	48093

    The Transfers table is critical, tracking the history of each item's movement.

    I've created the tables and imported the data from Excel, so I think I'm ready to create the queries and forms that I'll need to manage the database. That's where I'm having some trouble, but before I get to those problems, I wanted to see if there's anything wrong with the design as I've described it above.

    Thanks!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Might want to look at MS Access Asset Tracking template database Use the Access Asset Tracking template (microsoft.com)

    Linking of Items to Subcategories and Subcategories to Categories looks wrong.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    My advice is remove all location info from Items table. You can get the location of any item at any time moment from Transfers table.

    In Transfers table, there is no need to save previous location. The table will have the structure like
    Transfers: TransferID, TransferDate, ItemID, LocationID, Notes

    Probably you can add a table to keep history of item contition, like
    ItemCondition: ItemConditionID, ItemConditionDate, ItemID, ConditionInfo

    So the probable structure for Items table will be
    tblItems: ItemID, SubcategoryID, Brand, Model, SerialNumber, ..., Notes

    You can consider having some additional fields in Items table, like for purchase info (date, price, supplier), lease info (lease agreement number, monthly payment, lease status), etc.

    On front-end level, you need a (single) form based on Items table, with continuous subform(s) based on Transfers (and ItemCondition) table(s). And Reprot(s) which returns the location (and condition) of all/selected items at any given date. The report will be based on saved query.

    I had a similar app to keep track of IT assets (material and immaterial both), and there I had a unbound Main form where user could select item (sub)category. In Items subform of Main form, the user could select an item from this (sub)category, and edit it, or add a new item which was automatically assigned to active (sub)category. The Items form had his own continuous Transactions subform, where all transactions (transfers for you) for active item were displayed, ordered by transaction date.

    An afterthought: In Locations table you need a location for items out of use (scrap?). You can use this location either to filter out all scrapped items, or to get only scrapped items. (This was another feature of my IT assets app.)

  4. #4
    belliott4488 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    East Coast US
    Posts
    7
    Quote Originally Posted by June7 View Post
    Might want to look at MS Access Asset Tracking template database Use the Access Asset Tracking template (microsoft.com)

    Linking of Items to Subcategories and Subcategories to Categories looks wrong.
    Thanks - my first attempt was using that template, but modifying it to work with the data I have to start with looked like it would take as much or more effort than starting from scratch.

    I'm not surprised that those links look wrong - they're not working the way I want. I haven't figured out what would be right, however.

  5. #5
    belliott4488 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    East Coast US
    Posts
    7
    Quote Originally Posted by ArviLaanemets View Post
    My advice is remove all location info from Items table. You can get the location of any item at any time moment from Transfers table.
    That makes sense. I had been wondering how to enforce the requirement that the current location equals the new location from the most recent transfer (for a given item). I guess I'll build a query into any report or form that provides information for an item (?).

    Quote Originally Posted by ArviLaanemets View Post
    In Transfers table, there is no need to save previous location. The table will have the structure like
    Transfers: TransferID, TransferDate, ItemID, LocationID, Notes
    The reason I did it this way is that it's not uncommon (given the nature of this club) for club members to pass gear among themselves and forget to tell anyone. So, by the time I hear that it's going to someone new, I'm learning that it has been with someone else I hadn't known about. I guess the better solution would be to insist that the people involved provide me with the best reconstruction they can of the item's history, and then I'll add as many transfers as they can remember.

    Quote Originally Posted by ArviLaanemets View Post
    Probably you can add a table to keep history of item contition, like
    ItemCondition: ItemConditionID, ItemConditionDate, ItemID, ConditionInfo
    Yes, I'll probably do this at some point.

    Quote Originally Posted by ArviLaanemets View Post
    So the probable structure for Items table will be
    tblItems: ItemID, SubcategoryID, Brand, Model, SerialNumber, ..., Notes
    So, no assignment to a Category - I get that from the Subcategory, correct? I'm going to need help learning how to create a form that will let me select the Category first and the Subcategory from the related list.

    Quote Originally Posted by ArviLaanemets View Post
    On front-end level, you need a (single) form based on Items table, with continuous subform(s) based on Transfers (and ItemCondition) table(s). And Reprot(s) which returns the location (and condition) of all/selected items at any given date. The report will be based on saved query.
    Yes! And I have a lot to learn before I can do all that.

    Quote Originally Posted by ArviLaanemets View Post
    An afterthought: In Locations table you need a location for items out of use (scrap?). You can use this location either to filter out all scrapped items, or to get only scrapped items. (This was another feature of my IT assets app.)
    Yes - that's what that "Surplused" value for Location means. That's what they call "in the trash bin" where I work.

    Thank you!

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by belliott4488 View Post
    I had been wondering how to enforce the requirement that the current location equals the new location from the most recent transfer (for a given item).
    Create an UDF (User Defined Function), which returns latest value of asked table field (i.e. location for this task) for given filter condition. E.g. using an query like
    Code:
    SELECT TOP 1 LocationID FROM Transfers WHERE ItemID = SomeItemID AND TransferDate <= Date() ORDER BY TransferDate DESC
    And then you add an unbound control into your Items form, which uses this UDF to return the current location for active item.

    The reason I did it this way is that it's not uncommon (given the nature of this club) for club members to pass gear among themselves and forget to tell anyone. So, by the time I hear that it's going to someone new, I'm learning that it has been with someone else I hadn't known about. I guess the better solution would be to insist that the people involved provide me with the best reconstruction they can of the item's history, and then I'll add as many transfers as they can remember.
    When you discover, that some item's current location is wrong (because the item was relocated without informing you), then additionally having the wrong previous location of this item too doesn't help you in any way! You simply have to register missed location(s) of this item, based on info you get from employee.

    So, no assignment to a Category - I get that from the Subcategory, correct? I'm going to need help learning how to create a form that will let me select the Category first and the Subcategory from the related list.
    Or you do like I did in my app - in unbound main form you have a single combo, which selects a subcategory, but displays both category and subcategory (like "Computer: Desktop"). The query used for this is ordered by this text, so selecting the right one will be simple and fast. (Remember - when you start typing into combo, in combo's search list you are moved to first entry beginning with entered string!)

  7. #7
    belliott4488 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    East Coast US
    Posts
    7
    Thank you! I think I understand your suggestions, although I have a lot learn about how to implement them. Now I know what to search for as I look for tutorials, etc., so this is a big help.

    I'm sure I'll have more questions as I go, but this looks like enough for me to take the next few steps.

    - Bruce

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    in unbound main form you have a single combo, which selects a subcategory, but displays both category and subcategory (like "Computer: Desktop").
    Cascading combo boxes would allow you to select a category, then a subcategory.

    ---------------------------------------------
    You should not use spaces in object names.
    And having "ID" as the PK field name in all tables is (IMHO) poor programming.

  9. #9
    belliott4488 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    East Coast US
    Posts
    7
    Thanks - I didn't even know about cascading combo boxes, but they sound like just what I need.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Basic Coding Question - Novice
    By PinkLady50 in forum Access
    Replies: 8
    Last Post: 03-22-2018, 01:24 PM
  2. Relative Novice - Access Tracking
    By ArmandKruger in forum Database Design
    Replies: 3
    Last Post: 05-29-2015, 08:02 AM
  3. Replies: 2
    Last Post: 02-05-2015, 02:50 PM
  4. Tracking Inventory with Multipacks
    By CaneRivero in forum Database Design
    Replies: 8
    Last Post: 12-03-2013, 01:03 PM
  5. Inventory Tracking
    By PUDrummer in forum Access
    Replies: 3
    Last Post: 10-10-2012, 05:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums