Help with update query in Access

[ChrisCalvert]

In a table in Microsoft Access I have ten fields, each of which contain a number. This relates to an account type. I need to either make a new filed, or update these existing fields to display the text description of these account types instead. That is, I need to change Acc1Typ...Acc10Typ to read "Checking, Savings, Savings, Credit Card" instead of "1,2,2,3". That, or I need to have another field that contains the text, based on the column that contains the numbers. This data is imported new each day. I could run an update query or something, but I cannot figure out the syntax that would do this. Any help is appreciated.

 

[THoey]

Chris, I saw you had another thread on this and someone mentioned "Normalizing" your tables. I agree with them, but I think I also see your problem. The data brought in each day has the 1,2,2,3 data in it, right?

I am going way out on a limb here, but I guess you have a table that has "customer" info in it. I "think" you also have this other table like Accounts, that has the ten fields that get loaded daily. If that is true, I would suggest that you have the following tables:

tblCustomers
CustomerID <-- Primary Key
LName
FName
...

tblAccountTypes
AccountTypeID <-- Primary Key (This is your 1,2,3,4)
AccountDesc (This is Checking, Savings, ...

tblAccounts
CustomerID <-- Foreign Key to Customer table
AccountTypeID

Based on this, you can load the daily data into the tblAccounts table as it comes from your datafile (1,2,2,3 - four different records) and be able to run queries, pull reports, etc and show Checking, Savings, etc.

If you get one record with for each customer with the account types all on that record, you may need a temporary table to initially import into, and then convert that one record in the temp table to multiple records in the tblAccounts table.

This is what they were trying to say in your other thread. Let me know if this helps...
Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[ChrisCalvert]

Actually, this is part of a database that manages new account welcome letters. The table 'Welcome' is the only one that relates to this. On advise from yesterday I have a table 'TextDescriptions' that contains two fields [ID] and [Desc]. There is not a seperate customer table, as this data is not finalized, but is just auto-response type stuff from the FTP. It's not much more than a means to manage a mail merge. I actually use the table 'Welcome' and run a make table query to take all records where the current date (or a requested date) can have the letters printed for that date. It created a table 'PrintLetters', which is what the mail merge points to. If I am missing something and this relational aspect, I may be too new at this to grasp the issue. I am thinking, though, that the easiest way to do this could be just to
a) have the make table query create a new field in the 'PrintLetters' table.
b) Use an update query, just after the import each day, to change the AccXTyp fields to Textdescriptions.Desc where AccXtype=TextDescriptions.ID.

I like B) but I am not familliar enough with SQL to accomplish it.
Thanks for your help. Hopefully I am not making this way more difficult than it should be.