locked
Append records from related tables RRS feed

  • Question

  • Running Access 2007.

    I have three tables:

     

    tblEstimate

         EstimateID (a unique number I build)

         Other misc data fields

     

    tblEstimateAssembly

         EstimateAssID (AutoNumber)

         EstimatePK (relates to tblEstimate)

         Other misc data fields

     

    tblEstimateAssemblyItem

         EstimateAssItemID (AutoNumber)

         EstimateAssPK (relates to tblEstimateAssembly)

         Other misc data fields

     

    Assembly is linked to the primary key in Estimate, and Assembly Items is linked to Assembly, both with one-to-many relationships.

     

    I want to create a copy of all the records for an Estimate and append them to those same tables.  (The user needs to be able to create a copy of an existing Estimate for a new job.)   I’m performing this from a popup form that has the new Estimate number, which will be used to differentiate it from the original.

     

    The usual approach would be to create append queries for each of the three tables.   However, how do I track and pass the ID fields that associate the records from table to table?  I don’t know what those values will be until the first query appends the first table.

    Thanks in advance for the help,
    Larry

    Monday, October 18, 2010 10:21 AM

Answers

  • Well, I solved the problem myself.  I needed a way to cross reference during the appending process.  Here's how I did it....

    First, I append tblEstimate directly to itself.  The new primary key is generated by me (from an unbound txt box where the new number has been created on my Copy Estimate form), and therefore and I know what it will be.  In other words, I can use that primary key for the next tbl to append.

    (NOTE: The problem is the middle table...tblEstimateAssembly.  It needed a cross reference.  So I added a permanent field to the tbl called EstimateAssIDTemp.  This field is only used for the copy process.  Once the copy is done, I simply clear it with an update query.) 

    Second, to deal with the above tblEstimateAssembly, I start by running a create tbl query.  And I place the "old" (the current record) primary key, EstimateAssID, into EstimateAssIDTemp in my temp tbl.

    Next, I use a another create tbl query for the Items for those Assemblies.  Since I know what the "old" primary key value is for tblEstimateAssembly, it store it in the field EstimateAssPKTemp in another temp tbl.

    Now I'm ready to start appending data for Assemblies and their Items.  I first run an append query to add tblTempEstimateAssembly to tblEstimateAssembly.  I know the primary key in Estimate, so linking that in the query is easy.  And I append the "old" EstimateAssID into EstimateAssIDTemp.

    Finally, I use another append query using the permanent tblEstimateAssItems and the field EstimateAssPKTemp to the Assembly Items temp tbl, tblTempEstimateAssItem.

    To clean up, I run an update query to clear the values in tblEstimateAssembly / EstimateAssPKTemp.

    You don't need to run delete queries for the two temp tables because they will be overwritten the next time the make table queries are run.  But you could run a couple of delete table queries if you wanted.

    So in summary, I'm using the field EstimateAssPKTemp as an xref field.  It all works great.  Rather than code the SQL strings, I decided to leave them as query objects for maintenance reasons (I set SetWarnings = False for the user, so they never see the dialogs anyway).  Maybe there is a simpler way to pull this off, but it was what I could come up with.  If anybody have a simpler approach, I'd love to hear it for future reference.

    Larry
    Maximize Software, Inc.

    • Marked as answer by Larry Hodges Thursday, October 21, 2010 2:02 AM
    Wednesday, October 20, 2010 2:26 AM

All replies

  • Greetings,

    It sounds like the issue you are trying to get a handle on is the RDBMS (relational database management system) paradigm.  I will share my experience with RDBMS, and hopefully this will give you an idea how you can proceed with your project. 

    (Please bare with me as I get my thoughts together - I'm thinking aloud here, basically)  The one to many relationship idea is that you have a master table and a detail table.  The master table will contain one specific record for a customer, for example -- Tom smith.  The detail table will contain records on each purchase that Tom Smith makes.  Tom Smith will have a recordID that will relate the detail records to the Master table containing Tom Smith.  If you have two Tom Smiths, then the second Tom Smith will have a different recordID.  The Detail table will contain the redundant data. 

    Then you have supporting tables that will contain specific type information like a list of products, Item Color, similar to the master table.  Say Tom Smith1 orders a product item and wants it in blue.  You select the desired product item from the Products table, and then select the desired color from tblColors.  TblColors will contain 100 different colors for the given product items.  Blue happens to be color 14, say.  So you select ID 14 (or the actual color name ) from tblColors.   This value (either blue or 14) will be stored in the detail table along with the product Item (or the product item ID).   Bill Jones orders the same product item but wants it in green which say is color 20.  So you select the desired product itme and colorID 20 (or the actual color name).  These values will be stored in the Detail table. 

    When you go to print up the invoice, you will link up the Master, Detail, and support tables (the support tables are optional if you pick the actual value like bicycle nstead of product item 15, or the color name green or blue instead of the colorID - in which case you can ignore the support tables after values have been selected from them).  In any event, you would link up the Master and Detail tables and select the desired fields for your invoice.

    To insert (append) data to the master and detail tables, if you already have an existing customer, then you only want to insert (append) a new order record for Tom Smith to the detail table.  Lets say your company has a new product to add to the Products table, say skateboard.  You would insert (append) skateboard to the Products table.  Lets say you can make skateboards in a maroon color which is not listed in tblColors.  You would now insert (append) maroon to tblColors. 

    Finally, the detail table will contain all the redundant data like say everyone wants a blue bicycle.  You could have the exact same order 100 times in the Detail table, but for 100 different people like Tom Smith, Bill Jone, Tam Lo, ...  The master table will contain all the unique RecordIDs for each customer.  You would select the recordID from the Master table the same way you would select a product or color from the Products or Colors table.  I hope this gives you an idea how you could organize your project.  Here is some sample code to do this:

    DoCmd.RunSql "Insert Into Details(CustID, Product, ProductColor) Select comboCust, comboProduct, comboColor"

    You would display a list of customers, products, product colors from dropdown boxes (comboboxes) on a form.   THe user makes selects from each dropdown box and then you have a submit button (on the same form).  In the submit button you would have a line of code as the sample above.

     

    Monday, October 18, 2010 4:19 PM
  • Well, I solved the problem myself.  I needed a way to cross reference during the appending process.  Here's how I did it....

    First, I append tblEstimate directly to itself.  The new primary key is generated by me (from an unbound txt box where the new number has been created on my Copy Estimate form), and therefore and I know what it will be.  In other words, I can use that primary key for the next tbl to append.

    (NOTE: The problem is the middle table...tblEstimateAssembly.  It needed a cross reference.  So I added a permanent field to the tbl called EstimateAssIDTemp.  This field is only used for the copy process.  Once the copy is done, I simply clear it with an update query.) 

    Second, to deal with the above tblEstimateAssembly, I start by running a create tbl query.  And I place the "old" (the current record) primary key, EstimateAssID, into EstimateAssIDTemp in my temp tbl.

    Next, I use a another create tbl query for the Items for those Assemblies.  Since I know what the "old" primary key value is for tblEstimateAssembly, it store it in the field EstimateAssPKTemp in another temp tbl.

    Now I'm ready to start appending data for Assemblies and their Items.  I first run an append query to add tblTempEstimateAssembly to tblEstimateAssembly.  I know the primary key in Estimate, so linking that in the query is easy.  And I append the "old" EstimateAssID into EstimateAssIDTemp.

    Finally, I use another append query using the permanent tblEstimateAssItems and the field EstimateAssPKTemp to the Assembly Items temp tbl, tblTempEstimateAssItem.

    To clean up, I run an update query to clear the values in tblEstimateAssembly / EstimateAssPKTemp.

    You don't need to run delete queries for the two temp tables because they will be overwritten the next time the make table queries are run.  But you could run a couple of delete table queries if you wanted.

    So in summary, I'm using the field EstimateAssPKTemp as an xref field.  It all works great.  Rather than code the SQL strings, I decided to leave them as query objects for maintenance reasons (I set SetWarnings = False for the user, so they never see the dialogs anyway).  Maybe there is a simpler way to pull this off, but it was what I could come up with.  If anybody have a simpler approach, I'd love to hear it for future reference.

    Larry
    Maximize Software, Inc.

    • Marked as answer by Larry Hodges Thursday, October 21, 2010 2:02 AM
    Wednesday, October 20, 2010 2:26 AM