How to do calculations from unique values

[Soyeljefe77]

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

[#2 · Accepted 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

Alison	3	2	1.5
David	1	0
Mary	0	1	0
Scott	3	3	1

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

[#3]

@Soyeljefe77 

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

so you cannot divide the two.

see  Query2.

[#4]

Thank you for your help (and your willingness to help)! It worked great.

[#5]

You're welcome