Goal:
Ultimately, I'm hoping to end up with a form where user can choose whether to update, delete, or ignore records in one table base on what's in 2nd table.
Situation:
Analyst downloads data from external system using date and time range criteria. In Access, tblExDwnld is linked to the downloaded .txt file. Fields include: Tail (aircraft registration number), Date, Time, Gtwy (code for airport where problem occurred), ATACode (identifier for mechanical system on aircraft which had problem), Description, ActionTaken, and 7 others.
The downloaded records have no unique field. There really is no combination of fields that could be used as unique index for every situation, unless we could use all 14 fields. Examples: The same tail number could have 2 simultaneous problems on the same system at the same time and location (I.e. radio 1 won’t transmit, and radio 2 has static).
Initially, data is appended from tblExDwnld to tblExData using date and time range criteria to prevent duplication. tblExData has additional fields which analysts must determine and update (Type of problem, summary of history, in-work projects for that system, etc.)
10 minutes later the analyst runs the download again for same date/time range as before to see if new records have been added or existing records have been updated. Possible results include: all records same; same records but one or more fields have been updated/revised; one or more records have been voided and therefore don’t show up in the download. At this point tblExData needs to be updated to reflect current values in tblExDwnld, but without loosing values entered into additional fields in tblExData.
Analyst needs ability to compare latest downloaded records (in linked tblExDwnld) to the records in tblExData, And have control of whether each record in tblExData is updated, deleted, or ignored.
I'm thinking of something like a pop-up that displays the matching (or closely matching) records from each table, and user selects whether to update or ignore. The next record pops up, and so on. And also, records in download file that have no closely matching records pop-up giving user opportunity to delete.
Any help would be greatly appreciated!!!!
Ultimately, I'm hoping to end up with a form where user can choose whether to update, delete, or ignore records in one table base on what's in 2nd table.
Situation:
Analyst downloads data from external system using date and time range criteria. In Access, tblExDwnld is linked to the downloaded .txt file. Fields include: Tail (aircraft registration number), Date, Time, Gtwy (code for airport where problem occurred), ATACode (identifier for mechanical system on aircraft which had problem), Description, ActionTaken, and 7 others.
The downloaded records have no unique field. There really is no combination of fields that could be used as unique index for every situation, unless we could use all 14 fields. Examples: The same tail number could have 2 simultaneous problems on the same system at the same time and location (I.e. radio 1 won’t transmit, and radio 2 has static).
Initially, data is appended from tblExDwnld to tblExData using date and time range criteria to prevent duplication. tblExData has additional fields which analysts must determine and update (Type of problem, summary of history, in-work projects for that system, etc.)
10 minutes later the analyst runs the download again for same date/time range as before to see if new records have been added or existing records have been updated. Possible results include: all records same; same records but one or more fields have been updated/revised; one or more records have been voided and therefore don’t show up in the download. At this point tblExData needs to be updated to reflect current values in tblExDwnld, but without loosing values entered into additional fields in tblExData.
Analyst needs ability to compare latest downloaded records (in linked tblExDwnld) to the records in tblExData, And have control of whether each record in tblExData is updated, deleted, or ignored.
I'm thinking of something like a pop-up that displays the matching (or closely matching) records from each table, and user selects whether to update or ignore. The next record pops up, and so on. And also, records in download file that have no closely matching records pop-up giving user opportunity to delete.
Any help would be greatly appreciated!!!!