Writing a Formula in Access

[efmbman]

I am "OK" when it comes to figuring out a fomula in Excel, but Access is much different for me. I am looking to set up a calculated field in a report.

The result is find the Client-to-Staff ratio but there are two possible conditions:

CONDITION 1: If VacantFTEE = 0
ActiveClients / (DirectFTEE + DetailedFTEE + ProvidedFTEE)



CONDITION 2: If VacantFTEE > 0
ActiveClients / (DirectFTEE + VacantFTEE + DetailedFTEE)

I am presuming I need some sort of IF/OR statement to make this as 1 formula, but I can't seem to make it work.

I really appreciate the help

[pbaldy]

Try

ActiveClients / (DirectFTEE + IIf(VacantFTEE > 0, VacantFTEE, DetailedFTEE) + ProvidedFTEE)

[euanl]

IF in access can be done pretty much as you do in Excel only its IIF, and not IF.

In VBA it's different, its

IF whatever field you want to look up = Whatever THEN

'return whatever result

ELSEIF whatever field you want to look up = Whatever else THEN

'return whatever result

ELSE 'any other potential outcome

END IF

[efmbman]

Try

ActiveClients / (DirectFTEE + IIf(VacantFTEE > 0, VacantFTEE, DetailedFTEE) + ProvidedFTEE)

I am getting a result of "#Error" in some records, other records show the correct answer, and yet others show the incorrect answer (I did the math by hand to verify).

Any ideas what may cause that?

[pbaldy]

Is there a possibility of the divisor being zero or Null on the error records? What's an example of the values for the various fields when the result is error?

[efmbman]

I will give three examples... one gives the "#Error", one does the math incorrectly, one does the math correctly:

Site 689:
Active Clients: 18
DirectFTEE: 6.35
DetailedFTEE: 0
ProvidedFTEE: .3
VacantFTEE: 1.5

Result should be: ActiveClients / (DirectFTEE + VacantFTEE + ProvidedFTEE) because VacantFTEE > 0
OR................... 18 / 6.35 + 1.5 + .3)
OR................... 18 / 8.15
Which is............ 2.21 but is displaying "#Error"

Site 642:
Active Clients: 34
DirectFTEE: 5
DetailedFTEE: 0
ProvidedFTEE: 0
VacantFTEE: 0

Result should be: ActiveClients / (DirectFTEE + DetailedFTEE + ProvidedFTEE) because VacantFTEE is not > 0
OR................... 34 / (5 + 0 + 0)
OR................... 34 / 5
Which is............ 6.80 but is displaying 0.07 (that's just bad math!)

Site 558:
Active Clients: 42
DirectFTEE: 4.2
DetailedFTEE: 0
ProvidedFTEE: 0
VacantFTEE: 0

Result should be: ActiveClients / (DirectFTEE + DetailedFTEE + ProvidedFTEE) because VacantFTEE is not > 0
OR................... 42 / (4.2 + 0 + 0)
OR................... 42 / 4.2
Which is............ 10 which is correct!

Why would the formula work in some areas, give incorrect results in others, and an error in still others?

[pbaldy]

Can you post the db here?

[efmbman]

Sadly, no. It contains a ton of personal data (names, SSNs,etc). I understand that can be a dealbreaker when trying to troubleshoot these problems. I do appreciate the assist nevertheless.

[ssanfu]

I am looking to set up a calculated field in a report.

I would recommend NOT using calculated fields (in a table - if this is what you mean). Do the calculation in a query.

Using Paul's formula, I set up a table (no spaces in field names) and created this query:

SELECT Table2.Site, Table2.ActiveClients, Table2.DirectFTEE, Table2.DetailedFTEE, Table2.ProvidedFTEE, Table2.VacantFTEE, [ActiveClients]/([DirectFTEE]+IIf([VacantFTEE]>0,[VacantFTEE],[DetailedFTEE])+[ProvidedFTEE]) AS Result
FROM Table2;

The results were:

Site	ActiveClients	DirectFTEE	DetailedFTEE	ProvidedFTEE	VacantFTEE	Result
698	18	6.35	0	0.3	1.5	2.21
642	34	5	0	0	0	6.80
558	42	4.2	0	0	0	10.00

In the query I set the format to fixes and 2 decimals.

[efmbman]

That... is... awesome! And a bit over my skill set. I have to ask: where would I put that code? It does not look like a query to me (I am very basic when it comes to Access... maybe you noticed lol).

Thanks!

[pbaldy]

Sadly, no. It contains a ton of personal data (names, SSNs,etc). I understand that can be a dealbreaker when trying to troubleshoot these problems. I do appreciate the assist nevertheless.

Understandable. If you're still stuck, even just a representative sample db with the fields in the formula. I don't see why it would error on those values offhand.

[ssanfu]

I have to ask: where would I put that code? It does not look like a query to me

It is not code - it is the SQL of a query.

I used Table2 as the table name since you didn't specify a table. In the SQL, you will have to change "Table2" to the name of your table.
You will also have to check the field names. I don't use spaces in object names, so my names might be different than what you use......

Then -
Create a new query.
Switch to SQL View.
Delete whatever you see in SQL view (Should be SELECT; )
Paste in the SQL
Execute the query.