Results 1 to 10 of 10
  1. #1
    Sc0tt1e is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    13

    Multiple Customers to one product

    I'm trying to work out how I produce a form that can call the information for multiple people based on a single product.



    I'm thinking a multiple tab/navigation form each tab calls the information for each individual based on the clients relating to the product (loan)
    1 Main form product specific
    1 sub tab form (up to 4 tabs) each specific to an individual.

    I dont even know what I need to be googling. Can anyone suggest a method to research so I can work out architecture/structure?

    P.s. There is no example to show, at this stage it is just theory.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,525
    Make a query to pull the invoices.customer with the given product.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,659
    You add or remove a person, then what? Add or disable a tab control page? That would not make sense. Did you consider a main form for product details and a subform to list the people?
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  4. #4
    Sc0tt1e is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    13
    This is a workflow tool I am trying to build. A property being purchased will have a certain number of applicants (1-4). When I select the subject property (the product) I want the information I have created the form to show visible for each person associated to that purchase.

    A multi tab showing name age income occupation etc which will be populated for each customer is the desired outcome.

    If I add a customer to the purchase I want the form to add them to the list of customers (i.e. Mr & Mrs cant afford so Mrs Dad is added to the mortgage for affordability 3 instead of 2 tabs are populated.

    I hope this is making sense, I know what I want I just don't know how to go about building it from a relational aspect. How do I create a relationship that allows me to do the above?

    I want the info in forms so it can be amended if required.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,659
    To me it reads like you should have tblProperty for all the property details, tblCustomer (or whatever you're calling these people) with their details. To relate people to property you need tblPropCust (junction table) where the pk for this can be a simple autonumber. The PKs from the 2 other tables are stored in the junction table as foreign keys. So to show that property id 55 has 3 applicants (people ID's of 7,8,9)

    tblPropApps
    PropAppID PropID_fk CustID_fk
    1 55 7
    2 55 8
    3 55 9

    You can get the property and customer details by linking the fk fields in this table to their respective tables. I get the feeling that you need to research db normalization - based on your post.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  6. #6
    Sc0tt1e is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    13
    I have set up a customer table and a property table and have a join table with the relationship. Customers 1, 2, & 3 are joined to property 3 and each one of these joins has a unique primary ID 7,8,9.

    So sounds like I've set it up correctly (and yes the structure meets DB normalisation rules )

    The bit I'm struggling to get my head around is how does each tab know who to show the info for, how does tab 1 know to show Mr, tab 2 show Mrs instead of all tabs showing just Mr and all tabs trying to show everyone???

  7. #7
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    7,659
    I can only envision a code solution whereby you get the customer ID values as an array or delimited string (this might be values 56, 57 and 65) and when the form opens, step through the array/string and populate each tab with records (a datasheet form?) pertaining to the customer ID and property ID, the latter coming from the array. Move to the next array member and populate the next tab and so on. If there are only 2 array/string values then your loop to populate the tabs would only run twice. Again, 4 customers and 3 tabs and you have a problem. I still cannot see why your customers are not simply listed in a subform (continuous or datasheet view) on the main form holding the property record(s) and in my view, you're vastly over complicating this.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,123
    Sc0tt1e,

    I dont even know what I need to be googling. Can anyone suggest a method to research so I can work out architecture/structure?
    Start with a clear description of your proposed business. Start at the 30,000 ft level and gradually add detail.

    Work through 1 or 2 of these tutorials from RogersAccessLibrary
    -zyx Labs
    -Class Info System
    See the descriptions in these tutorials to see the level of info involved.

    Work through the process and you will learn how to build a model (blue print) for your database.
    The tutorials come with problem statements, step descriptions and solutions.
    What you learn can be used with any database.


    Your form, which is the user interface to the database, comes after you have identified and vetted the underlying tables and relationships.
    Good luck.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,186
    I'd go with design, where you have an unbound main form with tab control.

    On one of tab pages, you'll have products form with subform where you can see/add/delete relations of clients to active product;
    On other of tab pages, you'll have clients form with subform where you can see/add/delete relations of products to active client.

    Probably you want some events to synchronize both forms and subforms. E.g. you have come product active in 1st tab page, and certain client related with this product active in it's subform. When you switch to 2nd page, you want product active in 1st subform to be selected in form in 2nd page, and product selected in 1st form to be active in 2nd subform. And vice versa.

    So you don't need a page for every client - you simply select a client in second form. An additional advantage is, that when you get a new client later, you don't have to design your app anew every time this happens.

  10. #10
    Sc0tt1e is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Jun 2020
    Posts
    13
    This is ultimately what I want to be able to see

    44 High Street
    Customer 1 Customer 2 Customer 3 Customer 4
    Name
    DOB
    Address 1
    Address 2
    City
    County
    Post Code
    Income
    Employer


    I am a mortgage broker, I am trying to create a compliance/workflow tool that steps me thorough the mortgage application process.

    I have customers that are associated to the purchase of a property and I can work out how to use the property as the main and pull through a sub form of the customers associated to that form but I want to use either customer or the Ltd company they are purchasing the property in as the main and have them together on a screen (Mr & Mrs details together relating to the property they are buying.

    I have uploaded the Relationship structure of the DB I have so far. I know this is not going to be the final structure but I also don't know what/how I need to build so by understanding the end result I can back engineer the structure to accommodate the requirements of the build
    Attached Thumbnails Attached Thumbnails Relationships.jpg  

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

Similar Threads

  1. Replies: 6
    Last Post: 02-28-2015, 11:57 AM
  2. Replies: 1
    Last Post: 09-09-2014, 11:29 PM
  3. Replies: 1
    Last Post: 08-28-2013, 02:27 AM
  4. Enter Orders for Multiple Customers in a Form
    By nelsonsbrian in forum Forms
    Replies: 1
    Last Post: 07-16-2013, 03:29 PM
  5. Replies: 1
    Last Post: 05-05-2010, 10:34 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 - Senior Forums