Calculations within a query

[botuco via AccessMonster.com]

Just when you think you think you know Access, you try to do something more
complex and get stuck again....With the help of your discussion group I was
able to come up with a query which outputs two fields. The query is based on
two tables, TbInterp LEFT JOIN TbAo. The results of the query might look
like this:

InterpID AoID
1 23
2 0
3 15
4 40
5 4

AoID gives a count of the number of times InterpID occurs within the table
TbAo. What I’m trying to do now is make calculations based on these results.


For example, I would like to divide the result of AoID where InterpID=1 by
the result of AoID where InterpID=3 (basically, divide 23/15) so I can place
these results in a report.

Can I do this on the same query or do I make these calculations on a separate
query or in a report itself.

Thanks for your constant help.

 

[David F Cox]

The values of AoID that you want to use in your calculations occur in
different rows. You will have to get them into the same row in order to do a
calculation on them. It depends on how many InterpID fields you have whether
this is practical, and on how fixed the application is whether that is
advisable.

You can get the numbers into the same row by using IIF, something like
this:-

AoID1: SUM(IIF([interpID]=1,1,0))
AoID2: SUM(IIF([interpID]=2,1,0))
etc

Then you can use this query as an input to do the calculations where you
desire.

 

[botuco via AccessMonster.com]

Excellent. That should be enough to get me back in track.
Thanks a lot for your help David.

 

[botuco via AccessMonster.com]

One last quick question, just to clarify.

Any advantage of using Sum(IIf([InterpID]=1,1,0)) vs DCount("*","[TbInterp]",
"[InterpID]=1]")

 

[David F Cox]

It might have been better to post this as a new question to ensure that it
got a thorough response.

I may be wrong here, but I believe that DCount will go through the table to
get its result, whilst the IIF method will calculate its results as the
query goes through the table. If this is true the DCount method could be
slower. I have not tested this, and just use IIF because that is the way I
know and love. It may make no difference. The Access query optimizer is a
joy not to behold, it does a superb job behind the scenes.

 

[botuco via AccessMonster.com]

That response is more than enough for me.
Thanks again