SOLVED

# How to do calculations from unique values

Copper Contributor

# How to do calculations from unique values

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 (Copper Contributor)
Solution

# Re: How to do calculations from unique values

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

# Re: How to do calculations from unique values

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

so you cannot divide the two.

see  Query2.

# Re: How to do calculations from unique values

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

# Re: How to do calculations from unique values

You're welcome
1 best response

Accepted Solutions
best response confirmed by Soyeljefe77 (Copper Contributor)
Solution

# Re: How to do calculations from unique values

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