Solved Obtaining Customer Net In Query (1 Viewer)

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
I am having a slight issue with a query this morning. I am trying to make a "Quote Summary" to put on my main form to show a break down of a quote.
Capture.PNG

Pretty simple. The problem I am having is making the field for C/N (Customer Net). It should (in theory) just be DN divided by GP. I don't think you can refer to other expressions within an expression, but it should just be:

CN: [GP]/([Price]*[Quantity])

That however doesn't seem to calculate correctly. I have also tried a simpler CN: [GP]/[Price], but that doesn't work either. The number it outputs is much, much lower than it should be. It should just be my percent mark up divided by the price to get my customer price.

After I get my Customer Net, I then need to make another expression that is essentially CN-DN to show the total profit in the quote.

I think I could do this without a query and just enter all my expressions into textbox, but I don't know how I would get my WHERE's built into them doing that way. Can anyone please offer some insight?

Also, Happy Monday everyone!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:54
Joined
Oct 29, 2018
Messages
21,360
Have you tried repeating the expression?

Sum(Price*Qty)/GP
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
Have you tried repeating the expression?

Sum(Price*Qty)/GP
I believe I tried that one once, but I just did it again to make sure and now its resulting in a much much LARGER number than it should be.
Capture.PNG


If it calculated correctly, CN should be $43,620.00 @ 10%
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:54
Joined
Sep 21, 2011
Messages
14,059
It appears to be doing just what you said earlier?

It should (in theory) just be DN divided by GP
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
It appears to be doing just what you said earlier?
I don't know how it arrived to that number in post #3 however. That is quite a large number when it should have only been $43,620.00.
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
Hi. How exactly did you come up with $43,620.00? It's 10% of what value?
In the snip on post 3, the query is showing my DN (Distributor Net ie our cost) as $39,258, which when I checked against the records to verify was correct, was. That number then divided by my average GP, which in the snip was 10%, would equal a Customer Net (CN) of $46,620.

However the expression (Sum(Price*Qty)/GP) is returning $392,579.99. I have no idea how it arrived to that number based on the records I am working with other than multiplying by 10 and maybe some rounding or something. So while I think the calculation being done might be correct, it may not be using the field [GP] correctly.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:54
Joined
Sep 21, 2011
Messages
14,059
DN is likely rounded.
10% is 0.10
Dividing by 0.10 is the same as multiplying by 10 ?
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
DN is likely rounded.
10% is 0.10
Dividing by 0.10 is the same as multiplying by 10 ?
Maybe my thinking is all wrong then. I have always personally done my calculation as such:
To get a 10% mark up do my cost divided by 10% which would be cost divided by .90. So $39,258 / .9 gives me the $42k number which is a 10% increase.

Maybe in that lies my problem with how I am structuring this in that it isn't calculating the way I do it. That would be make things even weirder considering I have a few forms that do this calculation and they work just fine.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:54
Joined
Sep 21, 2011
Messages
14,059
So you would divide by (1-GP) ?
However 39258/0.9 = 43620 ? :unsure:
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
So you would divide by (1-GP) ?
However 39258/0.9 = 43620 ? :unsure:
Your second part is correct, I quickly typed so didn't put exact values.
So I just need to do the (1-GP) to get it to calculate right?
 

tmyers

Well-known member
Local time
Today, 11:54
Joined
Sep 8, 2020
Messages
1,090
Your second part is correct, I quickly typed so didn't put exact values.
So I just need to do the (1-GP) to get it to calculate right?
Huh, that worked.
I wonder why in queries the percent value is calculated differently than in expressions on forms.
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:54
Joined
Sep 21, 2011
Messages
14,059
If I wanted a 10% markup, I'd just add 10% of the net to the net, which is pretty much multiplying by 1.1 ?
so 39528 plus 10% markup is 43480.80 ?

Just to check, I'd take net away from gross and see what that produces.? In this case 3952.80
 

Users who are viewing this thread

Top Bottom