Don't Append Records Again!

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Using A02 on XP OS. I have an append query that
summarizes/groups data and if data meets certain criteria,
that data will append to a table for supervisor approval.

My challenge is to allow the append query to run, tell me
that 10 records are about to be appended, click okay and
then only append those of the 10 that aren't already there
(in case the query had already been run for some or all of
the data).

How do I get the second box that says how many records
were not appended due to key lock violations? So if 2 were
already there, only 8 would have been appended.

I hope this is understandable and someone can give me some
advice. Isn't this a multiple primary key thing? I'm
playing with it but not succeeding.

Thanks in advance for any help or advice!!!
 
-----Original Message-----
Using A02 on XP OS. I have an append query that
summarizes/groups data and if data meets certain criteria,
that data will append to a table for supervisor approval.

My challenge is to allow the append query to run, tell me
that 10 records are about to be appended, click okay and
then only append those of the 10 that aren't already there
(in case the query had already been run for some or all of
the data).

How do I get the second box that says how many records
were not appended due to key lock violations? So if 2 were
already there, only 8 would have been appended.

I hope this is understandable and someone can give me some
advice. Isn't this a multiple primary key thing? I'm
playing with it but not succeeding.

Thanks in advance for any help or advice!!!


.
I don't know the layout of your table but it is
Click to expand...
definately in your primary key field (s) selection.
 
My challenge is to allow the append query to run, tell me
that 10 records are about to be appended, click okay and
then only append those of the 10 that aren't already there
(in case the query had already been run for some or all of
the data).

How do I get the second box that says how many records
were not appended due to key lock violations? So if 2 were
already there, only 8 would have been appended.
Click to expand...

As Larry suggests, if the table has a Primary Key and that value
exists in both tables, you'll get this error message and the records
will not be appended. But Access must have *some* way to know (based
on the contents of the record) that it is "already there". What
information exists in the data to be appended which would let the
computer - or a person, for that matter - decide that it is an old vs.
a new record?
 
I thought I remembered someone setting multiple primary
keys on the fields that, as a group, should not be
duplicated.

I have fields: TDate, GP, Tech, Reason, Amt, RecordCount
and AutoNum. Records could be:

9/1/02 GP5344 BWH A8 $23.55 14 153854
9/1/02 GP5344 BWH A0 $53.11 03 153854
9/1/02 GP5344 KXK A8 $43.97 01 153854
9/1/02 GP5344 BWH A8 $64.34 56 153854
9/1/02 GP5344 BWH A8 $81.57 21 153854
 
Sorry for putting this out twice but I got tab happy and
next I knew, it was sent and not finished. Oops!

I thought I remembered someone setting multiple primary
keys on the fields that, as a group, should not be
duplicated.

I have fields: TDate, GP, Tech, Reason, Amt, RecordCount
and AutoNum. Records for a run could be:

9/1/02 GP5344 BWH A8 $23.55 14 153854
9/1/02 GP5344 BWH A0 $53.11 03 153854
9/1/02 GP5344 KXK A8 $43.97 01 153854
9/1/02 GP5346 BWH B1 $64.34 56 153854
9/1/02 GP5348 BWH C0 $81.57 21 153854

I thought you could set 3-4 fields as primary keys and
then if you tried to append the same set again you would
get the 'didn't send () records due to key lock
violations' and that would be a good thing. The first 4
fields should be a unique 'set'. As you can see, the 1st
3 records are almost alike.

I am summarizing info from a couple of tables and
appending summarized data into another table for approvals.
If 2 diff techs work on the same GP, I need it, if the
same tech works on the same GP but with diff Codes, I need
it.

Am I making any more sense here? Thanks again to both of
you guys for replying and trying.
 
I thought you could set 3-4 fields as primary keys and
then if you tried to append the same set again you would
get the 'didn't send () records due to key lock
violations' and that would be a good thing. The first 4
fields should be a unique 'set'.
Click to expand...

You can; open the Table in design view and either:

- Ctrl-click the four fields so they are all selected, then click the
Key icon to create a four-field Primary Key
- Open the Indexes dialog (there's an icon that looks like lightning
hitting a datasheet); type a name for an index in the left column and
select the four fields on sequential lines in the right colum. Specify
that the index is unique.
As you can see, the 1st
3 records are almost alike.
Click to expand...

Almost doesn't cut it. As far as "unique" is concerned, they are
identical (reject the record) or there is some difference, however
slight (accept the record). The five examples you provide are not
duplicates. Do you mean them to be? If so, how can you distinguish a
duplicate record from a different record?
 
Back
Top