Re: need append query SQL help

Tech-Archive recommends: Speed Up your PC by fixing your registry

The key violation caused by:
1. You are not appending a value to the primary key value in the table
and thus the key is null, which you cannot do. The Primary Key of a
table MUST have a value.
2. You are appending records where the key value is duplicated. The
Primary Key MUST be unique.

My guess would be #1 since you are trying to append 6 records, and you
got 6 key violations.


Slez via AccessMonster.com wrote:
Okay...
The first query works properly, so I created a second query to contend with
the next level of the recordset, and when I view the records in the query, it
returns the correct records (6 total)! I thought I was on my way to success
until I tried to actually Run the query. I then got an error message stating
there were 6 "key violations". All the fields' properties in both databases
are exactly the same. Here is the SQL from this query:

INSERT INTO tblItem ( RoomNumber, ItemNumber, RoomName, ElevationReference,
ProductSummary )
SELECT Item.RoomNumber, Item.ItemNumber, Item.RoomName, Item.
ElevationReference, Item.ProductSummary
FROM Project INNER JOIN (Bid INNER JOIN Item ON (Bid.ProjectID = Item.
ProjectID) AND (Bid.BidNumber = Item.BidNumber) AND (Bid.ProjectName = Item.
ProjectName)) ON (Project.ProjectName = Bid.ProjectName) AND (Project.
ProjectID = Bid.ProjectID)
WHERE (((Project.JobNumber)="065812"));

Here's something that I am thinking may be partially becoming an issue.
"ProjectID" is a unique idendifier in the source database, but once a
JobNumber is assigned and the project is moved to the destination database,
JobNumber becomes the unique identifier. ProjectID does not exist in tblItem.
Do you think that is contributing to my issue? Or do you think my JOIN is
causing it?
Again, thanks for your continued help!

Jeff L wrote:
Yes you need to create an append query for each table you wish to
append to. You can run them individually if you want or in code. The
code would be Docmd.OpenQuery "YourQueryName"

Just to make sure I understand your reply...
To accomplish this, I would need to create one query for each table that I
[quoted text clipped - 16 lines]
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200608/1

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200608/1

.

Relevant Pages

  • Re: A deletion query
    ... The table i'm appending FROM has no primary key. ... the query uses data imported from an excel spreadsheet. ... Correct the error in your query or in your validation rules. ...
    (microsoft.public.access.queries)
  • Re: Use entire table as a record in Access?
    ... Set the critera of the append query to soemthing that will limit the ... Instead of appending the same table, how about updating to a temp table, ... that's bound to tblEmpHours. ... hours for one employee for each day of the week, ...
    (microsoft.public.access.macros)
  • Re: Database Structure Question ... Serious Help Only Pls
    ... Maybe simply appending with a query won't work... ... The only problem you might have is determining the primary key ... On your recordset object after you have updated the recordset issue a ... it, and append the parent table data, grab the PK as above and store it ...
    (comp.databases.ms-access)
  • Re: Very slow append query
    ... If you are doing the appending via code, it's possible to remove the indices ... append query, then restore the indices using the values from the table. ... >> Does tblBalances have a lot of indices in it? ...
    (microsoft.public.access.queries)
  • Re: Cant make the most simplest of Queries...
    ... the problem was that I also put the table I was appending ... When I deleted that table from the Design view, ... >> just a select query but they all come up empty. ...
    (microsoft.public.access.queries)