Make Table Query?

[Guest]

This will be difficult to explain and if it is completly confusing for anyone
to answer, I understand.

The database I'm trying to design gets it's source data from another system
and can be updated as often as I wish. It is in read-only format, so users
cannot change any of the source data.

What I would like however is for users to be able to add additional data to
a particular record and save that data somewhere in the database. The
confusing point to me is, because the source data will be updated, how can I
keep any additional data that is added? There is a unique number for each
record, like a 'row id', so I was thinking somehow I could use that as the
link. But how??

Sometimes when the source data is refreshed, the row id that was in the
previous version of the data is also on the refreshed version and sometimes
not. How can I enable this so if a user adds data to a record, that when the
source data is refresh, their additional data is not compromised?

Thanks in advance.

 

[John Vinson]

This will be difficult to explain and if it is completly confusing for anyone
to answer, I understand.

The database I'm trying to design gets it's source data from another system
and can be updated as often as I wish. It is in read-only format, so users
cannot change any of the source data.

What I would like however is for users to be able to add additional data to
a particular record and save that data somewhere in the database. The
confusing point to me is, because the source data will be updated, how can I
keep any additional data that is added? There is a unique number for each
record, like a 'row id', so I was thinking somehow I could use that as the
link. But how??

Sometimes when the source data is refreshed, the row id that was in the
previous version of the data is also on the refreshed version and sometimes
not. How can I enable this so if a user adds data to a record, that when the
source data is refresh, their additional data is not compromised?

I would suggest, instead of a MakeTable query (which will create a new
table containing only the newly linked data) or even an Append query
(which will add new records to your local table), that you use an
Update query.

If you can link to the read-only table, do so; if not, import it to a
local table. Create a Query *joining* it to your "live" data table by
the unique ID. Make this query an Update query, and update each field
in the "live" table to the corresponding field in the "source" table;
if you don't update the additional fields, any data in them will
remain intact.

What do you want to do if the Source table contains FieldX, and the
user has edited FieldX? Leave the user's value alone, overwrite it
with the source table FieldX, or what?

John W. Vinson[MVP]

 

[Guest]

The users will not be able to edit FieldX from the data source. The only
fields they can edit are ones that I create in the linked "live" table.

Your solution sounds like it just might work.

Thank you.