Access: Updating multiple rows with different values

Question

I have a table describing calls made with the company cellphones, one of the fields is callTypeId, every call has one, while other is callType, a text description of the ID. Several calls lack a text description so what I want to do is to update each record with a description matching its callTypeID.

The number of different Id's is low, 15 but some Id's are for the same type of call (5 Id's for local calls for example). There are over 15,000 records so I'd like to know what is the best way of doing so.

vemcaster · Accepted Answer · 2013-05-23 17:06:07Z

2

You can use a lookup table or the SWITCH function.

For example:

UPDATE YourTable
SET callType = SWITCH(
callTypeId = 1, 'Description 1',
callTypeId = 5, 'Local calls',  
callTypeId = 10, 'Description 10'
)
WHERE callType IS NULL;

answered May 23, 2013 at 17:06

vemcaster

3614 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Beth · Accepted Answer · 2013-05-23 16:55:00Z

1

Do you have a lookup table with the callTypeID as the PK and description as a text field? Then you can create an update query joining on the callTypeID field.

answered May 23, 2013 at 16:55

Beth

9,6171 gold badge26 silver badges44 bronze badges

4 Comments

FilipeG Over a year ago

I could create a table for that then, going to test this. Would it be something like: UPDATE calls INNERJOIN Ids ON calls.callId = Ids.callId SET callText = ids.text ?

Beth Over a year ago

well, you could use the UI instead of writing SQL and just add your table and your lookup table, drag the PK field from the lookup table to the FK field of your existing table (callTypeID) and drag the calltype field to the grid. Change the query to an update query and change the value to the description field in the lookup table.

HansUp Over a year ago

If you have a lookup table which maps callTypeID to callType, you don't need the callType field in your original table. When you need to see callType, use a query which joins the lookup table to the original using their common callTypeID as the linking field. Storing callType in the original table is redundant ... not consistent with db best practice.

FilipeG Over a year ago

A lookup table solved it, thanks! I am storing because this is a temporary table. I import the calls from a text file, clean it up, add only SOME of the fields in there to the real table and them clean up.

Collectives™ on Stack Overflow

Access: Updating multiple rows with different values

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related