Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Append table (2003 on XP)

    I have a problem that I just can't seem to think my way through. I am importing an excel file and appending an existing table in my database. My problem come into play when some of these records that are being imported are new and some already exist. If they are new the append is fine, if they already exist, the import should really replace the existing records in there entirety. All of the records have an ID key that CAN HAVE duplicates because they relate to a specific record in another table. Is there an easy way to append new ones and replace existing ones in the same import/append process?

    Thanks
    Kevin
    Kevin

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    You should import or link the Excel table, and create two separate queries:

    1) An append query to add records whose ID doesn't occur in the existing table.
    2) An update query to update fields in the existing table for records with a matching ID.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    Thanks Hans,
    I thought about that but here is what I thought was a problem to that solution. This import represents Pricing detail records from an outside source. These detail records relate to one record in another table. If the detail records already exist, it means that the outside source has updated their pricing information and it may have more detail records or less detail records as well as different data within the records. Maybe I am trying to think to hard through this process? If there are 10 records with a particular ID that already exists and the update query tries to handle 8 new records, what will it do with the 2 remaining records?

    Kevin
    Kevin

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    You've lost me. It's difficult to form an image of how the records are related. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    Before I do that (it contains company private info), let me try to explain the relationship. I have one table called "Pricing" which contains pricing related data at what I call the macro level. It has a unique pricing ID and specific data elements such as the name of rate schedules used to price it, specific contact info etc. The database has a related table called "Pricing Detail". This table contain pricing data related to the "Pricing " table through the unique ID. The data in this table is more along the lines of a line item detail of the "Pricing Record". There are many line item records associated with the "Pricing" table record. The data from both of these tables comes from a contractor who will send us two excel files to import. Each file contains records for these tables. My problem happens when they send me a set of files containing records that are imported on one day, but may also send me on a seperate day in the future, "Pricing Detail" data for a specificf "Pricing that was already sent but updated for a current price. This updated set of records may contain more or less "Pricing Detail" records that were previously imported for an existing Pricing. (ex:On Monday i import a file with "Pricing" record which has a pricing ID of "123". The coresponding "Pricing Detail" file has 15 records at the detail level with a pricing id of 123. On Wednesday I get another file that has new records with different pricing id's as well as updated "Pricing Detail" records with a pricing id of "123".) I need to be able to import the new records(previously never imported) as well as the updated records(ones that already exist, but may have different number of records with specific pricing id).

    I hope this helps. I would have to delete all data in database in order to send it without the company private info.

    Kevin
    Kevin

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    The database would probably much too large with the existing data. Please see the instructions in the post I referred to.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    Thanks Hans,

    Here is a zipped file.

    Kevin
    Kevin

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    Your database contains two linked tables. Obviously, we cannot open these tables, not even to look at their structure. The queries are incomprehensible too.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    Sorry Hans,

    In my haste I did not realize the linked tables would mess everything up. In any event, the attached should be able to be reviewed. Thanks for your help.
    Kevin

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    You have explained about the Pricing and Pricing Detail. What is the imported table?
    Also, I see a lot of queries based on non-existing tables.
    Please help us to help you by providing sufficient information.

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    In the on open event of a form I us ethe following:

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.Maximize
    MaximizeRestoredForm Me

    'On Error GoTo ErrorHandler


    If fIsFileDIR("Cocuments and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls") = True Then

    DoCmd.SetWarnings False

    DoCmd.TransferSpreadsheet acImport, , "Pricing_Detail_EB_Export", "Cocuments and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls", True
    DoCmd.TransferSpreadsheet acImport, , "Pricing_Export", "Cocuments and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Export.xls", True

    DoCmd.OpenQuery "qryAppendToTarPricing"
    DoCmd.OpenQuery "qryAppendToTARDetail"
    DoCmd.OpenQuery "qryAppendToPricingProgramDetail"
    DoCmd.OpenQuery "qryAppendToPricingProgramPricing"



    DoCmd.OpenQuery "qryEmailTable"

    DoCmd.OpenQuery "qryDeleteAfterImportDetail"
    DoCmd.OpenQuery "qryDeleteAfterImportPricing"




    DoCmd.SetWarnings True


    DoCmd.SendObject acSendTable, "tblEmailChangeListTable", "Text", "#GRCT Code400", , , "Pricing and Technical Report Data Added to TAR Template and Pricing Program", "This email is auto forwarded and is sent to inform you that the TAR Template Database and the Pricing Program have been loaded/Updated with the latest Data from EB through " & Date & ". If you have any problems please repond to mcneilkm@supship.navy.mil.", False


    Kill "Cocuments and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Export.xls"
    Kill "Cocuments and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls"

    Else

    MsgBox "No Pricing / TAR Data to import at this time", , "Pricing / TAR Import"

    End If

    End Sub


    The queries in the attachment that I sent should point to the "Pricing" and "Pricing Detail EB" tables. I neglected to change the queries when I zipped.

    The main idea here is taking the excel file, transferring the spreadsheet to an "Pricing_Detail_EB_Export" or "Pricing_Export" table, then using the queries to append that table to the "Pricing" or Pricing Detail EB" table. The other queries delete the data written to the "Pricing_Detail_EB_Export" or "Pricing_Export" tables after the append. Finally, I build a txt file and send as an attachment to an email listing the records imported.

    Does this help any?
    Kevin

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Append table (2003 on XP)

    Sorry, I'm too lazy to change all those queries. Perhaps someone else has the time and energy for it.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    New England (Connecticut)
    Posts
    200
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Append table (2003 on XP)

    Thanks Hans,

    I was trying to rebuild the zip with the queries changed and send them along, it really is only the first two anyway. I didn't realize the question on how to selectively import records directly or delete and reimport others was so down in the weeds. I was just having trouble thinking it through.

    Thanks anyway
    Kevin

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •