Finding Key Violations

[Guest]

On the first of the month I retrieve new data, so for example; today I am
holding August in table 2, September in table 3. Table 1 holds all
accumulated data. All three tables are identical from the design perspective.
The primary key is a control # that is indexed with no duplicates. First I
append table 2 to table 1, then delete the data in table 2, append table 3 to
table 2, delete the data in table 3 and the new data is written to table 3.

When appending table 3 to table 2, I am getting 280 key violations thus
losing 280 records. Since the primary keys are designed the same I am lost
on this one.

Anyone offer a suggestion?

 

[Douglas J Steele]

You'll need to provide a bit more information.

What is the design of the 3 tables, specifically the primary keys?
(especially are you using Autonumbers?)

How are you transferring the data from one table to another? Are you running
queries through code? If so, what's the code?

FWIW, what you're doing seems to be rather unusual. Is there a real
requirement for this?

 

[Guest]

We are a large hospital using contracted services for all our medical
devices. This provider keeps a service log database identifying every piece
of equipment with a unique control number. I download their database every
month from the WEB and pull it into the database I built. I hold the last
two months of data separately which allows me to compare the two, then in my
reports I can list how many models of each piece of equipment are being used
by each department and I have conditionally formatted the report so that any
new model of equipment that has been added the last month is highlighted. I
have to keep a special record on every piece of equipment that meets certain
criteria. The primary key is a text field 255 characters, indexed with no
duplicates. The control number used by the service provider works for this.

I am wondering if the way I have related all the tables in the database
could affect and cause this key violation? I have other tables listing all
the departments and separately all the directors. The relationships are all
one to many or many to many and normalized.

 

[Douglas J Steele]

You still haven't really provided enough information to allow anyone to
comment.

How are the other tables related? Are there relationships between them to
enforce Referential Integrity?

Are there any details about what the 280 key violations are?

I don't know whether or not it's relevant in this situation, but in my
November, 2003 "Access Answers" column in Pinnacle Publication's "Smart
Access", I show how to simultaneously update existing records and add new
records to a table when you have a second table of new data. You can
download the column (and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Guest]

Well I finally found the problem. One of the fields I wqs bringing over is a
text field [Using Dept]. This field in Table 3 is related one to many to the
table of Departments. The 280 exceptions occur becuse the entries in this
field do not match anything in the Department list. Out of 4500 records I
guess it is not bad but I need to re-think the database design I guess.

Thanks for the assist with the article, I think I'll subscribe today!

Appreciate Your Help..(by the way, I am a beginner beer brewer too)

 

[Guest]

This is not really related to your question, but I am wondering why you
delete tables? Why not just rename them with the month and year, and keep
them as archival copies? Then you don't have to keep moving the data between
tables. You would just append a particular month to your accumulated data
table, and be done. It could be that I didn't understand the nature of your
database, so I apologize if this is not pertinent.

 

[Guest]

I have sequenced the process with very simple ( I am NOT a programmer) VB
"OpenQuery" statements and using the TransferText method importing the new
data. By deleting the old data in the tables and appending/moving the data,
the table names stay the same so the queries that run the reports work
consistently. These are all attached to an "ONClick" property of a command
button. By using a series of queries with one sided joins I can identify the
new data. I import roughly 4100 records of equipment which sorted down is
roughly 800 unique models of equipment, then comparing the different data
sets I get around 100 new models that were not here last month. I can
identify how many of each is in each different department. I flag certain
models meeting specific criteria by hand which attaches a questionnaire with
14 questions I have to record answers for.