Report/Query Problem

[alfred67]

Hello

Need help with my report/query. I have attached my database if someone doesn't mind looking at it.

My report Payment Distribution is based on the query Payment Distribution and works good but when I try to get the liquidated damages into the report I get funny answers under the Liquidated Damages column.

Everything is related to the Accounts table. So when I bring in the Liquidated damages into the query it repeats the same dollar value when it see the account.

For example $1000 will be charged to the "Roadworks Account" (only once) but in the query everytime it sees "roadworks account" it places $1000 so I could end up with $6000.00 in the report.

So I'm not sure on how to fix this. Maybe my Query has to be redone.

Alfred67

P.S. if you are running my data base : start date= may1/10;End date=Todays date;Payment #=5,Holdback =13,Lasthold back =13
I'm out of town for the weekend but will be checking the site so replies might be slow from me

 

[apr pillai]

Your Database with modified Payment Distribution Query is attached. Your original query is renamed as Payment Distributionx.

Removed the Liquidated Damages table from the query and added the following Sub-Query for the Column: LiquidatedDamages instead:

LiquidatedDamages: (SELECT SUM([LiquidatedDamages])  FROM [Liquidated Damages] AS L_DAMAGES WHERE ([Accounts.Accountid]=[L_Damages].[Account ID]) GROUP BY [L_Damages].[Account ID])

 

[alfred67]

Thank you very much