2 tables - one used for archive - append new data

P

PMK

I have two tables. One is used (via a form) to enter data. The other is a
backup of the data entry table. I want to be able to append any new data
that is entered into the data entry table into the backup table.

I have tried append queries but am not familiar with the syntax that allows
only new data to be transferred over.

Any help most appreciated.

PMK
 
T

tina

if the backup table is an absolute duplicate of the data table - including
the primary key values - then try using the Query Wizard to create an
Unmatched Records query that finds the records in the data table that don't
exist in the backup table. do the field comparison on the primary key field
in both tables. then convert the query into an Append query.

hth
 
P

PMK

Tina

Many thanks - the minute you mentioned unmatched query wizard I remembered
exactly how to do it.

amazing how one can miss the blatantly obvious by looking straight at it.
Many thanks once again

PMK
 
L

Larry Daugherty

One of the rules for relational databases is that duplicate data not
be stored in the database.

You no doubt believe that you are achieving some objective by having
the data duplicated. It's probably a valid objective, just don't try
to achieve it as you are now doing.

Better to post back describing the objective you want to achieve and
ask for suggestions as to how to achieve it.

HTH
 
P

PMK

Larry Daugherty said:
One of the rules for relational databases is that duplicate data not
be stored in the database.
Click to expand...

Thanks - I guessed that's what it was for :D
You no doubt believe that you are achieving some objective by having
the data duplicated. It's probably a valid objective, just don't try
to achieve it as you are now doing.
Click to expand...

The objective I am trying to achieve is that myself and my colleagues get
bonus payments depending on how much income we accrue during the week. The
problem is that our MD deletes certain records thus lowering the amounts
recorded. All I was after was just a simple backup table to allow me to
repopulate the data once he has deleted them - ensuring that we get closer to
the bonus. If you know of an easier method of getting our to not delete the
recods - much appreciated ( dont suggest that I assign allow deletions to
"No" - tried that and the MD requested that I reset it to what it was)
Better to post back describing the objective you want to achieve and
ask for suggestions as to how to achieve it.
Click to expand...

See problem in middle paragraph

Thanks in any case

PMK
 
T

tina

i don't know if MD is a doctor or not, but presumably he's your boss - or
someone in a position of authority in your workplace. sounds like a problem
in interpersonal relationships between him and and you and your colleagues,
if he knows that he's costing you the bonus(es) you've earned but deletes
records anyway. or perhaps it's an issue of poor data design, i.e. he has a
solid business reason for deleting certain records, and you and your
colleagues have a solid business reason for needing them to remain in the
table(s) - but the db was not designed properly to support both business
needs.

if there is any possibility of integrity issues arising from relying on
duplicated data (such as concerns that some data in the "duplicates" table
is bogus - entered manually rather than being copied from the "live data"
table), then you might want to consider using an audit trail to provide
"proof of deletion". it would be fake-able, but not so easily. check out MVP
Allen Browne's audit trail solution at http://allenbrowne.com/AppAudit.html
while you're in the neighborhood, Allen has lots of tips and other Access
help, see http://allenbrowne.com/tips.html

hth
 
Top