Append table (2003 on XP)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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 for instructions.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Thanks Hans,

Here is a zipped file.

Kevin

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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(“C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls”) = True Then

DoCmd.SetWarnings False

DoCmd.TransferSpreadsheet acImport, , “Pricing_Detail_EB_Export”, “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Detail EB Export.xls”, True
DoCmd.TransferSpreadsheet acImport, , “Pricing_Export”, “C:Documents 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 “C:Documents and SettingsmcneilkmMy DocumentsEBPricingFilesPricing Export.xls”
Kill “C:Documents 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?

Reply | Quote

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

Reply | Quote

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

Reply | Quote