Solved I need help in Overhead Expenses Query! (1 Viewer)

Local time
Tomorrow, 04:38
Joined
Aug 19, 2021
Messages
212
I've Created a Query that is working fine without date range. Let me share its design and datasheet views:

DESIGN VIEW
1632466760594.png

DATASHEET VIEW
1632466804585.png


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

Its not combining the values like this:
1632467136594.png

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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:38
Joined
May 7, 2009
Messages
19,169
because PaymentDate can have multiple dates.
use it as Where Clause only not as Group.
 
Local time
Tomorrow, 04:38
Joined
Aug 19, 2021
Messages
212
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

1632478723511.png

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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:38
Joined
May 7, 2009
Messages
19,169
can you use DSum()

Select HACID, HeadOfAccount, DSum("AmountPaid", "DebitVoucher", "HeadOfAccount='" & [HACID] & "'") As SumOfPayment
FROM HeadOfAccount;
 
Local time
Tomorrow, 04:38
Joined
Aug 19, 2021
Messages
212
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

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 07:38
Joined
May 7, 2009
Messages
19,169
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

Super Moderator
Staff member
Local time
Today, 19:38
Joined
Feb 19, 2002
Messages
42,981
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

AWF VIP
Local time
Today, 16:38
Joined
Apr 13, 2010
Messages
1,401
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,
 

Users who are viewing this thread

Top Bottom