Update/Append Query

[trish]

Hi

Can someone please explain to me what I'm doing wrong.

I'm trying to update my table called Portfolio with information sent to me via spreadsheet. I've been able to import the data from Excel into a table called PortfolioUpdate and the data types are all the same, but when i try to run the append query it keeps coming up with the message below

Switchboard can't append all the records in the append query.

Switchboard set 0 field(s) to Null due to a type conversion failure, and it didn't add 1889 record(s) to the table due to key violations, 0 record(s) due to lock violations, and 0 record(s) due to validation rule violations.

I've checked that all the data types are the same and I've also removed/added primary keys from the PortfolioUpdate table to see if that was the problem but to no avail.

Am I even doing this right?

If anyone could help that could be great

Thanks
Trish

 

[Pat Hartman]

Append queries add records and the message is telling you that the query can't add 1889 records becaues records with those keys already exist. That means that you have probably run the append query more than once.

Are you really intending to run an update query?

 

[trish]

Thanks for the info Pat.

I don't actually know if an update query is the right thing to do.

Basically the spreadsheet that is generated monthly contains information that is already in the database and new information as well. All I want to do is add the records that are new and also if any of the information has changed in the individual fields to update that as well.

Is there another way I should be doing this?

 

[Pat Hartman]

Take a look at this post. It refers to a query that can by using a right join, BOTH append and update in the same pass.

http://www.access-programmers.co.uk...d=49061&highlight=append+AND+update+AND+query

 

[GPGeorge]

For the record, I thought it might be helpful to review the difference between APPEND queries and UPDATE queries and how using a RIGHT JOIN instead of an INNER JOIN causes an UPDATE query to accomplish both in a single pass.

In a table of say, 50 records, you have a PRIMARY KEY, which is the unique identifier for each record; it can not be duplicated. That's important to remember when you are talking about APPEND and UPDATE Queries.

As you probably know already, APPEND means: "Take all of these records in this RECORDSET (i.e. your query) and add them to the records already in table." In an APPEND, none of the existing records in the table are to be changed in any way. So, if the recordset in your query contains any records with the same PRIMARY KEY as any of the 50 existing records, Access won't add them. It isn't supposed to touch any existing record and it can't add duplicate PRIMARY KEYS.

As you also know, UPDATE means "Find the record in this table which has the same PRIMARY KEY as each record in this RECORDSET and change all of the other values in that one record to those you find in the corresponding record this RECORDSET(i.e. your query) ." In an UPDATE, new records can be added, but only if their PRIMARY KEY does not match the PRIMARY KEY for any existing records (see above). Since the INNER JOIN only returns records with matching keys, it doesn't show the new records and can't add them.

Here's where the RIGHT JOIN steps in to help out. A RIGHT JOIN basically says, "Display all of the records in the first table, whether or not there is a matching PRIMARY KEY in the second table; however, only display records in the second table if they have a PRIMARY KEY that matches a PRIMARY KEY in the first table.

The resulting RECORDSET will have all 50 records from your original table, PLUS any records in the new recordset (i.e. your excel spreadsheet) that don't have a corresponding PRIMARY KEY in that table. These are, the new records you want to add.

You could have updated the 50 existing 50 records with an UPDATE and then added the new records with a seperate APPEND. BUT, because the RIGHT JOIN causes all of the records to be returned in one recordset, the UPDATE query can add the new unmatched records at the same time it is updating the existing 50 records.

It does so if, and only if, you have a way of adding new PRIMARY KEYS to the original table for each of the new records. If you have used Autonumber as the primary key for the table, of course, that is automatic.

HTH

George

 

[Pat Hartman]

Thanks for the extra details George, welcome aboard.

 

[trish]

Hi Pat, George

Thanks for your help and explanations. The query doesn't work 100% (yet) but it has at least put me on the right path!!

Kind regards
Trish

 

[Pat Hartman]

If you need more help with it, post the SQL.

 

[trish]

This is what I currently have..

UPDATE Portfolio RIGHT JOIN LandlinkPortfolio ON Portfolio.BuildingID = LandlinkPortfolio.BuildingID SET Portfolio.BuildingID = [LandlinkPortfolio].[BuildingID], Portfolio.BSMName = [LandlinkPortfolio].[BSMName], Portfolio.SBSMName = [LandlinkPortfolio].[SBSMName], Portfolio.RMName = [LandlinkPortfolio].[RMName];

 

[Pat Hartman]

What doesn't work about it?

 

[trish]

I have 7 records that won't do what its told due to key violations

'Database didn't update 0 field(s) die to a type conversion failure, 7 record(s) due to key violations, 0 record(s) due to lock violations and 0 record(s) die to validation rule violations.'

Just wondering, regardless of whether the query runs 100%, does this message pop up anyway?

 

[Mist]

It's probably well after the event now for this contribution... I had a similar problem when appending and discovered that it was the autonumber field in both tables conflicting with each other. Removing the autonumber field from the source in the append query solved the problem.