Append Query & Linked Tables

  • Thread starter Thread starter Karl Burrows
  • Start date Start date
K

Karl Burrows

I have an append query that worked great pulling in data we are converting
from another source. As the fields in our database increased, I split it
into some linked tables. Now when I run the append query, I get key
violations because of the PK fields to the linked tables.

Is there a way around this to get the append query to run? I have to do
about 15 more of these and I can't figure out how to make it run correctly.

Thanks!
 
Karl

Do you need to do anything? If I were converting data and extracting, say,
Country from an address, after a few I would expect to be running across
duplicate Countries. If I'm building a lookup table of Country, I'd expect
to get key violation messages -- that's just telling me Access won't add
England again after it's already been added.
 
I have not broken those lookup tables out yet, so that is not the cause.
There are primary keys to other tables with other field values not in the
append query that are keeping it from running. My understanding is any
tables with linked tables with PK's can not run an append query. I am
either trying to confirm that or figure out how to work around that.

As I stated before, the append ran before I linked these new tables. Thanks!

Karl

Do you need to do anything? If I were converting data and extracting, say,
Country from an address, after a few I would expect to be running across
duplicate Countries. If I'm building a lookup table of Country, I'd expect
to get key violation messages -- that's just telling me Access won't add
England again after it's already been added.
 
Karl

I guess I don't see the whole picture yet.

When I receive a "key violation" error message, it usually means that an
append query is trying to append a record with the same key as an existing
record.

Can you do a little test and create a dummy (new) record that you can
confirm does NOT duplicate a Primary Key value, and try appending that?
 
The table has fields 1, 2, 3, 4, etc., then two fields labeled tblARC_ID and
tblMap_ID (that are used to link data in these two other tables. They are
indexed with no dups. The relationship between this main table and the two
linked tables is then created in the query that I used to populate a form
that contains all of the combined tables information. Basically the ID
field duplicates the autonumber and is matched to the ID field in each of
the two linked tables.

If I remove the two fields in the table I am importing to that are the
indexed to the linked tables, the query runs perfectly. There are no values
in the append query that would add data to a key field.

Thanks!

Karl

I guess I don't see the whole picture yet.

When I receive a "key violation" error message, it usually means that an
append query is trying to append a record with the same key as an existing
record.

Can you do a little test and create a dummy (new) record that you can
confirm does NOT duplicate a Primary Key value, and try appending that?
 
Karl

Are you saying that you are using autonumber fields and trying to append to
them?

Could you post a simple description of the table structure? E.g.:

tblStudent
StudentID (an autonumber - Primary Key)
FirstName
...

tblClass
ClassID (an autonumber - Primary Key)
ClassTitle
...

trelEnrollment (a junction table)
StudentID (a long int - foreign key to tblStudent)
ClassID (a long int - foreign key to tblClass)
...

This would help me get a better picture of what you're trying to do.
 
No, I am not trying to append an autonumber field. I was just saying the
fields that create the relationship between the other tables uses the
autonumber field to create the relationship. The append works without the
ID fields and automatically assigns the autonumber to the new records.

Karl

Are you saying that you are using autonumber fields and trying to append to
them?

Could you post a simple description of the table structure? E.g.:

tblStudent
StudentID (an autonumber - Primary Key)
FirstName
...

tblClass
ClassID (an autonumber - Primary Key)
ClassTitle
...

trelEnrollment (a junction table)
StudentID (a long int - foreign key to tblStudent)
ClassID (a long int - foreign key to tblClass)
...

This would help me get a better picture of what you're trying to do.
 
tblLotInformation
ID (auto number)
LotNumber
MapID
Plat
Map
Book
Page
RecordedDate
DeededDate
Builder
Section
ClosingDate
HomeownerName
HOADues
tblPlatRevisions_ID (this keeps up with any plat revisions by the county
or if lots have been changed from the original plat)
tblARC_ID (this is the architectural data like house plan, siding,
color, garage, etc.)

Both ID fields at the end are indexed, no duplicates.

tblARC
ID
Elevation
HouseColor
Garage
MBR

tblPlatRevisions
ID
PlatRev1
MapRev1
BookRev1
PageRev1

The imported data is in a table called:

tblExcelImport
ID
LotNumber
MapID
Plat
Book
Page
RecordedDate
DeededDate
Builder
Section
ClosingDate
HomeownerName
HOADues

The append query just pulls the tblExcelImport into tblLotInformation. I
have a query that populates the form with all this data where the
realtionship between ID in tblLotInformation and ID in tblPlatRevisions and
ID in tblARC to populate the form where data is input.

I have an idea...what if I create two more columns in the tblExcelImport and
just pick up the last number in the current tblLotInformation ID and just
put in the next autonumber to create the number these fields are looking
for?

No, I am not trying to append an autonumber field. I was just saying the
fields that create the relationship between the other tables uses the
autonumber field to create the relationship. The append works without the
ID fields and automatically assigns the autonumber to the new records.

Karl

Are you saying that you are using autonumber fields and trying to append to
them?

Could you post a simple description of the table structure? E.g.:

tblStudent
StudentID (an autonumber - Primary Key)
FirstName
...

tblClass
ClassID (an autonumber - Primary Key)
ClassTitle
...

trelEnrollment (a junction table)
StudentID (a long int - foreign key to tblStudent)
ClassID (a long int - foreign key to tblClass)
...

This would help me get a better picture of what you're trying to do.
 
OK, thanks. One more piece, please. If you post the SQL statement of your
append query, we'll be able to see the "from" and "to" (table structure) and
the "how" (SQL).

Just to be certain, are you saying that any field with "ID" in it is an
autonumber (except, of course, the "...Plat" and "...ARC" fields in
tblLotInfo (these would have to be long integers, right?)?
 
INSERT INTO tblLotInformation ( Subdivision, LotNumber, BlockNumber,
[Section], JobCostPhase, LotWidth, LotType, MapBook, MapPage, Entity,
StreetNumber, StreetName, ActualLotClosingDate, HomeBuyersName,
HomeClosedDate, MapID, LotPrice, MarketingFee, PrelimCompDate,
LotDeededDate, CashReceived, PlatDate, County, State, Builder )
SELECT tblExcelImport.Subdivision, tblExcelImport.LotNumber,
tblExcelImport.BlockNumber, tblExcelImport.Section,
tblExcelImport.JobCostPhase, tblExcelImport.LotWidth,
tblExcelImport.LotType, tblExcelImport.MapBook, tblExcelImport.MapPage,
tblExcelImport.Entity, tblExcelImport.StreetNumber,
tblExcelImport.StreetName, tblExcelImport.ActualLotClosingDate,
tblExcelImport.HomeBuyersName, tblExcelImport.HomeClosedDate,
tblExcelImport.MapID, tblExcelImport.LotPrice, tblExcelImport.MarketingFee,
tblExcelImport.PrelimCompDate, tblExcelImport.LotDeededDate,
tblExcelImport.CashReceived, tblExcelImport.PlatDate, tblExcelImport.County,
tblExcelImport.State, tblExcelImport.Builder
FROM tblExcelImport LEFT JOIN tblLotInformation ON tblExcelImport.ID =
tblLotInformation.ID;

ID is the only autonumber field. Thanks!

OK, thanks. One more piece, please. If you post the SQL statement of your
append query, we'll be able to see the "from" and "to" (table structure) and
the "how" (SQL).

Just to be certain, are you saying that any field with "ID" in it is an
autonumber (except, of course, the "...Plat" and "...ARC" fields in
tblLotInfo (these would have to be long integers, right?)?
 
I'm guessing from the information you have provided that there is a
relationship established between tblLotInformation and these other two
tables, and referential integrity is enforced. This means you could
not add records to tblLotInformation leaving those two foreign key
fields null, because they have to have values that are found in those
two other tables. Referential integrity may be enforced by default
when you create a new relationship, but you can change this (go to
Tools--Relationships...).

If that isn't it, it may be some property of the foreign key fields.
Are they Required? Are there any Validation Rules? Is there a Default
Value?

Also, why are you joining to tblLotInformation?

Why did you split this into three tables in the first place?
 
Back
Top