Results 1 to 3 of 3
  1. #1
    Michael1926 is offline Novice
    Windows XP Access 2007
    Join Date
    May 2018
    Posts
    10

    One Table take informations from 2 tables what is the relation one to many or many to many?

    Hello everyone!

    I've 1 table with the name Salesman



    (Fields: code_sales, Lastname, Username, Department, Phone number etc)


    and a 2nd table with the name Products.

    (Fields: code_product, Name of the product , From which country , date etc)


    Each table have some properties like , specific characteristics with numbers , date, subnet mask etc.
    The task "tells" me that i've to create a 3rd table with the name Sales. The table Sales will accept from the table Salesman the code_sales and from the table Products will accept the code_product, there are also some fields.

    Can this happen simltaneously in the table Sales? and how?


    I am thinking that a potential solution might be like this:

    tblsalesman relation to tblsales (one to many)
    tblproducts relation to tblsales (one to many)

    the primary keys are in the first table the code_sales and in the seconde table the code_product, so in the third tblsales i will enter the the code_sales and code_product (numbers).

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    that is the way to handle many to many relationships

    Can this happen simultaneously in the table Sales? and how?
    that very much depends on how your app works, but in principle and from your description, someone is entering sales of products against salespeople - and with the information you can look up who is 'allowed' to sell a particular product.

    If this is the case then you would have a mainform populated with the relevant data from the salesperson record and a subform populated with the sales table. The subform only needs to show the FK to the products table in a combobox to display the product name. Ensure the subform linkmaster/child properties are populated with the relevant salesperson PK/FK field names - this will happen automatically if you have set referential integrity in your relationships, otherwise you will need to set manually. As the user selects products, the salespersonFK field will be populated automatically. If you need to know more than indicated above, your link table may also contain other fields such as a date, quantity, value etc.

    However, it may be sales of products are being recorded in an invoice, in which case I would expect the invoice header to be your link - salesperson>invoice header>invoice line>product - where there is no actually need for a many to many relationship unless an invoice can be shared between many salespeople. And if this is the case then perhaps the sales person would be linked to the invoice line table instead - again unless sales people share that particular invoice line.

    Suggest you need to map out your entire app before starting otherwise you will be constantly rebuilding the tables to get the relationships right.

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    think of the first 2 tables as lists from which to look up

    your 3rd table is the actual transaction table - that includes a value from each of the 2 lists.

    note that most sales oriented db apps require 2 transaction tables - the Parent holds the common info of the sale; customer, date, Purchase Order - - and then the linked child table holds the Product lines, quantity, etc

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

Similar Threads

  1. Replies: 13
    Last Post: 03-29-2018, 11:09 AM
  2. query with relation in tables problem
    By JeroenMioch in forum Access
    Replies: 4
    Last Post: 04-01-2016, 03:06 AM
  3. Relation between tables
    By vincentsp in forum Forms
    Replies: 8
    Last Post: 02-03-2015, 08:53 AM
  4. how to make relation tables
    By Bala in forum Access
    Replies: 1
    Last Post: 11-07-2013, 06:35 AM
  5. Relation in three different tables
    By kzdev in forum Access
    Replies: 1
    Last Post: 11-22-2010, 03:12 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