Results 1 to 3 of 3
  1. #1
    rochy81 is offline Novice
    Windows Vista Access 2007
    Join Date
    Sep 2008
    Posts
    22

    Advice on How to Create these Tables

    Hello,

    Can someone help me out? This is kinda hard to explain so please bare with me

    My situation:

    We receive materials from overseas. These materials come in large containers. We have about 100 different part numbers at our warehouse which come in these large containers. We'll receive about 5-7 of these containers weekly. The trucking company will send over 3 to 4 containers to us daily so for example, day 1 we'll receive 3 containers and day 2 we'll receive 4 containers totally 7 containers for that week.

    My issue is that each of these containers will contain several different part numbers. The way pack these containers make no sense either to be honest. They'll send several different part numbers in each containers with many repeats. For example, container one will have part numbers 1, 2, 3, 4 and 5 while containers two will have part numbers 1, 2, 3, 7 and 8 and etc. Also, each container will have varying quantities of each part numbers which make it even more difficult.

    I need to make a database that tracks the total quantity of each part numbers coming in these containers each time they come. The database has to have each container number, each part number and in which container they came in, total quantity for each part and the arrival date of the container.

    Please help

    Hope this made sense....
    Thank you!

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    This should be relatively simple:

    tblContainerArrivals
    ContainerArrivalID (Pk, autonumber)
    ContainerIdentifier
    ContainerArrivalDate

    tblContainerArrivalDetails
    ContainerarrivalDetailID (pk, auto)
    ContainerArrivalID (Long Int, fk)
    PartNumberID (Long Int, fk)
    PartQuantity (Integer)

    tblParts
    PartID (pk, auto)
    PartNumber (Text)

    If you see each container repeatedly, you may want to add a table of containers with its own ID field that you would store as a foreign key in the ContainerArrivals table instead of the ContainerIdentifier field (to prevent having repeating information that would violate the rules of normalization)

    Then, you would have a query that adds up the quantities delivered in each container arrival (group by ContainerArrivalID, and part number, sum on quantity field)

    Hope it gets you started

  3. #3
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    So you are going to have three tables:

    Containers:
    ContainerID – AutoNumber – PrimaryKey
    ContainerNumber - Text
    RecDate – DateTime

    Shipments:
    ContainerID – Long – ForeignKey from the Containers table
    PartID – Long – ForeignKey from the Parts table
    Quantity – Integer or Currency depending of whether you can have a partial part.

    Parts:
    PartID – AutoNumber – PrimaryKey
    PartName – Text
    Description – Text
    …any other fields you think you need that apply to the part.

    Then you create a form that has a query of the Containers table as a RecordSource of the form and at least controls for the ContainerNumber and the RecDate. The ContainerID field will take care of itself.

    Then create a form with a query of the Parts table as the RecordSource and ComboBox bound to the PartID field and a TextBox bound to the Quantity field. Have the wizard create the ComboBox for you and look up a value and save it in the PartID field. Set the Default View to Datasheet View.

    Then add a SubForm to the first form with the 2nd form as the SourceObject That’s all it should take.

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

Similar Threads

  1. create sequential id
    By proudestmnky1 in forum Programming
    Replies: 0
    Last Post: 12-16-2008, 12:10 AM
  2. Joining tables two tables and another table that is not
    By DevintheDude in forum Database Design
    Replies: 0
    Last Post: 09-12-2007, 08:56 AM
  3. Need advice on what I have so far
    By rumplestiltskin in forum Database Design
    Replies: 2
    Last Post: 05-25-2006, 12:48 PM
  4. Create the link
    By accessman2 in forum Access
    Replies: 0
    Last Post: 03-13-2006, 01:16 AM
  5. automatically create queries
    By GEORGIA in forum Programming
    Replies: 8
    Last Post: 01-23-2006, 02:35 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