Solved I need help in Overhead Expenses Query!

[Khurramshaikh47]

I've Created a Query that is working fine without date range. Let me share its design and datasheet views:

DESIGN VIEW

DATASHEET VIEW

But when I am trying to add date field like this:

Its not combining the values like this:

Actually I want to give date parameter to make the overhead report for specific date range.

Please guide how to do it.
Thank you

 

[arnelgp]

because PaymentDate can have multiple dates.
use it as Where Clause only not as Group.

 

[Khurramshaikh47]

because PaymentDate can have multiple dates.
use it as Where Clause only not as Group.

Thank you so much dear.
But it is missing some IDs because there is no values.
HAC-06
HAC-10
HAC-13
HAC-14

But I want to show all HeadOfAccounts.
Please guide how to show all of them?

 

[arnelgp]

can you use DSum()

Select HACID, HeadOfAccount, DSum("AmountPaid", "DebitVoucher", "HeadOfAccount='" & [HACID] & "'") As SumOfPayment
FROM HeadOfAccount;

 

[Khurramshaikh47]

can you use DSum()

Select HACID, HeadOfAccount, DSum("AmountPaid", "DebitVoucher", "HeadOfAccount='" & [HACID] & "'") As SumOfPayment
FROM HeadOfAccount;

SELECT HeadOfAccount.hACID, DebitVoucher.HeadOfAccount, Sum(DebitVoucher.AmountPaid) AS SumOfAmountPaid
FROM HeadOfAccount INNER JOIN DebitVoucher ON HeadOfAccount.hACID = DebitVoucher.HeadOfAccount
WHERE (((DebitVoucher.PaymentDate) Between [Enter Start Date:] And [Enter End Date:]))
GROUP BY HeadOfAccount.hACID, DebitVoucher.HeadOfAccount
ORDER BY HeadOfAccount.hACID;

Can you please tell me where should I add that statement? I am beginner in SQL.

 

[arnelgp]

create new query, and paste it SQL view:

Select HACID, HeadOfAccount,(SELECT SUM(AmountPaid) FROM DebitVoucher AS T1 WHERE T1.HeadOfAccount=HeadOfAccount.[HACID] And T1.PaymentDate Between [Enter Start Date:] And [Enter End Date:]) As SumOfPayment
FROM HeadOfAccount;

 

[bastanu]

Maybe try this (double-click the join between the tables and change it to include all records from HeadOfAccount):

SELECT HeadOfAccount.hACID, DebitVoucher.HeadOfAccount, Sum(DebitVoucher.AmountPaid) AS SumOfAmountPaid
FROM HeadOfAccount LEFT JOIN DebitVoucher ON HeadOfAccount.hACID = DebitVoucher.HeadOfAccount
WHERE (((DebitVoucher.PaymentDate) Between [Enter Start Date:] And [Enter End Date:]))
GROUP BY HeadOfAccount.hACID, DebitVoucher.HeadOfAccount
ORDER BY HeadOfAccount.hACID;

Cheers,