Results 1 to 8 of 8
  1. #1
    Jmoore86 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    9

    Table Links to add own quantities to central list of products.

    Hi All,
    Sorry if this is a bit basic...



    I've got a simple DB that has a table with a list of products (names, UOMs etc...) and i've which is linked to another table (called orders) where I want to select from that list of products and enter my own quantities. How do I set up the orders table to allow me to do this? I've got it so I can link products to the order via a lookup but I can't seem to set my own quantities against each item.

    The table(s) need to related in a way that will allow me to raise an order, select a product from the 'Product' table and enter quantities against each related product. These quantities could, obviously, be different from order to order... is this (relatively) simple to achieve?

    Cheers,
    Jon

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you should have a minimum of three tables

    tblProducts
    ProductPK
    ProductName
    ….
    ...

    tblOrderHeaders
    OrderPK
    Customer
    OrderDate



    tblOrderLines
    OrderLinePK
    OrderFK - link back to OrderPK in tblOrders
    ProductFK - link back to ProductPK in tblProducts
    Quantity
    Price
    ….
    ...

  3. #3
    Jmoore86 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    9
    Thanks Ajax my requirements have changed a tiny bit; can you possibly have a quick check over what i've gone with below?

    The scope here is that i need to have a list of 'Platters' that are made up of individual products - these products can appear on multiple platters. I need to maintain a list of products and manually assign them to platters.

    The basic premise is that I have a list of platters (tblPlatterHeader) that has an ID (PK), name and price (also an FK back to a platter lines table). These platters are made up of individual products. For this i've created tblProducts (contains ProductID (PK), Description, UOM and an FK back to the platter lines table) and tblPlatterLines which has an ID (PK), FKs back to the products and platter header tables as well as quantities and UOM fields. Does this sound about right? I've included a picture of the relationship diagram below - will this give me what i'm after if i create a number of forms for data entry?

    Click image for larger version. 

Name:	Relationships.PNG 
Views:	14 
Size:	33.1 KB 
ID:	35800

    Cheers!

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks ok to me in principle. Not sure what UOM is or why you have it repeated in products and platter lines

    Also don't see why you have 3 fields as a PK for platterLines, should only need PlatLineID.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can set PlatID and ProdID as unique index to prevent duplicate pairs but as Ajax said, don't need to be defined as part of primary key.
    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.

  6. #6
    Jmoore86 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    9
    Excellent, thanks for your help - i've removed the erroneous PK's and the extra UOM; that was an error on my part.

    There's now an 'orders' table that has the platter name on it, not the individual products and I need to write a query that shows the order number, platter name and the related products & quantities... when I try to write this query after linking the orders table to the platter header table, i get an error to do with an ambiguous outer join... what can I do to get the query that 'm looking for? Have i put the relationship in incorrectly?

    Thanks again

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    cant really suggest anything unless we can see your query

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Platter can be associated with many products. If you save just platter name (or ID), query won't know which PlatLineID needs to be referred to for the link to tblProducts, hence ambiguous join error.

    Should Orders table not have PlatLineID values?
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-11-2015, 08:02 PM
  2. Item Master List | Products Table
    By namu23 in forum Access
    Replies: 4
    Last Post: 10-02-2013, 10:33 PM
  3. Deduct sell products from receiving list
    By Georgi in forum Queries
    Replies: 4
    Last Post: 05-22-2013, 01:55 PM
  4. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  5. Replies: 3
    Last Post: 01-30-2012, 09:57 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