Results 1 to 6 of 6
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    Database Setup (beginners question)


    For reference I am an Excel Pro but and Access idiot and I want to make sure I set this up right.

    The big picture is I work in Manufacturing, We take big mettle sheets and cut them into smaller panels for different jobs, sometimes there is leftover material that we save, but currently do not track.

    Example:

    I have left over yellow material on skid A for Airport 1

    I have left over green material on Skid B for Airport 1

    I have more left over yellow material on Skid C for Airport 1

    I have left over yellow material for Car Dealership 1



    eventually I want to be able to look at all of the skids of materiel for Airport 1

    And also I want to be able to look at all of the skids of yellow material regardless of what Job

    I will figure out all of the queries and forms on how to view this (a learning/training experience for me)

    but I cant seem to understand/figure out what my beginning tables should be.

    Any help is greatly appreciated.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It looks like you've got a good start:

    tbl: tStorage
    Product, Skid#, Location
    ----------------------
    yellow, A, Airport 1
    green, B, Airport 1
    yellow, C, Airport 1
    yellow, A, Car Dealer 1


    other tables:
    tLocations
    tProducts
    tSkids
    etc..

    or more flexible:

    Product, Stored, Location
    ----------------------
    yellow, skid A, Airport 1
    green, skid B, Airport 1
    yellow, closet 1, Car Dealer 1

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum....

    Quote Originally Posted by Tuckejam View Post
    For reference I am an Excel Pro but and Access idiot..
    I have never see it put this way before, but it is good to know.

    If you had a spreadsheet (or do you?)to track the left over material, what would it look like? Just trying to see/find out what you want to track. If you have a SS, would you post it or an image?
    Are the metal sheets 4X8, 4X12, 4X16,???
    Do you want to track the dimensions of the left over material?
    What else besides the Product, Skid_Number & Location(Job)? Cut date??

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Steve --good questions ssanfu!
    Knowing what material (left overs) and the dimensions and locations of same would be extremely important when reusing and reducing waste (efficiency in processing).

  5. #5
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    No SS for this project, its just something I have been wanting to do for a while.

    To give a bit more information,

    First, Every Job has a Job# and Job Name (as well as everything else you can imagine, address, contractor etc. but none of that is relevant or needed for this project)

    Most jobs we will have only on size and one color of material that we are cutting eg. 62" x 196" Silver Metallic

    However some Jobs will have multiple colors and multiples sizes (also every skid of original material that w cut has its own skid#, but thats not important for this)

    50 x 132 Light Copper
    62 x 196 Light Copper
    62 x 228 Light Copper

    50 x 132 Dark Copper
    62 x 196 Dark Copper
    62 x 228 Dark Copper

    62 x 196 Champagne Metallic


    now lets say I have already cut and have left over material from 62x196 Light copper, and next week I cut and will have left over material from the 62x132 Light copper all of that "Drop" (left over material) can go on the same skid
    and its just to labor intensive to track the size of each piece. However, my plan is for a way to keep notes on each "Drop" skid created, that way we can note "all 14 inch wide strips" or "large square material"


    So yes the end product I am trying to reach would be a form that you could enter the job number and it would pull up

    A) a section to enter data for a new skid of drop (date, color, Drop skid size, and notes)
    B) a list of all of the drop skids already created for the job.


    Also I will have another form that would allow you to choose a color and see a list of all of the skids of drop of that color


    Hope that helps explain it some,

    Thanks for taking the time, and any input is appreciated.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    You must have tables tblAirports, tblSkids (contains airport id and skid number), tblMaterials.

    To keep track of leftovers:
    tblLeftoversMovements: tblLeftoverMovementsID, MovementDate, MovementType, SkidID, MaterialID, MaterialDimensions, MaterialQty, ...

    When you have some leftovers at some skid in some airport, you register them in tblLeftoversMovements with MovementType = 1 (incoming);
    When you want to use leftovers you did find at some skid in some airport, you register the use of them in tblLeftoversMovements with MovementType = -1 (outgoing).

    The currently available quantity of certain material with certain dimensions at certain skid/location (certain skid number in certain airport) will be SUM of MovementType*MaterialQty in this location and for this material with wanted dimensions over whole tblLeftoverMovements;
    The total of currently available quantity of certain material with certain dimensions will be SUM of MovementType*MaterialQty for this material with wanted dimensions over whole tblLeftoverMovements;
    Etc. (You can give dimension ranges as search conditions too.)

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

Similar Threads

  1. Replies: 14
    Last Post: 05-05-2019, 04:37 AM
  2. Replies: 3
    Last Post: 11-07-2015, 10:13 AM
  3. Replies: 5
    Last Post: 04-01-2012, 12:50 PM
  4. Beginners question
    By nashr1928 in forum Access
    Replies: 4
    Last Post: 07-19-2010, 11:20 AM
  5. a beginners question
    By bluelondon in forum Access
    Replies: 0
    Last Post: 07-12-2007, 04:54 PM

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