Show records based on one unique field (1 Viewer)

[paulsimo0]

Hi,

I wonder if somebody could please help with a query I am trying to figure out.
Basically I have 2 tables, one named “Customer” and the other named “Cust_Refer”
The (Customer) table is a list of customers who have purchased a product containing fields “EntryID” “Customer_Name” and “Entry_Date”
As they have become a customer I now wish to offer them an option to refer new customers to me in exchange for a reward. Currently the customers only have the opportunity to refer one person (at some later date, I will look at allowing them to refer more than one person)
The 2nd table (Cust_Refer) contains the fields “ReferID” “CustRefer_Name” “Referral_Name1” and “Refer_Date” basically listing the referral they have sent and when they sent it.
I have also created a query (Converted_Referrals) to list all the people who have been referred who have since become a customer. As such it lists all the customers who sent a referral that have now converted to a customer themselves. This way I can see which customers who sent a referral now need to be credited with a reward.

The problem I am encountering is when 2 or more customers, refer the same person as there can only be one reward per converted customer. The fairest way of doing this is to offer the reward to the person who sent the referral the earliest.

For example
Paul S from Leeds sent a referral:- Bill C from York on the 01/06/2018
Richard W from Manchester also sent the same referral Bill C from York but on 08/06/2018
Bill C from York then became a customer and so a reward is due to the person who referred him.
From this example Paul S from Leeds would be credited with the reward as he sent the referral earlier.

In my current query it lists all people who have been referred and converted to customers (including the duplicate referrals), so basically, I am wanting to filter out those duplicates whose referral date is not the earliest for that particular referral. In the example above Richard W from Manchester would not show in the query list.

I have placed a sort on “Referral_Name1” and a sort on “Refer_Date” so it will place the referrals in ascending order depending on when they were sent.

I wondered it was possible to add a 1 (in a separate field) to the first time a person is referred, 2 to the 2nd time the same person is referred and so on.
That way all unique referrals would have a 1 and any duplicate referrals would have a 2 or 3 or 4 depending on how many times they have been referred to me.
I could then set the criteria to only display the referrals who have a 1.

The problem I have is that I don’t know how to add the 1 or 2 or 3 etc in a separate field next to the particular record, I’m sure it will be quite straight forward, but it is baffling me, so any advice would be hugely appreciated.

The other criteria I would also need to add is the “Refer_Date” must be greater than or equal to the “Entry_Date” this means that a person has to become a customer first before they can send any referrals.

I have included the basic database, so you are able to see what I have done so far. If you can please help as I am just on the early road of getting to grips with Access and would like to develop my own knowledge more to the level I feel comfortable with.

Many thanks
Paul

 

[Ranman256]

The tCustRefer table only needs these fields:
CustID, ReferID,RefDate

CustID is the customer who gave refer. And ReferID is the referral ,but ALL
Data is in tCustomer table.

The customer can have infinite referrals.
All customer data is in Customer table.

 

[paulsimo0]

Hi Ranman

Thank you for the information, I see that it will make the database look tidier and that all the data would be in the customer table.
So I still need to be able to only show the unique referrals and hide any duplicate referrals that were submitted after the original one.

I'm sure I have seen somewhere a query where the records were given a number, all unique records would be given a number 1 and duplications of referrals would then be given a 2 or 3 or 4 dependant on how many times that person was referred to me. Surely then, it would be a simple matter of setting the criteria to only display the records with the number 1

That is the part of the query I am stuck on, i just can't figure out how it is achieved.

Many thanks
Paul

 

[Minty]

Use a totals query , and do a count on the CustID , set the criteria to =1

 

[paulsimo0]

Hi Minty

Thank you, that sounds vaguely familiar from what I had seen, I thought it did includ a count somewhere.
Sorry, I am only beginning to learn how to get around access, so forghive me if i don't fully understand.
How would I achieve this, I don't suppose you would mind showing an example of a totals query, please.

Many thanks
Paul

 

[Minty]

Actually - having had a look at your data and re-read you question, a single query may not work.

As Ranman has said you need a simpler data structure, and a date field in both to allow you to use the Min() function.

You need to query two distinct things here, and I think you'll require a sub query to make it work. If nobody else picks it up I'll have another look tomorrow.

 

[bastanu]

Please have a look at the qryToBeRewarded query in the attached file and see if that is what you want.

Cheers,
Vlad

 

[paulsimo0]

Hi Bastanu

Thank you for creating the referrals db. That is almost there, I had to tweak it a little bit to meet some of the criteria I am after.

In the qryToBeRewarded Query, records should only show if they pass the following criteria

1. Only referrals from customers are allowed, in other words if somebody who is not a customer refers someone else this should be disregarded. (this is one of the criteria I am still having problems with)

2. Only the first occasion of a referral should be listed. All subsequent listings of duplicate referrals should be ignored. (this is what you created for me in the database, thank you for that)

3. When a customer sends a referral, the date the referral is received must be later than or equal to the date the customer bought a product. (this is the part I tweaked, and seems to work okay).

As you will see from the attached database, there are 4 customers who have sent a referral in the table Cust_Refer

Two of the customers have sent the same referral, so in this case the first person to send the referral would be rewarded. In this case it is "Test1"

Of the 4 people who sent a referral, only 3 of them are actual customers, therefore customer "Test4" who is not a customer should be disregarded.

Although customer "Test3" is a customer and sent a referral "Refer2" (who also became a customer) this should also be ignored as the date "Test3" sent the referral is earlier than the date "Test3" became a customer.

If you could please advise how I can achieve the criteria, point 1 above, that would then seem to fulfill all the criteria.

Many thanks
Paul

 

[bastanu]

Here you go Paul. By adding an equijoin to the Customer table in the first query (qryFirstReferDate) we exclude the referrals from non-customers.

Cheers,
Vlad

 

[paulsimo0]

Thank you, that is what I was after. It works like a charm.
This is a great forum and brilliant that there are contributors like yourself willing to help people like myself solve access problems.
I am new to the forum, but really keen to learn more about access so that I can hopefully also contribute to others problems in the future.

Many thanks
Paul

 

[bastanu]

Great to hear it worked, Access is such a great tool and I am sure you will be posting answers here in the future if you put in your time .

Cheers,
Vlad