looking up matching records in a table using another table matching one or more fields

[Gvandermaten]

Dear all. We have a database which holds various tables. One of them is called "tblRespondentTasks" which holds the activities (Task) a person has taken part in over time. This table grows over time as more tasks for each of our respondents are added. Take for example John Smith who is a regular participant. His personal details are is in a table called "tblRespondents" which holds among other things his firstname, lastname and address details. There is a one to many relation ship between "respondents" and "RespondentsTasks".




What we would like to do is the following. Each time we organise a new activity, we will receive a list of potential participants ("Respondents") from an external source. Before we allocate the activity to one or more respondents, we want to check if any of the respondents have taken part in the past by querying the "tblRespondenTasks" table.

The external list would arrive in Excel and might hold the following information:

FirstName
LastName
Postcode
MobilePhone

How do we compare this external list (which I can import in Access to create a temporary table say "tblTaskValidation)" with the "tblRespondentTasks" and return the respondents that appear on both lists. what makes it tricky for us is that we would like to be able to check each field from "tblTaskValidation" against "tblRespondentTasks" as sometimes a respondent might have two different mobile phone numbers but only lives at one address. Or the first name might be mispelled but the mobile phone number would still match etc. So we would like Access to query each of the four field listed above agains the table "tblRespondentTasks". We also don't know at this stage if any of the potential respondents are already on our "tblRespondents". Say for example John Smith is on the Excel list. We would like the query to check if a person called "John Smith" is actually in our table "tblRespondentTasks" by checking his name OR his MobilePhone or his Postcode. We don't mind if we get some "false" returns if we have two participants living in the same postcode area e.g.



Asssume John Smith has RespondentID 37 and Jo Miital has RespondentID 17.

Is it possible to run the "tblTaskValidation" against "tblRespondentTasks", check if we have any of the four potential respondents already on our database and return the tasks they have participated in? We can test each field individually but in an ideal world we would like to "upload" the external excle file, run it against our current database and return every respondent and relating tasks who has taken part in the past. Sorry if we're a bit clumsy on our description of the task ahead of us. Any tips or solution much appreciated.

Added after I posted the above:

tblRespondents has as PK RespondentID
tblRespondentTasks has as PK TaskID and a FK called RespondentID which links the table to tblRespondents

tblRespondentTasks also has another FK called ClientID which is linked to tblClients which hold client specific information

temporary table tblTaskValidation would have an automatic PK allocated and is not linked to any table.

Many thanks

[orange]

A key concept when designing a database is to clearly define the "business rules" and terminology.
Person, respondent, participant, potential respondent tend to cloud the issue. Why do you identify an Activity as a Task? There may be good reasons, but a little confusing.

It sounds like you may be dealing with 3 "things":
Respondents
Tasks
a junction (linking) table RespondentParticipatedInTask

and the extra table of PeopleToValidate. That is, compare these records to your existing table RespondentParticipatedInTask.

Some sample data including some records that you would validate as existing respondent and those who would be considered new respondent would help readers focus responses.

Good luck and welcome to the forum.

[Gvandermaten]

Many thanks for this, apologies for the confusion, first post and clearly need to learn (fast) to write better posts.

Hopefully the following will help:

Respondent: a person that participates in an activity (or task)
Task: an activity a respondent takes part in

We have a table that holds Respondent details called "tblRespondents"
We have a table that holds Task data called "tblRespondentTasks"
We have a table that holds Client details called "tblClient"

We get an Excel list on a regular basis from a external source holding FirstName, LastName, MobilePHone and Postcode. We can import this in a temporary table called let's say "tblTaskValidation"

We would like to check of any of the data on the external Excel list (tblTaskValidation" matches Respondents who have taken part in a Task in the past. As output we would like a listing of every respondent and corresponding tasks and the name of the client.

At the moment we have a one to many relationships between Respondents and RespondentTasks as follows:





Assume Jonh Smith and Jo Mittal are existing respondents, Teresa Test and Preeta Galore are new respondents.

Hope this will help,please let me know if you need anything else.

Once again, many thanks for looking at this!

[orange]

What is the Client in this set up? In 2 sentences plain English how do the things relate to one another?
Do you differentiate a Respondent from a Participant? If so, then how?

You call the excel file tblTaskValidation, but aren't you checking if the Respondent has been assigned previously vs new?

[Gvandermaten]

Thanks for getting back.

The client is an organisation asking us to organise an event which we call a task. Respondents (participants) attend this task. External providers find us respondents and we need to check if these respondents have taken part in a previous task as sometimes our clients don't want to have the same respondents attending a task (event). The list of potential respondents from external providers are passed on to us in a excel file. We want to check if any of these potential respondents have taken part in previous tasks. E could manually look up each potential respondent but if it is possible to run a query that would save us a lot of time. I have added the relations between the various tables. Hope this helps, many thanks for your patience.

[June7]

tblCountry should not be linked to CountyID field.

Your search would assume respondents always use same name and along with DOB the identity would be unique. Names are very poor unique identifiers. After all, more than one John Smith can be born on the same date. And individual might enroll one time as John Smith and next time as John Q. Smith. Would you assume these are the same person if the DOB is the same? And if there is an error in the DOB provided?

Yes, you can run a query to return all John Smith records on given DOB but you still have to make judgment the match is correct.

[Gvandermaten]

Thanks for your reply, I'll correct that error re CountyID straightaway.

DOB would be fairly unique, MobilePhone would be another one. We could ask the external provider to include DOB on top of MobilePhone. If we want to use either DOB or MobilePhone or both if both are included in the external Excel file provided, I think we would have enough. We are aware it's not perfect but it would be better then having to seach each entry manually in our database.