Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Finding Key Violations

Discussion in 'Information Technology' started by Jeff C, Nov 2, 2005.

  1. Jeff C

    Jeff C
    Expand Collapse
    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?
     
  2. Loading...

    Similar Threads Forum Date
    Key findings of the bombing of Air India Flight 182 Commission of Inquiry‏ Hard Talk Jun 17, 2010
    The Salmon Story, Finding Home Sikh Sikhi Sikhism Jul 29, 2016
    Sikhi Guru Nanak's Methodology Of Finding Truth Sikh Sikhi Sikhism Jan 1, 2016
    Movies Ashdoc's movie review---Finding Fanny ( English version ) Theatre, Movies & Cinema Sep 15, 2014
    S Asia Sikh Girls in Afghanistan Face Difficulty in Finding Mr. Right Breaking News Jul 31, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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?

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:0A07864A-6A34-4E3E-B143-FE0FAF8DAEBB@microsoft.com...
    > 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?
     
  4. Jeff C

    Jeff C
    Expand Collapse
    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" wrote:

    > 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?
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:0A07864A-6A34-4E3E-B143-FE0FAF8DAEBB@microsoft.com...
    > > 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?

    >
    >
    >
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    news:A607723D-08AA-4671-BB98-31D1E6D86A99@microsoft.com...
    > 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" wrote:
    >
    > > 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?
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > > news:0A07864A-6A34-4E3E-B143-FE0FAF8DAEBB@microsoft.com...
    > > > 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?

    > >
    > >
    > >
     
  6. Jeff C

    Jeff C
    Expand Collapse
    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)

    "Douglas J Steele" wrote:

    > 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
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > news:A607723D-08AA-4671-BB98-31D1E6D86A99@microsoft.com...
    > > 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" wrote:
    > >
    > > > 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?
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Jeff C" <JeffC@discussions.microsoft.com> wrote in message
    > > > news:0A07864A-6A34-4E3E-B143-FE0FAF8DAEBB@microsoft.com...
    > > > > 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?
    > > >
    > > >
    > > >

    >
    >
    >
     
  7. mnature

    mnature
    Expand Collapse
    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.

    > 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.
     
  8. Jeff C

    Jeff C
    Expand Collapse
    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.

    "mnature" wrote:

    > 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.
    >
    > > 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.

    >
    >
     

Share This Page