I need to violate the primary key

[Paul Axelrod]

I am a relatively new user with a previously unanticipated
requirement.
I have a simple, one table DB using Access 97 currently. My table is
just for storing data from a mainframe textfile download for the
purpose of some minor data editing and major report generation.
The table has a number of fields, a Project-UnitNo field, OrderNo,
DelivTicket
and a SchedShipDate. These come from the mainframe and the
SchedShipDate can sometimes be edited directly on the mainframe. The
Access table also has a FirstReScheDate, SecondReScheDate and each of
these have corresponding reason fields, Comments1 and Comments2 whose
values are selected from Combo box lists.
The two Resched fields and Comments fields do not come from the
mainframe.

Here is my difficulty:
To preclude overwriting existing records in the Access table I have
designated three primary key fields, OrderNo, Project-UnitNo and
DelivTicket, since any two of these can be duplicated many times with
unique values of the third (the combination of OrderNo and
Project-UnitNo can have a large number of unique values of
DelivTicket). I have tried to not overwrite any pre-existing records
because I need to generate reports on rescheduling activities.
I now discover I am not capturing any rescheduling that is done
directly on the mainframe. Because of my choices in Key fields, a
mainframe reschedule order is not accepted by Access.
The question is then, how do I examine each record, determine if an
existing Access record has had the SchedShipDate field modified on
the mainframe and thus is indicted in the text file I am using to
import into the Access table.
In importing from the text file delivered by the mainframe, it is
vital that I do not overwrite existing records, just modify the date
field that has been modified. I need to preserve the history of each
record.

Thank you for any help that can be provided.

 

[Rolls]

You've got several problems but this does not seem too unusual. You have a
flat file (denormalized) mainframe table similar to a spreadsheet with
duplicate data. Access works best by normalizing the data into several
parent:child tables. Then it appears that whatever you do with Access, you
periodically want to "replicate" date fields in the mainframe, and update
the Access date fields to agree with the changed mainframe date fields.
Correct?

You may be able to do everything you want to do with SQL in Access. Convert
your mainframe dataset to a fully normalized Access dataset through 3NF.
You'll be able to use it for queries and reporting. For the date
replication part, import your mainframe table key fields plus date fields
into a seperate table. Join that to your Access table(s), set criteria <>
(does nor equal) and update only the Access date fields for which the
corresponding mainframe fields have changed via an update query that you run
at a certain interval.