If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Make Table Query?
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. |
#2
|
|||
|
|||
On Wed, 6 Jul 2005 13:49:12 -0700, "FrankTimJr"
wrote: 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] |
#3
|
|||
|
|||
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. "John Vinson" wrote: On Wed, 6 Jul 2005 13:49:12 -0700, "FrankTimJr" wrote: 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] |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Alert Message for a Make Table Query | Nyla K | General Discussion | 3 | April 20th, 2005 10:40 PM |
can I change text field size in output table of make table query? | PN_Samm | Running & Setting Up Queries | 2 | December 22nd, 2004 05:49 PM |
Make Table Query Question | Hanif Merali | Running & Setting Up Queries | 1 | November 13th, 2004 03:40 AM |
Make Table query gets ODBC Error | Robin Hughes | Running & Setting Up Queries | 1 | August 30th, 2004 03:02 PM |
Adding records from another table when doing a make table query | Gilkesy | Running & Setting Up Queries | 1 | July 9th, 2004 05:19 PM |