Data Entry Query... Primary or Foreign key field?

[SirStevie3]

I have a form that is used in datasheet mode. It is based on a query that pulls fields from table A that has a foreign key (pointing to table B). I'm using this form as a way to quickly copy and paste data from an excel spreadsheet and get it into the database.

The problem is for a specific field. The foreign key (pointing to table B). If the new data in the foreign key field doesn't match an existing primary key already in table B, I get an error.

So I switched the query to include the primary key from table B instead of the foreign key that points to table B. And now I get an error if the data going into that field is a duplicate of an already existing primary key (obviously we cant have duplicate primary keys).

How can I paste this data so that I'm filling in the foreign key in table A, but also creating a new primary key in table B if needed?

sorry if I didn't use the correct terms... learning as I go here.

Thanks in advance!

 

[CJ_London]

not by copy and pasting, for sure. however you need to expand on what you are copying - is it all rows from the spreadsheet? just some?

Perhaps provide some example before and after data

The normal process for something like this is to link to the excel spreadsheet and then write an appropriate query to append the data to the required table

 

[SirStevie3]

IT would be all the rows except for the top row which just holds the column titles.

I was afraid an append query would be the answer. Ok, I'll have to do some research and learn about them and how to implement them.

Thanks for the suggestion CJ! Much appreciated.

 

[SirStevie3]

Actually I think my issue is more about key violations than anything else.

My research on append queries leads me to believe my "foreign key"/"primary key from another table" issue would still be present with an append query.

When I use this.... sometimes I get this.
foreign key.... an error saying there is no matching primary key.
primary key..... an error saying duplicate primary keys are not allowed.

I just don't understand how to get around this.

I wish I could have a FK/PK hybrid field where it acts as a foreign key, but then if there is no matching PK, then it would create that new PK. does that exist?

 

[The_Doc_Man]

The problem is that a joined query doesn't always update so well regardless of the data source. There is no hybrid key field because you have to statically declare PKs in the tabldef for the parent table and FKs are statically declared in the relation definitions between the parent and child. The static nature of those definitions are why you do not have a key that is both flavors by itself.

The reason you get what errors you get using a JOIN type of query: Access will attempt to perform the INSERT INTO operation monolithically (also called an "atomic" transaction). But here's the two "gotcha" cases.

1. If at the time of the INSERT there is no parent record already in the table to match the data for the child table, you would have to split the operation to assure that the parent record existed first - but that is an atomic transaction and you can't do that.

2. If at the time of the INSERT the parent record already exists, Access was going to try to insert something in the parent table as a part of an atomic transaction, but doing so is a key violation (duplicate PK).

You really cannot expect a single JOIN query with a ONE/MANY relationship between the component tables to support an atomic transaction because you (a) have requirements on the order of execution of the parts and (b) sometimes have to skip a part.

 

[SirStevie3]

Thanks for explaining Doc! I'll read that one a few times and hope I absorb some of your expertise!

I'm still researching append queries, but I have some ideas on how i'll work this out.

I'm hoping I can append things in a certain order to avoid these key violation errors.

Thanks again for your guys' help! much appreciated!