Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148

    Append Query to Append records from multiple tables to 1 table

    Really struggling with this for quite sometime so would truly appreciate for any possible help.

    I am trying to Append records from 2 tables (OppDetailT and PartsT) to 1 table (ItemsT) using an Append Query which is appending 0 records. Records that I want to append from 'OppDetailsT' table to 'ItemsT' table are 'Quantity' and 'UnitCost', and the record I am trying to append from 'PartsT' table to 'ItemsT' table is 'PartID'. I have some sample data in my db that I am trying to append. Later on, my objective is to use ItemsT table as a subform in a QuoteEntryForm (MainForm) where these records will be appended based on a combobox selection. This subform will display item details (PartNo, PartDesc, Qty, Unitcost) that will be recorded for a particular QuoteID. I am not worrying much about the form/subform setup yet as my Append Query itself has some issue.

    As seen in attached image, 'ItemsT' table has one-to-many relationship with multiple tables, which are OppDetailT, PartsT, and QuoteHeaderT(for QuoteID) tables. All the FKs are a required 'yes' field in my ItemsT table. Not sure what other details I should provide here so I could post my db if needed.



    I have researched almost every possible video/forum available online regarding this, but couldn't be successful. I might be missing something really basic or my approach could be incorrect, but have really given it too much time so seeking help here. Thanks in advance for any possible help.
    Attached Thumbnails Attached Thumbnails Query.PNG  

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Records that I want to append from 'OppDetailsT' table to 'ItemsT' table are 'Quantity' and 'UnitCost',
    Quantity and UnitCost are NOT records - they are fields IN records. The same goes for PartID - it is a field, not a record.

    Are you sure you want to APPEND? Append adds new records to a table, in this case ItemsT. If you really do want to Append new records to ItemsT, then you should take ItemsT out of the query design grid. The fact that it is there may be why the query adds 0 records.

    A useful tip: Design the query first as a Select query, so you can run it to check whether it is selecting the expected number of records and the correct data. Once you have it selecting the proper records, then you can change it to an append query.

  3. #3
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much John_G. That helped me to get half way through. Select Query tip is really useful.

    There are 2 fields in my ItemsT Table where I need further help. These are required 'yes' fields:-

    1) OppDetID_FK (in ItemsT table) - I want to append records in this field, from the OppDetailT table, based on a combobox selection in MainForm(QuoteHeaderT). To do this, I have set a criteria in Query design that [OppDetailT]![OppID_FK]=[Forms]![QuoteEntryForm]![CmbOppList]. See attached Query design. I want to understand if this is correct approach?

    2) QuoteID_FK - This field in ItemsT table isn't really appended from anywhere. I want this field to GET value from the MainForm(QuoteHeaderT) for a newly generated QuoteID. In conclusion, what I really want is to GET QuoteID_FK, from QuoteID_PK generated from QuoteEntryForm for a new quote, AND at the same time APPEND records using append query to ItemsT table, for this particular QuoteID_FK. Therefore, every time a new QuoteID_PK is generated from Mainform(QuoteHeaderT), the QuoteID_FK should equals to QuoteID_PK, AND the Opportunity details (PartNo, PartDesc, Qty, UnitCost) should be appended to ItemsT as well.

    I am not sure if QuoteID_FK should be in Query design for what I want to achieve. Please see the attached Query Design with comments.

    As I mentioned in my last post, ItemsT table will be subform in the mainform I have talked above.

    I really hope I haven't confused you. If I did, please ask for specific detail and I will try to clarify, and could post my db if needed. Any advise on how to achieve (1) and (2) or anything else for my db would be REALLY appreciated. Thanks for your time.
    Attached Thumbnails Attached Thumbnails Query1 new.PNG  

  4. #4
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    If anyone could help me with this, would truly appreciate. Thinking all day and night about this now. I have just 3 months experience with Access so pardon me for any unclear description. Thank you.

    If my last post was unclear, then what I am trying to achieve is 'Append 4 fields records from 1 table to destination table, and at the same time get the 5th field (QuoteID) from a different table, where QuoteID is generated from Main Form'. I want both 'Appending records' and 'Getting QuoteID' at the same time in my destination table. Destination table is my Quote Items Table, that stored item details for the generated Quote IDs. I am able to run append query successfully with just 4 fields, but really confused about the 5th one (Quote ID).

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want the QuoteID to be data in the new record, it belongs on the Field row.

    QID: [Forms]![QuoteEntryForm]![QuoteID_PK]

    An SQL statement would be like:

    INSERT INTO ItemsT (QuoteID_FK, Item_Qty, Item_UnitCost, PartID_FK, OppDetID_FK) SELECT [Forms]![QuoteEntryForm]![QuoteID_PK] AS QID, OppDet_Qty, OppDet_UnitCost, PartID_FK, OppDetID_PK FROM OppDetailT WHERE OppDetID_PK = [Forms]![QuoteEntryForm]![CmbOppList];

    Copy/paste in SQL view then switch to Design view.

    The record on main form must be committed to table first. This will likely require code to save record if it is a new record.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    Thanks very much June7. With your help I have moved 1 step further it seems. I am not completely sure though where to enter QID: [Forms]![QuoteEntryForm]![QuoteID_PK]?
    Does it belong to my ItemsSubform?

    What I currently have in the SQL statement is this, which looks very similar to what you have provided:

    INSERT INTO ItemsT ( QuoteID_FK, Item_Qty, Item_UnitCost, PartID_FK, OppDetID_FK )
    SELECT QuoteHeaderT.QuoteID_PK, OppDetailT.OppDet_Qty, OppDetailT.OppDet_UnitCost, OppDetailT.PartID_FK, OppDetailT.OppDetID_PK
    FROM OppDetailT, QuoteHeaderT
    WHERE (((OppDetailT.OppID_FK)=[Forms]![QuoteEntryForm]![CmbOppList]));

    The only issue after running this query is that the QuoteID_FK value is not same as the one generated in Quote Entry Form (QuoteID_PK). I am running this query from a combobox in my QuoteEntryForm, after a QuoteID_PK is generated. However, the query displays some old QuoteID_FK (214), as shown in attached image, whereas the current QuoteID_PK in my QuoteEntryForm is 216.

    Your help will be truly appreciated. Thank you.
    Attached Thumbnails Attached Thumbnails ItemsT after Query1.PNG  

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    It belongs in the query per my example.

    You did not follow my example.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    Thanks for the clarification June7. I copy/pasted your SQL statement and the Query design looks as attached. QID is in the field row now. However, the query is appending 0 records to the ItemsT table. Your further help will be much appreciated. Thanks very much again.
    Attached Thumbnails Attached Thumbnails Query updated1.PNG   itemst display1.PNG  

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you actually RUN the query as opposed to displaying in datasheet? Datasheet view will not show any records for an INSERT SQL.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    sud2017 is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jul 2017
    Posts
    148
    I have run the query from combobox in my Quote Entry Form. It says 0 records appended. Sorry, my Quote Entry Form and Query were open the same time that's why its looking like datasheet view. I'd really appreciate for your further advise. Thanks very much.

  11. #11
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Look at your query design again. You have set criteria for the field [OppDetID_PK]. But because that field is the PK, the MOST the criteria will ever select is ONE record, because a PK by definition is unique.

    Should the criteria field be looking at [OppID_PK] instead?

  12. #12
    sud2017 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    148
    Thanks John_G and June7. I tried running the query again by changing the criteria to OppID_FK. Please see attach query design. With this change, the query starts appending (3) rows as intended, but then gives a key violation error (didn't add 3 record(s) to the table due to key violations). I am not sure where I am wrong now. Sorry if I am going beyond scope of my originally posted question but I would be really grateful for your help. Thanks again.
    Attached Thumbnails Attached Thumbnails Capture1.PNG  

  13. #13
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    That indicates an issue with the destination table, ItemsT. Check the indexes on that table to see if any are unique when they should not be (its PK has a unique index, obviously). The other possibility is that running the query twice with the same criteria would cause duplication in ItemsT, and a unique index in ItemsT is preventing that from happening (in other words, it's working as it should).

  14. #14
    sud2017 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    148
    Thanks again for your quick reply John_G. I verified Indexes in all of the FK fields in both tables, i.e. destination table (ItemsT) and OppDetailT where I am appending records from, and all the appending fields have NO indexes. Also, all of them are NUMBER fields, so there shouldn't be a type mismatch issue. I just deleted the entire data from ItemT table and run append query again, but no luck. In my post#6 image, itemsT was taking all the records after query but the issue was with QuoteID (old QuoteID it was). Anything else I should verify? Thanks again for your time and help. I really hope I am missing something SILLY, which can be fixed quick. I am struggling with this for over a month now.

  15. #15
    sud2017 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    148
    PS - only OppDetID_PK is Indexed(No duplicates) in OppDetailT table, and OppDetID_FK in destination table(itemsT) is not indexed.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access can't append all the records in the append query
    By fluffyvampirekitten in forum Access
    Replies: 2
    Last Post: 08-27-2015, 01:53 AM
  2. append to one table using multiple tables
    By tagteam in forum Access
    Replies: 2
    Last Post: 06-27-2015, 07:29 AM
  3. Append Query Saving Multiple Records
    By jewll in forum Queries
    Replies: 5
    Last Post: 12-13-2014, 03:54 AM
  4. Replies: 5
    Last Post: 12-12-2011, 08:08 AM
  5. Replies: 1
    Last Post: 10-06-2011, 08:37 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