I might be totally off here but I'm trying to avoid pointing to a specific row in the AccountGroup table. I want to be able to change the names of the groups but the Account Numbers has built in meanings according to standard chart of accounts in Europe.
AccountGroup contains AccountGroupNos like:
Descr: 'My Machinery Assets of type 1'
Account contains the accountnos:
Descr: My Machine type 1
Desc: My Machine type 2
Now, is this good practice or should I in fact store the AccountGroupID in the Account based on the formula below each time I add an Account? Or if this is OK, is it possible to do the query without the #temp-table so I can put the code in a view?
I don't know if it makes a difference but the database is located in a .MDF SQL Express file and will run localy on the end users computer.
Tanks in advance!
SELECT a.AccountID, a.Accountno, a.AccountDescription, g.AccountGroupDescription
FROM Account a, AccountGroup g
WHERE left(a.accountno,2) = g.AccountGroupNo
SELECT AccountID, AccountNo, AccountDescription, a.AccountGroupDescription, g.AccountGroupDescription as MainAccountGroup
FROM #temp a, AccountGroup g
WHERE left(a.accountno,1) = g.AccountGroupNo
ORDER BY AccountNo