SQL Insert join help

[BRobinsonS]

This SQL depends on the AccountName to be part of the JrDetails table.
Using Insert, what I want is to get the AccountName from a GL (General Ledger table) using the common AccountNo to be part of the result without duplicating the AccountName in the JrDetails table.

dbpExecSQL "NBAccounting" "INSERT INTO IncomeExpenseTbl (AccountNo,AccountName,AccountType,TotalDebits,TotalCredits) SELECT AccountNo,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails GROUP BY AccountNo,AccountName,AccountType " ""

I use a table to insert the results and do a report from there.

Suggestions?

[Rasl]

If I understood correctly, you do not want to repeat AccountName in the report, add ORDER BY AcountName to the script and in the report use a Group Header with AccountName as a grouping
salu2

[BRobinsonS]

Sorry I guess I wasn't clear.

I would like the query to do a 'look up' for the AccountName according to the AccountNo from the GL Table. Maybe it's a join I need.
I programmed it originally to add the AccountName to the JrDetails table which corresponded to the AccountNo.

The problem occurred when I did the report I had changed the AccountName in the GL and the report showed both names (current and prior) for the same account number.

Example: I used CP Pension and CPP for Account # 4100.
So the report showed:

4100 CP Pension
4100 CPP

If I could do a lookup in the GL Table it would show the current AccountName from the GL in the report.

[Rasl]

Try this

SELECT J.AccountNo, G.AccountName, J.AccountType, SUM (J.InAmt) AS TotalDebits, SUM (J.OutAmt) AS TotalCrédits
FROM JrDetails J
JOIN GL G ON G.AccountNo=J.AccountNo
GROUP BY J.AccountNo, G.AccountName, J.AccountType

[BRobinsonS]

Just noticed your reply. Thank you.
I assume the J after FROM JrDetails is in error as well as G after JOIN GL G.

I have tried to adapt the code but I am getting 'can't perform this operation on closed set'.

dbpExecSQL "TryDb" "SELECT JrDetails.AccountNo, GL.AcctDescription, GL.AcctType, SUM (JrDetails.InAmt) AS TotalDebits, SUM (JrDetails.OutAmt) AS TotalCrédits FROM JrDetails JOIN GL ON GL.AccountNo=JrDetails.AccountNo GROUP BY JrDetails.AccountNo, GL.AcctDescription, GL.AcctType" "JrGL"

I seem to have got my first goal to work by getting the Account Description from the GL accounts table, but I am having trouble with Summing fields ie. Debits and Credits fields.

I did originally have the sums working but without picking the Account Description from the GL.

I am sure it is something in the code I am not seeing. My basis knowledge of SQL scripting does not help.

I will keep trying.

[Rasl]

J and G are aliases of the tables, they are not an error. It can also be used like this: GL as G, but "as" is not mandatory. I'm assuming this is sql server

The error is fixed if you open the JrDetails and GL tables first

dbpOpenTable "TryDb" "JrDetails" ""
dbpOpenTable "TryDb" "GL" ""

salu2

[BRobinsonS]

I have my code working now.
Once I used LEFT JOIN is worked.
I now have the code and report working to my satisfaction.

Thanks for help.