Update table with a SQL case statement?

[Webtechie]

Hello,

I have a table myTable that needs to be updated.

mytable

id name payment savings checking credit card
1 John Doe DB x


When there is a value in the savings or checking field, then I need to put
"DB" in the payment field.

However,

When there is a value in the credit card field, then I need to put "CD" in
the payment field.

I am thinking I need to do:

select id, name, payment =

case
when len(savings) = 0 then "DB"
when len(chekcing) > 0 then "DB"
when len(creditcard) > 0 then "CD"
end Case

from myTemp.

At least that is what I would do in SQL Server. How do I do this in Access?
I am doing this via VBA which is why I posted to the programming forum. I
would run

docmd.runSQL strSQL

Thanks.

Tony

 

[Jerry Whittle]

The basic problem is that you need 3 tables.

Why? What happen to your case statement, queries, forms, and reports when
you need to add another account, say MoneyMarket? You'll have to go through
all of them to fix the problems.

Here's something like you might need:

myTable has info about the people such as:
MT_id name
1 John Doe

myPaymentTable about payments

PT_ID AccountType PaymentType
1 Saving DB
2 Checking DB
3 Credit Card CD

Then you need to join these together in a third table as there can be a
Many-Many relationships with a person having more than one account and an
account held by more than one person.

myLinkingTable

Link_id MT_id PT_ID
1 1 1
2 1 2
3 1 3

By joining all there tables together in a query, you could see what accounts
and payments John Doe has. No need to update the Payment field as it's
already linked to the account type.