Calculate a sum from a join 1

[BasicBoy]

I have a table for Creditors and a table for all their CreditorTransactions, a table for Debtors and a table for all their DebtorTransactions. I need to calculate (in different fields) the total transaction amounts for each, such as in :

Take all the Creditors, put their Names in field(0), their InvoiceTotals in field(1) and their CreditNoteTotals in field(2)and union this with all the Debtors, put their Names in field(0), the sum of their InvoiceTotals in field(3) and the sum of their CreditNoteTotals in field(4).

This is far too complicated for me to even try, but I will :

SELECT
Creditors.Credname,
SUM(CreditorTransactions.amount)as CredInvoiceTotals where TransType=1,
SUM(CreditorTransactions.amount) as CredCreditNoteTotals where TransType=2
FROM Creditors
INNER JOIN
CreditorTransactions
ON
Creditors.ID=CreditorTransactions.CredID
WHERE
CreditorTransactions.Transdate<=Mydate (parameter)

UNION

Debtors.Debtorname,
SUM(DebtorTransactions.amount) AS DebtorInvoiceTotals WHERE
TransType=1,
SUM(DebtorTransactions.amount) as DebtorCreditNoteTotals WHERE TransType=2
FROM
Debtors
INNER JOIN
DebtorTransactions
ON
Debtors.ID=DebtorTransactions.DebtorID WHERE DebtorTransactions.Transdate<=MyDate

... and this is about as far as I can go.

The TransactionTypes are available in the Transactions table as a field called TransType where 1 refers to invoices and 2 refers to credit notes.

I know its a tall order, but help will be appreciated.

Thanks

 

[PHV]

What about this ?

SELECT U.Name
, SUM(CredInvoice) AS CredInvoiceTotals
, SUM(CredCreditNote) AS CredCreditNoteTotals
, SUM(DebtorInvoice) AS DebtorInvoiceTotals
, SUM(DebtorCreditNote) AS DebtorCreditNoteTotals
FROM (
SELECT C.Credname AS Name
, IIf(T.TransType=1,T.amount,0) AS CredInvoice
, IIf(T.TransType=2,T.amount,0) AS CredCreditNote
, 0 AS DebtorInvoice, 0 AS DebtorCreditNote
FROM Creditors C
INNER JOIN CreditorTransactions T ON C.ID=T.CredID 
WHERE T.Transdate<=Mydate
UNION SELECT D.Debtorname, 0, 0
, IIf(T.TransType=1,T.amount,0)
, IIf(T.TransType=2,T.amount,0)
FROM Debtors D
INNER JOIN DebtorTransactions T ON D.ID=T.DebtorID
WHERE T.Transdate<=MyDate
) U
GROUP BY U.Name

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BasicBoy]

You are incredible - although I still have a 'syntax error in FROM clause' which I cannot find. Still checking and will revert to you.

Thanks.

 

[BasicBoy]

I do not understand why the CreditorTrans and the DebtorTrans tables both have an alias of T and how does the SQL know which table is U to get the name from - or maybe I do not need to know and just follow blindly.

Or shall I say we hold a competition for 'spot the error'

I have not given the correct names previously to make it easy, and have now filled in the names I actually use. Can you please check and say where I have gone wrong.

My fields in Both Creditors and Debtors has ID, CredName or DebtorName etc.

My fields in both CreditorTrans and DebtorTrans are : ID (of transaction), CredNo or DebtNo (referring to the ID of these in their tables), Status (1 for invoices and 2 for credit notes), Amount.

"SELECT
U.Name,
SUM(Creditortrans.Amount) AS CredInvoiceTotals,
SUM(CreditorTrans.Amount) AS CredCreditNoteTotals,
SUM(DebtorTrans.Amount) AS DebtorInvoiceTotals,
SUM(DebtorTrans.Amount) AS DebtorCreditNoteTotals
FROM
(SELECT
C.Credname AS Name,
IIf(T.Status=1,T.amount,0) AS CredInvoice,
IIf(T.Status=2,T.amount,0) AS CredCreditNote,
0 AS DebtorInvoice,
0 AS DebtorCreditNote
FROM
Creditors C
INNER JOIN
CreditorTrans T ON C.ID=T.CredNo
WHERE
(T.Transdate>=#" & StartDate & "# and T.Transdate<=#" & EndDate & "#)
UNION
SELECT
D.Debtorname,
0,
0,
IIf(T.Status=1,T.amount,0), IIf(T.Status=2,T.amount,0)
FROM
Debtors D
INNER JOIN
DebtorTrans T
ON D.ID=T.DebtNo
WHERE
(T.Transdate>=#" & StartDate & "# and T.Transdate<=#" & EndDate & "#)
)
U
GROUP BY U.Name"

Thanks again.

 

[Qik3Coder]

The UNION separates the 2 separate queries.
The engine will actually run each query separately, then merge the results. Because of this you can reuse aliases (C,D, T) to keep your code clean and show that you are trying to do the same thing in each.

As for your last post. The alias you need to use is U.
Once you collapse the tables with a union you lose the separation of data.

Lodlaiden

If [blue]you have problems[/blue], I want [green]source code[/green] AND [green]error messages[/green], none of this [red]"there was an error crap"[/red]

 

[BasicBoy]

Thanks - I got it right