Changing a record as it is imported into a table

[jmar93]

Help,

I have an access 2007 database that imports data from an sql database, one of the fields can contain multible inputs. One of the is "Spec\Met\O EM", when it is added to table in my database I would like it to change to "Metro". How do I do this?

thanks,
Jeff

[Stewart Ross]

I have an access 2007 database that imports data from an sql database, one of the fields can contain multible inputs. One of the is "Spec\Met\O EM", when it is added to table in my database I would like it to change to "Metro". How do I do this?

If you are importing a table from elsewhere I would leave the contents of this field as it is. Should you want to have the value 'Metro' returned whatever the contents of the original field then create a new query based on your table and add a calculated field in your query which simply returns the value 'Metro'.

If you wish to return 'Metro' in place of the abbreviation 'Met' in your source table then add a small lookup table to your database. The lookup table only needs two fields - the abbreviation and a full name field. Create entries for each of the abbreviations in use. Add this lookup table to a query along with the imported table, joining the two together on the abbreviated field. That way you can include your explanation without changing the original data in any way.

-Stewart

[jmar93]

If you are importing a table from elsewhere I would leave the contents of this field as it is. Should you want to have the value 'Metro' returned whatever the contents of the original field then create a new query based on your table and add a calculated field in your query which simply returns the value 'Metro'.

If you wish to return 'Metro' in place of the abbreviation 'Met' in your source table then add a small lookup table to your database. The lookup table only needs two fields - the abbreviation and a full name field. Create entries for each of the abbreviations in use. Add this lookup table to a query along with the imported table, joining the two together on the abbreviated field. That way you can include your explanation without changing the original data in any way.

-Stewart

Hi Stewart,

Thanks for your reply, but unfortunitly I am brand new to Access and your answer is over my head. The reason I am trying to replace "Spec\Met\O em" is because it is to large to be displayed in the forms and report I will be using.

thanks,
Jeff

[Stewart Ross]

Hi Stewart,

Thanks for your reply, but unfortunitly I am brand new to Access and your answer is over my head. The reason I am trying to replace "Spec\Met\O em" is because it is to large to be displayed in the forms and report I will be using.

thanks,
Jeff

Hi again Jeff. Two things: firstly, try to keep imported data in its original form, so that you can guarantee that nothing has been lost in the import process. Second, it is good practice to base forms etc not directly on underlying tables but on views of the data - Access queries, or in other databases SQL Views which provide sorted or user-organised presentation of the data. There is much more scope and flexibility in using Access queries, because you can specify the sort order, add calculated fields - new virtual fields based upon existing fields - and do things which add to the existing data without changing it. If you are new to Access then I guess you are new to database design, an aspect of all work on databases which comes before any live data is prepared or transferred.

What I referred to in my reply to your question was the following: if you have a single field value that is always the same in your original data - and I am not sure why this would be so at all - you can include that value in an Access query (to make sure you do not lose any existing information by excising it) but substitute in a form or report a new calculated field - a virtual field if you like - returning the value "Metro". In a blank column of the Access query editor you could add a field like this to do so:

NewSource: "Metro"

If, on the other hand, different values were returned - OEM on some rows, Met on others - you would use the lookup table to provide a one to many relationship between the imported table and the meaning of these cryptic abbreviations. The many side of the relationship in these circumstances would be your imported table, and the one side the lookup table.

-Stewart

[jmar93]

Hi again Jeff. Two things: firstly, try to keep imported data in its original form, so that you can guarantee that nothing has been lost in the import process. Second, it is good practice to base forms etc not directly on underlying tables but on views of the data - Access queries, or in other databases SQL Views which provide sorted or user-organised presentation of the data. There is much more scope and flexibility in using Access queries, because you can specify the sort order, add calculated fields - new virtual fields based upon existing fields - and do things which add to the existing data without changing it. If you are new to Access then I guess you are new to database design, an aspect of all work on databases which comes before any live data is prepared or transferred.

What I referred to in my reply to your question was the following: if you have a single field value that is always the same in your original data - and I am not sure why this would be so at all - you can include that value in an Access query (to make sure you do not lose any existing information by excising it) but substitute in a form or report a new calculated field - a virtual field if you like - returning the value "Metro". In a blank column of the Access query editor you could add a field like this to do so:

NewSource: "Metro"

If, on the other hand, different values were returned - OEM on some rows, Met on others - you would use the lookup table to provide a one to many relationship between the imported table and the meaning of these cryptic abbreviations. The many side of the relationship in these circumstances would be your imported table, and the one side the lookup table.

-Stewart

Hi Stewart,

My Access database is linked to a view of a SQL database so I talked to the IT manager and he changed the view to return "metro" whenever it encounters spec/met/oem, so that solved my problem. I really a appreciate your help as I want to learn how to do it myself.

thanks,
Jeff