Results 1 to 4 of 4
  1. #1
    bcarter17 is offline Novice
    Windows 10 Access 2007
    Join Date
    Jun 2021
    Location
    Richmond MO
    Posts
    25

    Parent/Child.....Master/Slave.......etc. First attempt at utilizing

    Hello everyone,


    I have used Microsoft Access (2003, 2007) ever since 2002. But I have never had a situation where I felt I needed to use a Parent/Child relationship between data.

    I work for a Rock Quarry. We have our own product list of items we produce and sell. But, we also do a lot of contract/government jobs, where they want to purchase an item we sell, but want all the sales tickets, billing and documentation to under "their" product name. This creates issues with trying to keep track of inventory, production quantity, etc.

    Example -

    Item we sell -

    1" Base Rock

    We have a customer that wants to purchase it as AB3, another (Corp of Engineers) wants it show up on tickets and billing as Type 1 River Rock. And there are others similar to this. \

    But, they are all simply 1" Base Rock to us.
    We would want to be able to show sales numbers for all 3 items to be as one (1" Base Rock, AB3, Type 1 River Rock)

    I want to be able to allow our scales operator to create a ticket and billing under a sub name for the customer as they request, but, have reporting tie all of these items to the parent item (1" Base Rock) to show how much we produced to how much we have sold, etc.

    Our product list is very short, only about 30 base items from a production standpoint. I have never used parent/child process in the past, looking for some very basic guidance on how to set this up right out of the gate so I do it right.

    Any and all help always appreciated!!!!!

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,883
    Untested but offhand I'm thinking a table with ProductID, CustomerID, and CustomerText (sample names). On your internal reports you just use the product description from your product table. As needed you can join to this table to get the customer's desired text. It could also be structured to use this description if it exists, yours if it doesn't.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,854
    Here's an idea that might show a way to get started:

    CustomerProdAlias-davegri-v03.zip

    Define mapping between your product name and cust product name

    Click image for larger version. 

Name:	cartercust.png 
Views:	22 
Size:	17.8 KB 
ID:	46242

    Show the cust prod name when your prod name is chosen
    Click image for larger version. 

Name:	carterLoads.png 
Views:	23 
Size:	11.7 KB 
ID:	46243
    Last edited by davegri; 09-18-2021 at 11:07 AM.

  4. #4
    Join Date
    Apr 2017
    Posts
    1,281
    You need tables like:
    tblItems: ItemID, ItemName, ...;
    tblCustomers: CustomerID, CustomerName, ...;
    tblCustomerItems: CustomerItemID, ItemID, CustomerID, CustomerITemCode.

    Now, whenever you create e.g. an invoice for customer (and you have a tables like tblInvoices: InvoiceID, CustomerID, InvoiceDate,..., and tblInvoiceRows:InvoiceRowId, InvoiceID, ItemID, ItemQty, ItemPrice) , you can use a query as source for invoice report like
    Code:
     SELECT inv.InvoiceID, inv.InvoiceDate, inv.CustomerID, cust.CustomerName, invr.ItemID, Nz(ci.CustomerItemCode, invr.ItemName) AS ItemCode, ... FROM (((tblInvoices inv LEFT JOIN tblInvoiceRows invr ON invr.InvoiceID = inv.InvoiceID) LEFT JOIN tblCustomerItems ci ON ci.CustomerID = inv.CustomerID AND ci.ItemID = invr.ItemID) LEFT JOIN tblCustomers cust ON cust.CustomerID = inv.CustomerID) tblItems item ON item.ItemID = invr.ItemID
    Using NZ() copes with cases where there is not defined an alternative item code/name for this customer.

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

Similar Threads

  1. Replies: 20
    Last Post: 05-13-2020, 02:49 PM
  2. Update Master Table Baesd on Slave Table
    By alexbeatle in forum Queries
    Replies: 7
    Last Post: 09-21-2014, 01:06 PM
  3. Replies: 4
    Last Post: 07-08-2014, 08:59 AM
  4. Replies: 7
    Last Post: 01-21-2014, 11:32 AM
  5. Replies: 3
    Last Post: 07-03-2013, 01:20 PM

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