INSERT into a table from another table with composite primary key

[Pada]

I have a temporary table (temp), and its data are needed to be appended into another table (tblMain).
The table tblMain has a composite primary key including two fields, Location and PayCode.
There are multiple locations that have multiple pay codes, so they have to go together to make unique records.

For example, Location = NewYork; PayCode = 1,
Location = NewYork; PayCode = 2, and so on.
Location = Chicago; PayCode = 1
Location = Chicago, PayCode = 2 and so on.

Also, the table, tblMain has an Id field that not a primary key but it is an autonumber and increment by 1.

How can I create an append query to insert data from the temp table (no primary key) into the table, tblMain?

I have tried but it pops up error message such as violate key, violate rule, etc. Please help!


Thank you very much.
Pada

[June7]

Show your attempted query SQL statement.

What kind of dataset is tblMain holding?

Exactly what does "PayCode" signify - a pay period?

Wouldn't date also be a factor in record uniqueness?

Are Location and PayCode also foreign keys in a related dependent table? If not, then these don't really need to be primary keys, just set them as a compound index no duplicates.

[Pada]

Hi Jun7, thank you for your quick replying.
Here is my attempted query SQL statement.

Insert into tblMain(PayCode, Location, Division, UpdateDate)
Select temp.PayCode, temp.Location, temp.Division, Now() As LastUpdate
From temp LEFT JOIN tblMain ON (temp.PayCode = tblMain.PayCode) AND (temp.Location = tblMain.Location)
Where (((tblMain.PayCode) is Null) and ((tblMain.Location) is Null));

The tblMain is actually just a copy of the temp with additional fields such as update date. And the temp table is actually imported data from the external Excel file.

PayCode means the code of the payment used for payment a service.

No, the date is not used in tables for record uniqueness.

Yes, the tblMain will be used in other relationships of the database.

[Micron]

I read that and think that you shouldn't be needing to append to tblMain on a regular basis as it ought to be a lookup table for something else. It seems obvious that your issue is trying to append the same combination of location and paycode more than once. If that's not obvious to you then what you could do is append to a test table first. Then put tblMain and tblTest in a new query and look at the output. Duplicate rows should stand out. If not, you can always edit qryTest to return only fields where tblMain [location] and [paycode] fields equal those fields in tblTest. Include your autonumber field (which IMO you should have used as the pk) and then the record numbers from tblMain should be obvious.

EDIT - check your composite to see how you told it to handle Nulls. There is an option to ignore them and that could also be your problem. If you don't choose that option and IIRC it means you can't have a location with paycode as null more than once either. Or vice versa.

[June7]

INSERT means you want to add records. There would be no need for a JOIN if only want to insert new records.

If you want to accomplish INSERT of new records as well as UPDATE existing, then that is called an UPSERT and would utilize a JOIN with UPDATE action. Review https://stackoverflow.com/questions/...crosoft-access

Can still use ID field as primary/foreign key for linking to dependent table. Set Location and PayCode as compound index to avoid duplicate pairs in tblMain.

[Pada]

Hi Micron,
Thank you for replying and trying to help.
Unfortunately, it can't be a lookup table (I wish) because data in the temp table will be updated periodically and there may be new locations and new paycodes.

You have added your EDIT part, and I think that helps because I took a look at my composite and found out that those fields must not be null and duplicate should be allowed. I re-set those fields, so looks like it is working. Thanks a lot!

[Pada]

Thanks, June7. I will check the link you posted. How to set Location and PayCode as compound index?

[Micron]

Unfortunately, it can't be a lookup table

I'm thinking you don't know what a lookup table is. It provides values that you use to mine your data with queries/forms/reports. If you need to update a lookup table (append new values) that is perfectly normal. Then those new values are available wherever you need them. Not to be confused with lookup fields in tables, which are typically not a good idea.

[ArviLaanemets]

... The table tblMain has a composite primary key including two fields, Location and PayCode.
...
Also, the table, tblMain has an Id field that not a primary key but it is an autonumber and increment by 1...

The normalized structure of tblMain would be having autonumber id field as primary key, and Location [and PayCode] as Foreign key(s). And an unique index defined containing Location and PayCode.

To enter new data into tblMain from e.g. tblTempData, you can run a query to add all entries from tblTempData which don't have a matching pair of Location and PayCode in tblMain currently.
Or you run a procedure, which processes the temporary table row-wise, checks the tblMain for matching Location and PayCode, and in case not found, adds the row into tblMain.

[June7]

Should search "Access compound index" or "Access composite unique index" - plenty of guides. Here is a nice tutorial http://msaccess.erpmakers.com/Articl...t%20access.php

[Pada]

Thank you so much, June7, Micron, and ArviLaanemets. What you guys wrote make sense.
Sorry, but I take a look again closely at the data file (to be imported into table tblMain of my current database) and see this scenario:
There are many PayCodes for one Location. On the other hand, there are many Locations for one PayCode.

For this reason, I don't think I can setup composite unique INDEX.

I think I will have to create a composite Primary Key including both Location and PayCode, so that it will make unique records.
For both Location and PayCode, their properties are set to Index = yes (duplicate OK). Required = Yes.

Please let me know if I can create a composite Primary Key this way or not. Sorry again and thanks for your time.

Going further, there are queries in my database that currently have other tables connect with tblMain by Location, but now a new setup with both Location and PayCode as primary key I don't know how to deal with it. I can create a new thread about this issue if that is allowed.

Thank you!

[June7]

Again, composite index set to No Duplicates will not allow duplicate pairs. Seldom any advantage to using a composite key, in fact can be more cumbersome to deal with.

Are these 'other' tables dependent child tables? Why would they now need a compound key to link records? Location must have been unique in tblMain for link to child tables to work. Use autonumber ID in tblMain as primary key and save this value to child table(s) for linking records.

Can you provide database for analysis? Follow instructions at bottom of my post.

[Pada]

Again, composite index set to No Duplicates will not allow duplicate pairs. Seldom any advantage to using a composite key, in fact can be more cumbersome to deal with.

Are these 'other' tables dependent child tables? Why would they now need a compound key to link records? Location must have been unique in tblMain for link to child tables to work. Use autonumber ID in tblMain as primary key and save this value to child table(s) for linking records.

Can you provide database for analysis? Follow instructions at bottom of my post.

Hi June7,
Looks like I should use composite index set to No Duplicate, not setup the compound Primary Key.

No, there are no dependent child tables. Only that the table, tblMain does have a relationship with another table in the DB. Currently, this table, tblBills, has a 1-to-1 relationship Location-Location with the tblMain. Now, if the tblMain has a composite index set (Location and PayCode) then how can I create a relationship with tblBills that has only a Location field?

I am trying to narrow the database with the issue I have before posting up for analyzing.
Thank you!

[June7]

That does still sound like a parent/child dependency. I am just not sure which is which.

How can it be 1-to-1 if there are multiple records with same location in tblMain?