SOLVED

How to do calculations from unique values

Occasional Contributor

I posted a little while ago about how to get unique values (see previous post ) where @isladogs gave a great solution.  But now I need to take it a step further. I would like to take the unique values from each of the referral columns and compare them to each other.  I can get the unique values from each column, but I don't see a way to connect the two to be able to perform a calculation.

 

I have this table:

Client

Referred_To

Referred_By

John Doe

 

Scott

Grant Imohara

 

David

Juan Fulano

Scott

 

David Hasselhoff

Scott

 

Jane Smith

 

Alison

Bill Sykes

Alison

 

Sam Jones

 

Scott

Harry Houdini

 

Alison

Sarah Smith

Mary

 

Jim Jones

Scott

 

Juana de Tal

 

Scott

Shannon Sim

 

Alison

Frank Sinatra

Alison

 

 

And I want this report:

Total Referred To:       Total Referred By:        Ratio By/To

Scott   3                         Scott   3                      Scott   1.0

David 1                          Alison  2                     Mary    --

Alison 3                         Mary   1                      David  --

                                                                         Alison  0.66

4 Replies
best response confirmed by Soyeljefe77 (Occasional Contributor)
Solution

@Soyeljefe77 

Hi again
There is more than one way of doing this.
The simplest to understand isn't the most elegant way as it involves 4 queries.
This is partly because your data isn't normalised.

 

First I copied your data into a table called ClientReferrals


You should have a separate table listing all those involved in referrals - either By or To

As you don't have that, I've bodged it using a union query qryReferrersUnion:

 

SELECT DISTINCT ClientReferrals.Referred_To AS Referrals
FROM ClientReferrals
WHERE (((ClientReferrals.Referred_To) Is Not Null));
UNION SELECT DISTINCT ClientReferrals.Referred_By AS Referrals
FROM ClientReferrals
WHERE (((ClientReferrals.Referred_By) Is Not Null));

 

 

That gives 4 names 

 

Referrals

Alison
David
Mary
Scott

 

Next I used the query & the original table to get an aggregate query qryCountReferredBy

 

SELECT qryReferrersUnion.Referrals, Count(ClientReferrals.Referred_By) AS CountOfReferred_By
FROM qryReferrersUnion LEFT JOIN ClientReferrals ON qryReferrersUnion.Referrals = ClientReferrals.Referred_By
GROUP BY qryReferrersUnion.Referrals;

 

 

Similarly for qryCountReferredTo

 

SELECT qryReferrersUnion.Referrals, Count(ClientReferrals.Referred_To) AS CountOfReferred_To
FROM qryReferrersUnion LEFT JOIN ClientReferrals ON qryReferrersUnion.Referrals = ClientReferrals.Referred_To
GROUP BY qryReferrersUnion.Referrals
HAVING (((qryReferrersUnion.Referrals) Is Not Null));

 

 

Now the last 2 queries are combined to get the ratios in qryFinal

 

SELECT qryCountReferredBy.Referrals, qryCountReferredBy.CountOfReferred_By, qryCountReferredTo.CountOfReferred_To, IIf([CountOfReferred_To]>0,[CountOfReferred_By]/[CountOfReferred_To],Null) AS RatioReferredByTo
FROM qryCountReferredBy INNER JOIN qryCountReferredTo ON qryCountReferredBy.Referrals = qryCountReferredTo.Referrals;

 

 

An IIf function was used to resolve a divide by zero error

The results are:

Referrals CountOfReferred_By CountOfReferred_To RatioReferredByTo

Alison321.5
David10 
Mary010
Scott331

 

EDIT: Sorry but the forum software messed up the table layout

 

To save you reinventing the wheel, see attached database.

Whilst this works it is more complex than it would be if your tables were properly designed.

 

You need to sort out the structure of your data otherwise every action like this will be unnecessarily complex

@Soyeljefe77 

there are referral by = 0 and there are also referral to = 0,

so you cannot divide the two.

see  Query2.

Thank you for your help (and your willingness to help)! It worked great.
You're welcome