locked
Relate Fields in Main Form to Details in Sub Form. RRS feed

  • Question

  • I am creating a customer database for quoting out new jobs.  At times, for each customer, we break up those jobs into 3 or 4 options.  So into my main form I have 3/4 subforms which contain all of my products.  I total it up in the footer of the subform and I link that to that box in my main form and I am pleased with that!  My question is this...I would like to give a desciption, a discount, and some notes which relate to that particular sub form (for each customer) but enter that information into the main form and not that particular sub form (because it is just easier to view and keep track of).  How do I relate these unbound fields to this particular subform?  Right now when I fill in one customer, the same information shows up for all customers.  Hope that makes sense!

    Thanks!


    Tuesday, March 8, 2016 8:30 PM

Answers

  • You've made a good start for someone with so little experience, but the mistake you've made, and it's not an uncommon one, is to have multiple OrderDetails tables.  This is what's known as 'encoding data as table names'.  I'm not sure what the three 'locations' are in real life terms, but they are presumably each a different category of 'location'.  Tables model entity types, whereas, the three categories are data.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    What you should have is a single OrderDetails table with foreign key columns OrderID and ProductID, a non-key Quantity column, along with another column for the 'location'.  This would most probably be a foreign key LocationID column which references a Locations table in which there are three rows, one for each type of 'location'.  There might be one or more other non-key columns which represent  an attribute of the order detail's relationship with a 'location', but until I know more about what 'location' actually means in real world terms I can't be categorical at this stage.

    The OrderDetails table would also need a UnitPrice column.  The UnitPrice in Products represents the current price of a product, but this can change over time, so to retain the price which was current when an order was raised it's necessary to have a UnitPrice column in OrderDetails also.  The subform is then set up so that when you select a product its current unit price is looked up in Products and assigned to the column n OrderDetails.  The sample Northwind database which ships with Access does this, though in an unnecessarily long-winded way; it can be done a lot more simple.

    If you can put a bit more flesh on the bones for me as regards 'locations' we can then take a look at how the model needs amending in more detail, and how you'd interface with the data via the form, but with a single OrderDetails table you can still have three subforms, one for each 'location' category, by basing each on a query which is restricted to a separate category in each case.

    Ken Sheridan, Stafford, England

    • Marked as answer by jpd2 Thursday, March 10, 2016 2:08 PM
    Wednesday, March 9, 2016 5:14 PM
  • You need to add a Locations table to the model, between the Orders and OrderDetails table:

    Locations
    ….LocationID  (PK)
    ….OrderID  (FK)
    ….LocationDescription
    ….Discount
    ....Notes

    Note that I've included the columns for the additional attributes you referred to in your original post in this table as you seemed to indicate that these were attributes of each location.  The OrderDetails table will now have a LocationID foreign key column in place of the OrderID column, e.g.

    OrderDetails
    ….LocationID  (FK)
    ….ProductID  (FK)
    ….UnitPrice
    ….Quantity

    The primary key is a composite one of the two foreign key columns LocationID and ProductID.  The Model is therefore now:

    Orders----<Locations----<OrderDetails>----Products

    In an orders form you either, can include a locations subform, and within it an order details subform, i.e. nested subforms; or you can use separate locations and order details subforms within the orders form, i.e. correlated subforms.  You'll find examples of both, using Northwind data, in CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Any summary totals derived from the aggregation of amounts in the subforms' records can be returned in unbound controls on the parent form. My demo simply totals the amounts for each order order in the order details subform footer, but the expression in the control could be more complex, referencing the discount from the location subform and adjusting the total accordingly.

    I don't see how you could have a fixed number of subforms as originally proposed, one per location, however, as surely the number of locations will vary per order?

    For production of the quote itself a report should be generated as this allows grouping of the data by location.  If you take a look at the InvoicePDF demo in my same OneDrive folder, for instance, you'll see how the inv ice is generated as a report on the basis of the data in the tables entered via the form.  In this report the report groups the items by the rate of VAT (the European Value Added Tax) which has variable rates, the subtotals per rate are computed independently to get the amounts gross of VAT for each rate, which are then added to get the invoice total before subtracting any payments made to date to return the amount due.  Your report would not be the same of course, but would subtotal per location in a similar way, and would be simpler as I assume you would not have the complication of variable VAT rates or similar applied per group.

    Ken Sheridan, Stafford, England

    Wednesday, March 9, 2016 7:08 PM

All replies

  • That would be the wrong way to do it. If the data is related to the record in the subform then it either belongs in that recordset or in a separate table that is related to the subform record.

    Bill Mosca
    www.thatlldoit.com
    http://tech.groups.yahoo.com/group/MS_Access_Professionals

    Tuesday, March 8, 2016 9:20 PM
  •   How do I relate these unbound fields to this particular subform?  Right now when I fill in one customer, the same information shows up for all customers.  Hope that makes sense!

    That would be an unbound TextBox and because it is not bound there is no record that it is tied to.

    Build a little, test a little

    Tuesday, March 8, 2016 9:27 PM
  • A form and its subform(s) merely reflect the underlying logical model, so you firstly need to think in terms of the model not the forms.  From your description the current model is, I imagine, either something along the following lines:

    Customers---<Jobs----<JobProducts>----Products>---ProductCategories

    or something along the following lines:

    Customers---<Jobs----<JobProductCategories>----JobCategories
                                                 |
                                                  >----Products

    In the first products are categorised and JobProducts models a binary many-to-many relationship type between jobs and products.  The second categorises jobs, with JobProductCategories modelling a ternary many-to-many relationship type between Jobs, JobCategories and products. The subforms would be thus based on either queries on JobProducts or JobProductCategories, in each case being restricted to a particular category of product or job.

    From your description my impression is that the additional attributes are of each category per job, in which case it would be necessary to introduce a further table, e.g. in the first scenario above:

    Customers---<Jobs----<JobProducts>----Products>---ProductCategories
                            |                                                                               |
                             -------------<JobCategoryDetails>-----------------

    In the second scenario:

    Customers---<Jobs----<JobProductCategories>----JobCategories
                             |                  |                                               |
                             |                   >----Products                        |
                             |                                                                  |
                              ---------<JobCategoryDetails>-------------

    In either case a further subform in the parent would be required.

    Ken Sheridan, Stafford, England








    Wednesday, March 9, 2016 1:27 PM
  • Thanks Ken for your thoughtful explaination!  I think that my current relationships are probably off to start with and I need to make corrections to that and then add the table as you suggested.  I am very new to Access, I have owned it just over 2 weeks with only a 6hr online class to go on, so any advice that you are willing to give is GREATLY appreciated.  Would you mind looking at my relationships below and letting me know what I need to do.

    Thanks again!!

    (The underlined words are the table names)

    Customers

    CustomerID (1)

    FirstName

    LastName

    Address

    Etc

    Orders

    OrderID (1)

    (M) CustomerID

    OrderDate

    OrderDetailsLocation1

    LocationID

    Location1Description

    (M) OrderID

    ProductID (M)

    Quantity

    ***I then have similar tables for OrderDetailsLocation2 and Location3 same one many relationships with Order ID and ProductID

    Products

    ProductID

    ProductCode

    ProductDescription

    UnitPrice

    • Edited by jpd2 Wednesday, March 9, 2016 3:27 PM
    Wednesday, March 9, 2016 3:21 PM
  • You've made a good start for someone with so little experience, but the mistake you've made, and it's not an uncommon one, is to have multiple OrderDetails tables.  This is what's known as 'encoding data as table names'.  I'm not sure what the three 'locations' are in real life terms, but they are presumably each a different category of 'location'.  Tables model entity types, whereas, the three categories are data.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    What you should have is a single OrderDetails table with foreign key columns OrderID and ProductID, a non-key Quantity column, along with another column for the 'location'.  This would most probably be a foreign key LocationID column which references a Locations table in which there are three rows, one for each type of 'location'.  There might be one or more other non-key columns which represent  an attribute of the order detail's relationship with a 'location', but until I know more about what 'location' actually means in real world terms I can't be categorical at this stage.

    The OrderDetails table would also need a UnitPrice column.  The UnitPrice in Products represents the current price of a product, but this can change over time, so to retain the price which was current when an order was raised it's necessary to have a UnitPrice column in OrderDetails also.  The subform is then set up so that when you select a product its current unit price is looked up in Products and assigned to the column n OrderDetails.  The sample Northwind database which ships with Access does this, though in an unnecessarily long-winded way; it can be done a lot more simple.

    If you can put a bit more flesh on the bones for me as regards 'locations' we can then take a look at how the model needs amending in more detail, and how you'd interface with the data via the form, but with a single OrderDetails table you can still have three subforms, one for each 'location' category, by basing each on a query which is restricted to a separate category in each case.

    Ken Sheridan, Stafford, England

    • Marked as answer by jpd2 Thursday, March 10, 2016 2:08 PM
    Wednesday, March 9, 2016 5:14 PM
  • Thanks for answering back so quickly...and for explaining it all in such a way that I could understand it.  I had a gut feeling that the 3 OrderDetailsLocation's weren't right, I just don't know enough to put it all together - so your help is invaluable.

    So "locations" for us means a breakdown of different physical areas of a job.  We are a residential landscape lighting contractor and generally are asked to break up our overall quote into smaller pieces.  Here is an example of what we want to show up on our quote (report):

    "Illuminate front of house, flag pole and large tree" (ie Location 1)

    1  200 Watt LED Transformer

    1  60 Watt LED Transformer

    2  Digital Astronomical Timers

    7  LED Spot Lights

    2  LED Flood Lights

    Regular Price = $2541

    Spring Sale Price = $2287

    "Illuminate 3 landscape beds" (ie Location 2)

    6 LED Path Lights

    Regular Price = $881

    Spring Sale Price = $793

    "Illuminate backyard" (ie Location 3)

    Etc.

    So that is pretty much it, a simple quote form to relay that information.  I would like the subform to contain the fields for ProductCode, Product Description (which will pull into the Quote as above), Quantity (which will pull into the quote as above) and Total.  Then in the main form, next to each of the sub forms the Location, pull out the Total from the subform (which will pull as the "Regular Price" in the Quote as above), Discount, then Calculated Total-Discount Price (which will pull as the "Spring Sale Price" in the Quote as above), then finally a section for notes that will go under the items in the quote for options or special criteria (ie Upgrade transformer from 300 watt to 500 watt to accomodate future expansion, ADD $240"

    Hopefully that is what you need.  Thanks again!!!

     


    • Edited by jpd2 Wednesday, March 9, 2016 6:35 PM
    Wednesday, March 9, 2016 6:09 PM
  • You need to add a Locations table to the model, between the Orders and OrderDetails table:

    Locations
    ….LocationID  (PK)
    ….OrderID  (FK)
    ….LocationDescription
    ….Discount
    ....Notes

    Note that I've included the columns for the additional attributes you referred to in your original post in this table as you seemed to indicate that these were attributes of each location.  The OrderDetails table will now have a LocationID foreign key column in place of the OrderID column, e.g.

    OrderDetails
    ….LocationID  (FK)
    ….ProductID  (FK)
    ….UnitPrice
    ….Quantity

    The primary key is a composite one of the two foreign key columns LocationID and ProductID.  The Model is therefore now:

    Orders----<Locations----<OrderDetails>----Products

    In an orders form you either, can include a locations subform, and within it an order details subform, i.e. nested subforms; or you can use separate locations and order details subforms within the orders form, i.e. correlated subforms.  You'll find examples of both, using Northwind data, in CorrelatedSubs.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Any summary totals derived from the aggregation of amounts in the subforms' records can be returned in unbound controls on the parent form. My demo simply totals the amounts for each order order in the order details subform footer, but the expression in the control could be more complex, referencing the discount from the location subform and adjusting the total accordingly.

    I don't see how you could have a fixed number of subforms as originally proposed, one per location, however, as surely the number of locations will vary per order?

    For production of the quote itself a report should be generated as this allows grouping of the data by location.  If you take a look at the InvoicePDF demo in my same OneDrive folder, for instance, you'll see how the inv ice is generated as a report on the basis of the data in the tables entered via the form.  In this report the report groups the items by the rate of VAT (the European Value Added Tax) which has variable rates, the subtotals per rate are computed independently to get the amounts gross of VAT for each rate, which are then added to get the invoice total before subtracting any payments made to date to return the amount due.  Your report would not be the same of course, but would subtotal per location in a similar way, and would be simpler as I assume you would not have the complication of variable VAT rates or similar applied per group.

    Ken Sheridan, Stafford, England

    Wednesday, March 9, 2016 7:08 PM
  • Awesome!  So far I:

    • removed the OrderDetailsLocations 1, 2, 3 tables from the relationship
    • I created the Locations table (thank you for the details that go into it!)
    • I created the OrderDetails table (thank you for the details that go into it!)
    • I created the PK's & FK's as you recommended

    Question:  The LocationID (PK) in the Locations table is creating a 1 to 1 with the LocationID (FK) in the OrderDetails table.  Is that correct.  I have 1 to many relationships for all other.

    I am now reviewing your Northwind Data in your folder...I am sure I will have more questions to come!

    Wednesday, March 9, 2016 8:08 PM
  • I think I figured out my above question.  I didn't in fact have a 2nd key in the OrderDetails table for ProductID, I just had the relationship line.  I added it and now I have a 1 LocationID (Locations) to Many LocationID (OrderDetails).  I figured that out by comparing the relationships in the sample you linked.

    I have decided to use the correlated sub forms.  I am working on that now and when I come to my next block I will let you know.  Thanks!

    Wednesday, March 9, 2016 8:56 PM
  • Question:  The LocationID (PK) in the Locations table is creating a 1 to 1 with the LocationID (FK) in the OrderDetails table.  Is that correct.
    No.  The relationship type should one-to-many from Locations to OrderDetails.  You might possibly have used an autonumber as the LocationID in OrderDetails, or otherwise indexed the column uniquely.  It should be a long integer number data type, and indexed non-uniquely (duplicates allowed).

    You might have been confused by my saying that the primary key is a composite one of the two foreign key columns LocationID and ProductID.  This means that the two columns in combination make up the primary key of the table.


    Ken Sheridan, Stafford, England

    Wednesday, March 9, 2016 8:57 PM
  • That was very helpful, I did have autonumber in the LocationID in OrderDetails...so I fixed that and looks good!
    Wednesday, March 9, 2016 9:11 PM
  • Hi jpd2,

    If your original issue has been resolved, I would suggest you mark the helpful reply as answer to close this thread. If you have new issues, please feel free to post new threads in this forum.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, March 10, 2016 9:23 AM
  • I think I am now good with my table setup and relationships.  I have carefully reviewed the CorrelatedSubs link you sent above, and now I am stuck in creating the subforms/querys with this extra table Locations that is not in your example.

    I am going to start a new thread "Creating Correlated Sub Forms" and hopefully, Ken, you will follow me there.  If you don't, I completely understand as I have taken a lot of your time already.  I am very new to these type of discussions, but you have gone above and beyond!  Thank you for starting me on my way to completion and hope to see you in the new thread!

    -Jennifer

    Thursday, March 10, 2016 2:21 PM