Query Formula (1 Viewer)

sunnytaru

Member
Local time
Yesterday, 19:14
Joined
Mar 24, 2022
Messages
41
Hello All,
Really appreciate some guidance on this and thanks in advance !

I have a table that has following fields
Table A ProjectID, DocumentTypeId, MemberID, MemberShare
Table B ProjectID, CurrentBudget
Query 1 ProjectID, SumofMemberShare, DocumentTypeId, MemberID

I have formula in a query that calculates the funding costs for each member.
MC: CCur(Nz(Sum([CurrentBudget]/[SumofMemberShare]*[MemberShare]),0))

There is a possibility that the DocumentTypeId can change, how can I in the existing formula add that criteria?

e.g.
ProjectIDDocumentTypeIDMemberIDMemberShare
2001194
20011115.5
Sum of Member Share9.5
2001593
20015113
Sum of Member Share6
 

plog

Banishment Pending
Local time
Yesterday, 18:14
Joined
May 11, 2011
Messages
10,998
What criteria?

An IIF statement can have criteria, a DLookup can have criteria, a WHERE clause is criteria. You've given us no criteria. Further, the code you have given us does not contain DocumentTypeID so it changing has no relevance.

Perhaps you can demonstrate with data what you expect to occur and under what conditions.

Also, you are doing division which opens yourself up to division by 0 errors if MemberShare is ever 0.
 

sunnytaru

Member
Local time
Yesterday, 19:14
Joined
Mar 24, 2022
Messages
41
What criteria?

An IIF statement can have criteria, a DLookup can have criteria, a WHERE clause is criteria. You've given us no criteria. Further, the code you have given us does not contain DocumentTypeID so it changing has no relevance.

Perhaps you can demonstrate with data what you expect to occur and under what conditions.

Also, you are doing division which opens yourself up to division by 0 errors if MemberShare is ever 0.
The code that I have given, is where I want to add DocumentTypeId but I need to know how.

When the code is executed the sumofmembershare for that DocumentTypeId should be used to calculate.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 18:14
Joined
Feb 28, 2001
Messages
23,115
Here is an old rule that I sometimes trot out for discussion. If you can't do it on paper then you can't do it in Access.

Meaning if you cannot compute your desired result by hand then you cannot get Access to do any better. Because Access doesn't "know" anything except how to build stuff AT YOUR DIRECTION. Access is otherwise just as dumb as a box of rocks. It doesn't know doodlum-squat about member shares. You are the subject matter expert. Access is just a glorified typewriter-keyboard adding machine.

Therefore, if you cannot adequately explain the problem in words, WE wont be able to adequately devise a solution in Access.
 

sunnytaru

Member
Local time
Yesterday, 19:14
Joined
Mar 24, 2022
Messages
41
Here is an old rule that I sometimes trot out for discussion. If you can't do it on paper then you can't do it in Access.

Meaning if you cannot compute your desired result by hand then you cannot get Access to do any better. Because Access doesn't "know" anything except how to build stuff AT YOUR DIRECTION. Access is otherwise just as dumb as a box of rocks. It doesn't know doodlum-squat about member shares. You are the subject matter expert. Access is just a glorified typewriter-keyboard adding machine.

Therefore, if you cannot adequately explain the problem in words, WE wont be able to adequately devise a solution in Access.
I have it clear in my head and honestly I am struggling to put in words, I had shown a table above explaining that but seems the visual is not clear. I want the formula to use the The sum of share for that DocumentTypeID e.g. (using data from above table) DocumentType ID is 1 then it should use the MemberofShare 9.5 and If the DocumentType ID is 5 then it should use the MemberofShare 6.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 19:14
Joined
Feb 19, 2002
Messages
36,836
When you are aggregating data, you need to omit all the fields that are "below" the level of summary. Therefore, in this case, remove MemberID from the Select clause since you want to summarize away, the individual member level.
 

mike60smart

Registered User.
Local time
Today, 00:14
Joined
Aug 6, 2017
Messages
1,235
I have it clear in my head and honestly I am struggling to put in words, I had shown a table above explaining that but seems the visual is not clear. I want the formula to use the The sum of share for that DocumentTypeID e.g. (using data from above table) DocumentType ID is 1 then it should use the MemberofShare 9.5 and If the DocumentType ID is 5 then it should use the MemberofShare 6.
Can you upload the database?
 

sunnytaru

Member
Local time
Yesterday, 19:14
Joined
Mar 24, 2022
Messages
41
Did you try my suggestion?
MemberID is not a part of the formula, I displayed that for explanation. I want to include DocumentTypeID in the formula CCur(Nz(Sum([CurrentBudget]/[SumofMemberShare]*[MemberShare]),0)) so if DocumentTypeID is 1 then I want SumofMemberShare 9.5 to be used. Currently it is replicating by using the SumofMemberShare 9.5 for DocumentTypeId 1 and 5 and same for SumofMemberShare 6 DocumentTypeID 1 and 5.
1649871458974.png
 

plog

Banishment Pending
Local time
Yesterday, 18:14
Joined
May 11, 2011
Messages
10,998
The best way to demonstrate data issues is with data, 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed this query the data from A.

Again, 2 sets of data--not 1 and an explanation, not 1 that you think shows everything. Show us what you are starting with and what you expect to end with. Just data.
 

GPGeorge

Grover Park George
Local time
Yesterday, 16:14
Joined
Nov 25, 2004
Messages
764
The best way to demonstrate data issues is with data, 2 sets:

A. Starting data from your table(s). Include table and field names and enough data to cover all cases.

B. Expected results of A. Show us what data you expect to end up with when you feed this query the data from A.

Again, 2 sets of data--not 1 and an explanation, not 1 that you think shows everything. Show us what you are starting with and what you expect to end with. Just data.
Another way to say something similar is to put the problem into its proper context.

Don't try to abstract away the messy details to "simplify" the picture. Often, the messy details are precisely what's needed to grasp the problem.
 

Users who are viewing this thread

Top Bottom