Appending a table

[Paul Axelrod]

I have a table with seven fields:
Date, CostCenter, Project-Comp#, ResheduleDate, Comments, DateIn,
DateOut

CostCenter and Project-Comp# are repeated many times in the table, but
the combination of CostCenter;Project-Comp# is unique to each record.
The table needs to be updated weekly from a text field download from a
main frame. Records can hang around for weeks, or even months and
will continually reappear in the periodic download until they are
completed as noted in the DateOut field.
My question is two fold:
1. How can I append the table such that records that already in the
table are not duplicated, or overwritten (the Comments field is a memo
field into which comments about the record are entered).
2. How can I remove fields with a date in the DateOut field, and move
them to a table designated to store historical data.

Many thanks for any and all suggestions.
Paul

 

[Jeff Boyce]

Paul

Some thoughts for your consideration:

"Date" is a reserved word -- using it as a field name will confuse Access

and cause you headaches.

Are you saying that the combination of [CostCenter] and [Project-Comp#] is

your key? Does this table have a primary key?

I don't understand if you are trying to add new records from the mainframe

download, or update your existing records -- if the CostCenter/Project-Comp#
unique constraint holds, you couldn't add another one, right?

If the only thing that is changing from download to download is the

Comments (and when finished, the DateOut), why are you replacing the entire
record, or adding a new one? This sounds like your Access table has not
been normalized sufficiently. You might want to consider a different table
design -- one that records a single row for the "facts" that don't change (a
"parent" table), and a second table for whatever it is you are downloading
and "updating/appending" (a "child" table). This second table is related to
the first with a "foreign key", which is a "copy" of the primary key in your
"parent" table. Now the question of what determines a unique row in your
parent table comes back into play.

Any time you start talking about "removing" a row (or rows) to a

historical/archive/... table, you're creating more work for yourself.
Another approach is to come up with a way to treat a row as "historical",
even while leaving it in the same table. From what you've described, any
row with a DateOut is, by definition, historical. Why not leave it there?

Good luck!

Jeff Boyce
<Access MVP>

 

[Nikos Yannacopoulos]

Paul,

1.In your table design, make fields CostCenter and Project-
Comp# the primary key (select both and press the button
with the key on the toolbar). This will prevent your
append query for afdding records with combination of the
two fields that already exist (you'll get a
message "Access could't append XX records due to key
violations).
2.Make another append query, source the current table,
destination the historical data table, filter on DateOut:
Is not null... and a Delete query on the first table, same
filter. Run the queries in sequence (in that order!).

HTH,
Nikos

 

[Paul Axelrod]

Thanks to you both for your suggestions.

Nikos, you suggested solution did the job for me, and once directed
properly, I was able to get far more sophisticated in what we were
able to do.
Jeff, I also took your suggestion and changed the field name to not
read Date. I searched Access Help for a reserved names list, and the
VB Help and couldnt find anything there. How can I acquire a list of
reserved names, to avoid potential future problems.

 

[Jeff Boyce]

Paul

Great question! (and I'm scratching my head for an answer <g>). Have you
checked Google.com with "reserved word" as a topic under the MS Access
group?

Perhaps one of the other newsgroup readers can help?

You may want to post this as a new question, to improve the odds of someone
seeing and knowing the answer.

Good luck

Jeff Boyce
<Access MVP>

 

[Paul Axelrod]

And I have an answer: I fired up and old Windows 98/Office 97 machine
that has been retired from active use and search Help for Reserved
Words. I got a complete list.
I cant believe the same search produced nothing in Access XP.

Once again, thanks to both of you.