Query returing accurate results sometimes, inaccurate at others

[bradp1979]

In my list box, I have one column called "Ratio." It is supposed to return the percentage of cars parked to guests estimated. Here's the code in the query: Ratio: Nz([Actual#CarsGarage],0)+Nz([Actual#CarsOutside],0)/[Expected#Guests]

However, as you can see by this picture, sometimes it gives me a really random, large number.




Oddly enough, no matter what I change those numbers to, I still get the same result.

Other clients, the ratio is returned perfectly.

[JoeM]

If both of the numbers added together are supposed to be divided by your denominator, then you need another set of parentheses, as the default calculation order is multiplication/division before addition/subtraction, i.e.

(Nz([Actual#CarsGarage],0)+Nz([Actual#CarsOutside],0))/[Expected#Guests]

[CJ_London]

to equate your calculation to your column headings - is that GE+CE/CP? - and if so, shouldn't it be (GE+CE)/CP?

And what would you expect the answer to be for the two examples?

Using # (and other non alphanumeric characters, other than underscore) in field names is generally a bad idea - they can cause issues

[June7]

Algebraic order of operations applies.

If you want the addition done first, then enclose in parens.

I can't get the results you show in the image. I tried various combinations of GE, CE, CP - none of which returns the % values displayed.

Provide sample of raw data and expected results.

[bradp1979]

Using # (and other non alphanumeric characters, other than underscore) in field names is generally a bad idea - they can cause issues

I know. I inherited this DB, I'm still working on getting all the #s out of names.

I can't get the results you show in the image. I tried various combinations of GE, CE, CP - none of which returns the % values displayed.
Provide sample of raw data and expected results.

I can't get the results either. The raw data is the number under CP, with is taken from two cells added up from frmEvent, divided by GE, which is taken from another cell on frmEvent.

In this case, it would be (Actual#CarsOutside+Actual#CarsGarage)/(Expected#Guests), so Actual#CarsGarage is 110, Actual#CarsOutside is 0, and Expected#Guests is 500. So it should be (110+0)/500=22%

on several of the individual clients it works like a charm. I don't know why it decides not to work randomly.

For example, on this client, no problem.

[rpeare]

Show the SQL statement driving this list box. If you are pulling a value from your form instead of within the query itself it may be misinterpreting a string as a number or vice versa.

if the query is not an aggregate query you should be able to get that percentage with a simple formula

Pct: CP/GE

I'd stay away from using % as a column header

if the query driving that list IS an aggregate query you would substitute in your formulas

Pct: Sum([Actual#CarsOutside]+[Actual#CarsGarage])/[Expected#Guests]

[June7]

So what is it about a particular client's data that would cause this?

If you want to provide db - think you've heard this before.

[CJ_London]

In this case, it would be (Actual#CarsOutside+Actual#CarsGarage)/(Expected#Guests), so Actual#CarsGarage is 110, Actual#CarsOutside is 0, and Expected#Guests is 500. So it should be (110+0)/500=22%

you need to clarify
'in this case' - which case? if you are referring to Event#7257, CP=Actual#CarsOutside= 110, GE=Expected#Guests=500 - so what =Actual#CarsGarage=0? If CE then it is 175, so the calc should be (110+175)/500=57%

[bradp1979]

Sorry I wasn't clear enough. I did mean event 7257.

And June, I definitely have heard it before, but in this case I can't provide DB for analysis, since I'd have to remove all confidential information, which would completely remove all information you could possibly use to come to a solution for this unique problem.

[June7]

I doubt name, address, phone number, SSN, email will have any bearing on the issue.

[bradp1979]

Perhaps not. The last few times I removed all sensitive data and uploaded the db for analysis, you were getting a bunch of error messages when you tried to open it, but I'll try again.

The forms in question are: On frmEvent: tabPreEvent and tabPostEvent, and on frmClient: lboEvent.

I'm including 3 back ends - I don't remember which one you had the least amount of problems with.

[rpeare]

Your formula is not what other suggested and will only work if Actual#CarsGarage is 0

Your formula currently says:

Ratio: Nz([Actual#CarsGarage],0)+Nz([Actual#CarsOutside],0)/[Expected#Guests]

What it should say is:

Ratio: (Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0))/[expected#guests]

[bradp1979]

Yep. That did it.

Thank you.

[JoeM]

Your formula currently says:

Ratio: Nz([Actual#CarsGarage],0)+Nz([Actual#CarsOutside],0)/[Expected#Guests]

What it should say is:

Ratio: (Nz([actual#carsgarage],0)+Nz([actual#carsoutside],0))/[expected#guests]

I think I said that all the way up in post #2: https://www.accessforums.net/queries...tml#post288887

[June7]

And echoed in posts 3 and 4.