Creating unique rows in one column of query

[ANJIBA]

CodeSalesPayment_Tbl_Transaction AmountGains_and_Losses_Tbl_Transaction AmountNet_Cash_Proceeds001EMP000318($100,000.00)$30,000.00($70,000.00)001EMP000318($100,000.00)$20,000.00($80,000.00)

I'm a beginner here. Above is a portion of a query I created and I am trying to get the gains and losses amount to deduct out of the same $100,000 because there is really only one sales Payment. Can anyone please help me?

Thanks.

 

[yorkshirepudding]

Check your post. This doesn't look like a query and it is hard to read as a table. Do you have the SQL that these figures come from?

Cheers
yorkie

 

[ANJIBA]

SELECT Prop_Mast_Tbl.[Property Code], Prop_Mast_Tbl.Resident, GL_SalesPayment_Tbl.[Transaction Amount] AS [SalesPayment_Tbl_Transaction Amount], GL_Gains_and_Loss_Tbl.[Transaction Amount] AS [Gains_and_Loss_Tbl_Transaction Amount], [SalesPayment_Tbl_Transaction Amount]+[Gains_and_Disposals_Tbl_Transaction Amount] AS Net_Cash_Proceeds
FROM (Prop_Mast_Tbl LEFT JOIN GL_SalesPayment_Tbl ON (Prop_Mast_Tbl.[Property Code] = GL_SalesPayment_Tbl.[Property Code]) AND (Prop_Mast_Tbl.[Property Code] = GL_SalesPayment_Tbl.[Property Code])) LEFT JOIN GL_Gains_and_Loss_Tbl ON Prop_Mast_Tbl.[Property Code] = GL_Gains_and_Loss_Tbl.[Property Code];



This is the entire SQL.

The problem is that I get the below reconstructed data -

Property Code Resident Sales Payment Gains and Losses Net Cash Proceeds
415 John 100,000 (20,000) 80,000
415 John 100,000 (10,000) 90,000


What I want is only one line for property code with a total loss amount of 30,000 and therefore a net cash proceeds amount of 70,000 - One line and totals. Hope the detail helps....

 

[yorkshirepudding]

HI

The quickest way would be to do 3 queries:

1. Total sales grouped by property code

SELECT GL_SalesPayment_Tbl.PropertyCode, Sum(GL_SalesPayment_Tbl.TransactionAmount) AS SumOfTransactionAmount
FROM GL_SalesPayment_Tbl
GROUP BY GL_SalesPayment_Tbl.PropertyCode;

2. Total gains/losses grouped by property code

SELECT GL_gains_and_loss_tbl.PropertyCode, Sum(GL_gains_and_loss_tbl.TransactionAmount) AS SumOfTransactionAmount
FROM GL_gains_and_loss_tbl
GROUP BY GL_gains_and_loss_tbl.PropertyCode;

3. Summary by property

SELECT Prop_Mast_Tbl.PropertyCode, Prop_Mast_Tbl.Resident, Sum(Query2.SumOfTransactionAmount) AS SumofSales, Sum(Query3.SumOfTransactionAmount) AS SumofGainsLosses, Sum([Query2]![SumOfTransactionAmount]+[Query3]![SumOfTransactionAmount]) AS Net_Cash_Proceeds
FROM (Prop_Mast_Tbl INNER JOIN Query2 ON Prop_Mast_Tbl.PropertyCode = Query2.PropertyCode) INNER JOIN Query3 ON Prop_Mast_Tbl.PropertyCode = Query3.PropertyCode
GROUP BY Prop_Mast_Tbl.PropertyCode, Prop_Mast_Tbl.Resident;

Sorry, not done your exact aliases as just trying to do it quickly, but it should give you the idea.

Cheers
yorkie