Append query with foreign key

[pli]

Hello,

I'm trying to do a self append to copy the last record entered. However I cannot use the append because of a key error (the query does not include the tables' primary key) created by the a relationship stating a field is a foreign key. The relationship is one-many so the duplication of the foreign key is not an issue. Can anyone assist? Much thanks.

 

[jzwp22]

Welcome to the forum!

Could you please provide some more details such as the SQL text of the query that did not work as well as a list of fields (and their datatypes) and table that are pertinent to the problem?

 

[pli]

Thanks for the assist. It is most appreciated. Here is the requested SQL script:

INSERT INTO bioreactor ( CCID, dt, [Time], station, brxlot, cellday, titer, cd, viab, acd, glucose, cellbleed, perfusion, CSPR, harvest, media, base, recordcolor, notes )
SELECT DMax("CCID","cellculture") AS CCID, bioreactor.dt, bioreactor.Time, bioreactor.station, bioreactor.brxlot, bioreactor.cellday, bioreactor.titer, bioreactor.cd, bioreactor.viab, bioreactor.acd, bioreactor.glucose, bioreactor.cellbleed, bioreactor.perfusion, bioreactor.CSPR, bioreactor.harvest, bioreactor.media, bioreactor.base, bioreactor.recordcolor, bioreactor.notes
FROM bioreactor
WHERE (((bioreactor.CCID)=DMax("ccid","queprevcc")));

Where queprevcc is a second query defined as:

SELECT cellculture.CCID
FROM cellculture
WHERE (((cellculture.CCID)<>DMax("CCID","cellculture")));

The foriegn key in this table is "CCID" and the primary key is "BRXID". BRXID are defined as auto-increment 1 and both have the data type of Long. The relationship I wish to establish is a one:many (CCID:BRXID) but the script will not function if they are defined with that relationship. Access gives me a key error in that instance which makes no sense to me as I am manipulating a foriegn key (which to the best of my knowledge does not require to be unique).

 

[jzwp22]

BRXID are defined as auto-increment 1 and both have the data type of Long

If BRXID is auto-increment the datatype should be autonumber not long. Are you actually working with an Access backend or an SQL Server backend.

If BRXID is true a long integer number datatype and it is unique the append query will fail because you are not incrementing the BRXID value. The only way I know to do that is via Visual Basic for Application code.

 

[pli]

If BRXID is auto-increment the datatype should be autonumber not long. Are you actually working with an Access backend or an SQL Server backend.

I'm modeling the system with an Access backend as a proof of concept and if it gets the go ahead, it'll convert to an Oracle backend.

If BRXID is true a long integer number datatype and it is unique the append query will fail because you are not incrementing the BRXID value. The only way I know to do that is via Visual Basic for Application code.

Thats the wierdest thing though, and the part that quite confounds me. If the relationship between the two tables does not exist, the append to "bioreactor" with the previously posted SQL will both allow the append to be valid (no key error) and auto-increment the BRXID. The behavior is different if I create the relationship that defines CCID as a foreign key in the "bioreactor" table and the system errors with mention of a key error.

I'll take a second look into it and investigate further into using VBA for this. Much thanks for the help so far!

 

[jzwp22]

The behavior is different if I create the relationship that defines CCID as a foreign key in the "bioreactor" table and the system errors with mention of a key error.

I wonder if it has to do with referential integrity?

 

[pli]

I don't know what I did but it works now. The SQL query is exactly the same character for character and the relationship is defined but not an error in sight. If I figure out what I did, I'll post it here (or could just be a Microsoft thing, who knows?). Thanks for the assist!

 

[jzwp22]

Glad to hear that you got it worked out!