Results 1 to 6 of 6

How to create a database that has multiple rows for a single value?

  1. #1
    birpal is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    19

    How to create a database that has multiple rows for a single value?

    Hello everyone, I'm trying to create a database entry form which allows me to enter multiple rows of data to a single Product #. The issue I'm having is there are 2-10 rows of data that needs to be entered for each product and each row is divided by 3 columns.

    How do I assign 10 rows to each product # without having to create a table for every product?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,393
    It sounds like you would need two tables:
    tblProducts
    Product_PK autonumber
    ProductNum Text

    tblData (or whatever you name this table)
    Data_PK autonumber
    Product_FK (RI link to tblProducts)
    Field1
    Field2
    Field3


    Then you would have a main form (record source query based on tblProducts) and a sub form (record source query based on tblData)


    But it is not very clear. Care to explain more and provide a few examples of the products/ 10 rows of data???
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    birpal is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    19
    Sorry for not clarifying, but what I'm trying to create is a form that has the Product# on the top and a table on the bottom. The table (or subform) will list all of the entries of that Product. These entries are measurements of the parts needed to create the product.

    Ex) Product: 4509


    Qty Length Width

    2 Pieces 6' 2" x 2' 4"
    4 Pieces 2' 5" x 3' 9"
    1 Pieces 3' 8" x 6' 11"
    3 Pieces 5' 9" x 8' 10"


    Each Product won't always have four rows some can be up to 10 others just 1, basically each line represents the various measurements for the building that model. This basically a database of measurements for each model. In the future I would also like to add a tab control for the variations of that model. Each model has 3 variations that should have it's own unique set of measurements. The variations are simply size differences for each product (Small, Medium, Large).

    I have about 410 Models, but definitely plan to add more.

    Thank you for replying.
    Last edited by birpal; 10-15-2016 at 09:17 PM. Reason: Additional Information

  4. #4
    birpal is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    19
    Please let me know if u need any more details.

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,393
    Ex) Product: 4509
    Is there a description in addition to the ProdNum?


    Qty Length Width

    2 Pieces 6' 2" x 2' 4"
    4 Pieces 2' 5" x 3' 9"
    1 Pieces 3' 8" x 6' 11"
    3 Pieces 5' 9" x 8' 10"
    Are you going to perform calculations using the length and width?
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  6. #6
    birpal is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    19
    I Apologize for the late reply,

    1) To answer your question regarding the "description"; there is no description. However, I would like to add one later - it would be a picture of the product.

    2) Also there are no calculations, The sizes are just the measurements needed to make the product, therefore no calculations are needed.

    3) That being said: I am planning on adding 2 additional Tabs (3 in total) - Each Tab would be labeled: Small, Medium, Large. These 3 tabs should display the measurements of the specific Product # (ex. 4509), and list the correct measurements for that size. I could add the measurements manually for each size in each Product # or if possible use a calculation to increase the values of the dimensions for each size. But this is for another time - creating multiple tabs.

    At the moment I only need to create a form that displays the Product # and the multiple sentences (Min 1 - Max 10) of dimensions listed. All in the following format:


    Product: 4509

    Qty Length Width

    2
    Pieces 6' 2" X 2' 4"
    4
    Pieces 2' 5" X 3' 9"
    1
    Pieces 3' 8" X 6' 11"
    3
    Pieces 5' 9" X 8' 10"
    Last edited by birpal; 10-22-2016 at 12:52 PM. Reason: Additional Information

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

Similar Threads

  1. Replies: 5
    Last Post: 03-06-2015, 02:04 PM
  2. Replies: 5
    Last Post: 05-10-2014, 10:05 AM
  3. Replies: 5
    Last Post: 04-10-2012, 08:53 AM
  4. Replies: 5
    Last Post: 12-01-2011, 05:38 PM
  5. Access to Excel transferring multiple rows to single row
    By peter_lawton in forum Import/Export Data
    Replies: 10
    Last Post: 09-23-2009, 10:16 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
  •  
Tech Forums: Microsoft Office Forums