Results 1 to 6 of 6
  1. #1
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43

    Many to Many With 2 Combo Boxes

    Hi,



    Table 1: Orders
    Table 2: Join Table
    Table 3: Customers

    1 order can be related to 2 or more customers (but I will only need to relate the order to 2 customers at most).
    1 customer can be related to 1 or more orders.

    In the join table, the FK from Table 1 and 3 are combined to make a composite key.

    I would like to have the entry form showing 2 combo boxes that allow me to select customers from the customer table. If this were possible, the user could see the two customers related to the order.

    Note. The above is a problem I ran into after building a small database for my team at work. The original requirement was to track orders. Each had a Customer and an Enduser. Customers buy our product and sell them to the enduser. The problem is, some times the customer can be the enduser.

    I originally built two tables, customers and endusers. This is because we rarely get complete information about the enduser; therefore, I did not want to have simply 2 out of 10 fields empty by storing customers and endusers in the same table. I think this was a mistake.

    Anyway, now I would like to store all customers + endusers in the same table but still use the two combo boxes to select and relate them to each order. I figured this would require a many to many relationship, hence the join table.

    Thank you for your help.

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Very bizarre that you would be concerned with your customers customers. I certainly would not be comfortable with this as your customer. Based on your description, it sounds like you need tblCustomers, tblOrders, tblOrderDetail and then mirror customers (tblSecCustomers) and orders (but not details) as tblSecOrders. However, it isn't obvious if you're keeping the customer details about those who are not your direct customer. Assuming yes, I probably would not have both in one table, identifying them by some other field. Rather secondary would mirror primary as noted above, updating both sides when necessary. Joining primary orders to secondary orders, and customer id's are in both order tables, you would be able to join primary to secondary on orders and return those customers where the data was known for both. The single details table could link to either or both Orders table (depending on the query need) and return the details of an order.

    I'm kind of writing off the top of my head here, since as I said, it seems like a strange situation - bordering on invasion of privacy. Perhaps this is more common than I'd expect, and I'm wet behind the ears.
    Last edited by Micron; 07-02-2018 at 08:23 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Hi Micron,

    Thanks for your help. I will review your response in more detail.

    Regarding the 'customers customers...'.

    Its a government requirement to make sure the 'bad' guys arent buying our stuff to make more 'bad' stuff.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Aha! The requirement seems to be justified. But how do you know who I sell your product to?

  5. #5
    Sunny8760 is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    43
    Good question. I request a detailed form along with the signature of the person making the purchase (our customer), which declares their customer and associated information.

  6. #6
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by Sunny8760 View Post
    Good question. I request a detailed form along with the signature of the person making the purchase (our customer), which declares their customer and associated information.
    From this follows, that the information you get about end enduser is probably more limited as information about your own customers. And the only thing you do with this information is to decide, can you sell the product to customer or not. So a separate table of endusers is the best way to go.

    A probable setup (assumed secondary customer is declared for order) is to have tables e.g
    tblSecondaryCustomers: SCID, CountryCode, FirmRegNo, FirmName, ... (where FirmRegNo is the registration number of secondary customer in its country of registration or an equivalent of it);
    tblRestrictedCountries: RCID, CountryCode;
    tblRestrictedFirms: RFID, CountryCode, FirmRegNo;
    In orders table, you have fields SecondaryCustomerCountry and SecondaryCustomerFirmRegNo.

    You have to check, that:
    1. The SecondaryCustomerCountry is not listed in tblRestrictedCountries;
    2. The SecondaryCustomerFirmRegNo from SecondaryCustomerCountry is not listed in tblRestrictedFirms.
    There is no need to list the full information about secondary customer in orders form, a single field indicating that secondary customer is restricted or not will do.

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

Similar Threads

  1. Replies: 5
    Last Post: 09-22-2016, 08:42 AM
  2. Changing text boxes to combo boxes
    By Lou_Reed in forum Access
    Replies: 8
    Last Post: 09-15-2015, 11:09 AM
  3. Linking Combo boxes and Text boxes
    By Nathan4477 in forum Forms
    Replies: 6
    Last Post: 07-29-2015, 08:50 AM
  4. Replies: 11
    Last Post: 08-06-2014, 09:47 AM
  5. Replies: 9
    Last Post: 06-04-2014, 10:45 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