Results 1 to 4 of 4
  1. #1
    quarky2001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9

    From a blank database, how to manage tracking of multiple identical items in different locations?

    I'm creating a database for my construction company to track locations and quantities of tools, which is simple enough for unique tools, but I'm having difficulty understanding how to manage something like scaffolding, where there may be an inventory of 120 identical parts, with 30 in one location, 60 in another, etc.

    I have basic Access skills, but this particular issue is posing problems for me. What's the best approach?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are the components serialized in some way? If not, all you can do is assign a quantity to a job or location without knowing anything else about them. More focused answers will require more information from you - such as what tables you're already using to track the other tools. How do those tables interact with job (or location) tables, assuming you have those. What is the goal with equipment like scaffolding - know where the parts are or just know how many you have on hand to allocate to a new job?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    quarky2001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Location
    Edmonton, AB, Canada
    Posts
    9
    Thanks for the reply. I have a single table tracking all the tools, which are serialized with a company assigned serial number, and a quantity of each isn't indicated. It's simply named drill #1, with serial number 0348, etc, since we have so few tools. However we have hundreds of legs of scaffolding, and it's impractical to assign serial numbers to each individual leg, which I think means I would have to create a separate table for them. I would, however, like to be able to create a query that tells me how many legs of scaffold are on a particular job site at the same time as it summarizes the tool inventory on that same site.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I would, however, like to be able to create a query that tells me how many legs of scaffold are on a particular job site at the same time as it summarizes the tool inventory on that same site.
    I can only guess because I still know nothing about the related tables.

    If you have a jobs table, an equipment table and a jobsEquip table (latter table is a many to many table because you will have many of any particular equipment type allocated to many jobs) then I guess your form would subtract the sum of all allocations for a type (say 60) from the type count (say you own 100 legs) and know that there are 40 left. Where the allocated ones are is what the jobsEquip table is for. You would join that table's jobFK to the jobs table (jobPK) to get the count of typeFK allocated to jobs. Since the typeFK would be a number (the PK from tblEquipment) you'd need a join between jobsEquip.EquipTypeFK on tlbEquip.EquipPK.

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

Similar Threads

  1. Replies: 7
    Last Post: 01-26-2017, 12:49 AM
  2. Open Items Tracking Database Design Questions
    By visphoto in forum Database Design
    Replies: 5
    Last Post: 03-08-2014, 07:37 AM
  3. Tracking Inventory At Multiple Locations
    By Dustin in forum Database Design
    Replies: 4
    Last Post: 12-28-2013, 10:32 PM
  4. 'SubTable' to manage items attached to a specific record
    By RichardGR in forum Database Design
    Replies: 4
    Last Post: 04-03-2013, 02:37 AM
  5. Replies: 1
    Last Post: 06-17-2012, 12:38 AM

Tags for this Thread

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