Help with update stmt for chart of accounts

[eerich]

Hello everyone,

I have a problem I'm trying to solve using SQL to tag a chart of accounts hierarchy file. My COA has several thousand line items and I need to figure a way to tag the account class field to each account in the chart. The problem is I can't figure a way to setup a proper join for this task since the account class is derived based on a range of account numbers. I can't match on a specific account. Here's an example:

CASH Accounts are between 1010 and 1149
ACCTS RECEIVABLE Accounts are between 1150 and 1299
INVENTORY Accounts are between 1300 and 1539
etc.

I extracted a table with just the min and max ranges of the Account Class items which contains 84 records.

However I can't figure out how to create an SQL update statement to search for accounts in the ranges and tag the appropriate account class.

I did create an update statement for Account Type (Assets / Liabilities / Equity / Revenue / Expenses) using a LIKE clause in the WHERE criteria because it was only 5 items and the logic was much simpler....
UPDATE COA
SET AcctType = 'Assets'
WHERE AcctNum LIKE '1*'

Would that be the only way to do this with such a large array? Account Class has 84 records to go through so I would hope to not have to write 84 queries to do that update.

Any help is appreciated.

 

[maxhugen]

This is very rough and untested, but may help you:

UPDATE COA
SET AcctType =
IIF(AcctNum Between 1010 and 1149, "CASH",
IIF(AcctNum Between 1150 and 1299, "ACCTS RECEIVABLE",
IIF(AcctNum Between 1300 and 1539, "INVENTORY",

Null
)
)
)

Max Hugen
Australia

 

[eerich]

Thanks, Max.

While it did work in theory, the problem is that I received an error due to the complexity of the nested IIFs. I have 84 records (thus 84 IIFs) and it produced an error after #6. I really wish there was a way to do a join but given the account numbers are unique, I can't establish a field to match on, since the account class is over a range of accounts. Also, when I tried to break it down into chunks, it overwrites the column each time I run the update statement on different ranges. Not sure what else I may try, but I would appreciate any insights.

 

[Andrzejek]

How about this:

Dim strSQL As String

strSQL = "UPDATE COA SET AcctType = "

Select Case AcctNum 
    Case 1010 To 1149
        strSQL = strSQL & "'CASH'"
    Case 1150 To 1299
        strSQL = strSQL & "'ACCTS RECEIVABLE'"
    Case 1300 To 1539
        strSQL = strSQL & "'INVENTORY'"
    ...

End Select

Debug.Print strSQL

Execute strSQL

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.