Jul 06 2022 10:37 AM
Jul 06 2022 10:37 AM
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:
Juana de Tal
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 --
Jul 06 2022 01:43 PM - edited Jul 06 2022 01:47 PMSolution
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
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
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