Add a column to a query (the value of the column needs calculation

[Dawn]

I have a table 1, information of customer, primary key custID;
Table 2 , information of accounts, primary key account number, one account
number is under one custID.
Try to link two tables to generate a query with all the fields of table
1,also to add a new field called accountType, it’s value includes “None(not
finding respective custID in table2)â€, “Aâ€(the accounttype is asset), “Lâ€
(the accounttype is Liability),â€A/L†(under the same custID there are
accounts covered accounttype Liability and Asset) .
How to write it in sql? Many thanks.

 

[John Spencer]

There doesn't seem to be a good reason to store this information in Table 1 at
all. If you can calculate the value then usually you should not store the
value. The simplest method to set the values would be to run five update
queries (see below).

The problem is if you add more Customers or more Accounts (or delete or modify
records) you would need to do this all over again.

A query that looks something like the following should allow you to calculate
the types of accounts for customers on the fly.

SELECT Aa.AcctID,
IIF(Count(AccountType)=2,"A/L",Max(AccountType)) as TheType
FROM (SELECT Distinct A.AcctID, A.AccountType
FROM AccountsTable) as Aa
GROUP BY Aa.AcctID

IF you really feel the requirement to store the information, you would
probably need update queries like the following.

UPDATE CustomerTable
SET AccountType = "None"
WHERE CustID NOT IN
(SELECT CustID FROM AccountsTable)

UPDATE CustomerTable
SET AccountType = "A"
WHERE CustID in
(SELECT CustID FROM AccountsTable WHERE AccountType="Asset")

UPDATE CustomerTable
SET AccountType = "L"
WHERE CustID in
(SELECT CustID FROM AccountsTable WHERE AccountType="Liability")

UPDATE CustomerTable
SET AccountType = "A/L"
WHERE CustID in
(SELECT CustID FROM AccountsTable WHERE AccountType="Asset")
AND AccountType = "L"

UPDATE CustomerTable
SET AccountType = "A/L"
WHERE CustID in
(SELECT CustID FROM AccountsTable WHERE AccountType="Liability")
AND AccountType = "A"

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Dawn]

John,
Thanks for your kindly reminding. The rule not to put a column that can be
calculated in table is a common sence. But I have hundreds of queries to
quote the table every month, if the necessary and tiny redundancy can avoid
writing more queries , I thought it might be worth. I will try your second
way, because it’s my monthly work to do the queries upon monthly data, the
table I quote for generating monthly data will be updated every month, so it
doesn’t take some trouble.
Again to thank you for expanding my mind and your patience , by now you
have answered many of my troublesome (of course to me ) questions not only
with specific solutions and some general methods I should learning on.
It’s fortunate that I have turned to this discussion group for help, it’s
also fortunate to meet people like you who are willing to help basicers.
Salute!