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;

 

[Pat Hartman]

But it is missing some IDs because there is no values.

Find all the records with missing IDs and either add the ID or delete the reows. Then in design view make the field required and remove the default of 0 and leave the default as null. That will prevent future missing IDs

PS. Do NOT use domain functions inside queries or loops when there is another option. you have another option. The one you chose will be fine. Your grouping problem was caused by the extra non-visible field.

 

[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,