Looking for repeat applicants in a database where an entry can have 1-4 applicants

[Thirith]

I've been asked to create a database for a new funding scheme at my place of work, something I've done before for a simpler funding scheme.

One thing I've done in the simpler DB is add a subform that lists all proposals where the applicant name is identical, so we can quickly check whether someone's submitted an application before and may therefore not be eligible. I would like to do the same, or something that's more or less identical in function, for the new DB, but I'm not sure what would be the best way to go about this.

In the new DB, there'll always be a main applicant and between 1 and 3 co-applicants. While we need to keep track of who the main applicant is for admin reasons, the duplicate check would have to be done for all applicants.

How would those of you with more experience go about doing this? My thoughts at the moment are for the main table (tblProposals) to have a one-to-many link to a table for applicants (tblApplicants), in which the project number, applicant name and other relevant data (e.g. main applicant (y/n), country, place of work, DoB) are stored. tblApplicants would be shown in a subform on the main form where we enter proposals. I'd then hope that I could create a non-editable subform that shows the result of all the projects that share an applicant (i.e. I'd have to create a SQL search using the 2-4 applicant names as search criteria).

Does this make sense? Is there an easier way to do this? Are there any pitfalls I need to watch out for?

 

[xenou]

My thoughts at the moment are for the main table (tblProposals) to have a one-to-many link to a table for applicants (tblApplicants), in which the project number, applicant name and other relevant data (e.g. main applicant (y/n), country, place of work, DoB) are stored.

My question would be what is project number you are referring to in Proposals? Is that a proposal number?

You could consider if this is really a many-to-many relationship (many applicants with proposals, many proposals (i.e., more than one being possible) for an applicant. That would call for a junction table. But you could use the proposed plan I guess, if you are okay with some redundant data:

------------------------------------------------
| Proposal | Applicant    | Country |      DOB |
------------------------------------------------
|        1 | John Smith   | USA     | 1/1/2000 |
|        1 | Mary Miller  | USA     | 1/2/2000 |
|        2 | Jacob Miller | Canada  | 1/3/2000 |
|        2 | John Smith   | USA     | 1/1/2000 |
------------------------------------------------

A more robust enterprise would have a junction table between Proposals and Applicants. This wouldn't matter as much if applicants really are never on more than one proposal

 

[Thirith]

In this case, the project number would be the auto ID the project entries are assigned; my organisation does use its own project numbers, but these are only assigned after an eligibility check, so they're not available when a record is created.

I was thinking about the many-to-many relationship, but I'm not sure what the added benefit would be for our setup; I'd imagine that perhaps 1 out of 100 proposals (or fewer) would have an applicant who's already submitted an earlier proposal. In addition, the country where they're employed and their place of work may change from one application to the next, and we'd need to know what these were at the time a proposal was submitted.

Would a many-to-many relationship make it easier to check for and highlight applicants that already submitted an earlier proposal than a one-to-many relationship would in this case?

 

[Thirith]

If you'll forgive the double post, let me rephrase my question somewhat, as either the one-to-many or the many-to-many approach takes care of storing the information, but that's not the most important part:

1) We've got individual records for project proposals.
2) Each proposal can have up to four applicants.
3) If an applicant was already an applicant previously, this should be signaled somehow (e.g. by means of conditional formatting).
4) Ideally, there should be a user-friendly way to see that the applicant was also an applicant for proposals X, Y and Z.

 

[xenou]

I generally avoid telling people what their forms should look like as I am not a UI expert. There's probably 100 ways to find and report duplicates and most or all are probably fine. It doesn't sound like a complicated search so probably almost any way you go on this would be "easy".

 

[Micron]

As always, the problem is subject to interpretation and mine may be way off. Seems like you really want 2 types of information, some of it related to a proposal, some related to the applicants. Presumably you'd like to see this all together, so what about a main form with 2 subforms? Main has proposal info and who is main applicant for it. 1st sub has co-applicants for said proposal. 2nd sub has proposals related to any of the applicants appearing on main or 1st sub. Of course, these records would need to show all the proposals per applicant, so this will likely be a longer list. If many applicants can relate to 1 proposal, plus 1 applicant can relate to many proposals, you definitely have a many to many relationship and need a junction table between proposals and applicants.

 

[Thirith]

Thanks to everyone for the suggestions, which have given me material to work with. I'm still at a bit of a loss with respect to how to do this structurally (mainly because the information associated with each applicant may change from one proposal to the next, and we need to store the info at the time of submission) and in terms of UI. Still, I have a number of leads now.

 

[Thirith]

Me again, after some fiddling with a test DB. My thought process on this may well be screwy, but here's what I'm thinking: I don't actually need two tables for this. What I'd need to do is run a query along the following lines:

SELECT (relevant fields) <the relevant="" fields=""> FROM tblProposals
WHERE Applicant1 = [Forms]![frmProposals].[txtApplicant1]<the table="" field="" displaying="" that="" proposal's="" applicant2="">
OR 
Applicant1 = [Forms]![frmProposals].[txtApplicant2]<that proposal's="" applicant3="">
OR
...
OR
Applicant4 =[Forms]![frmProposals].[txtApplicant4]<that proposal's="" applicant3="">

Does this make sense? The one thing I'd have to find a way of handling is that since a proposal can also just have two applicants, there'll be plenty of proposals where one or two of the fields are empty. I neither want to risk an error nor do I want any proposal with one or two empty applicant fields to show all others as proposals that involve the same applicants.


Edit: Turns out this works exactly as intended - with the added bonus that blank applicant fields don't actually affect the query, i.e. if a proposal doesn't have an applicant 4, the query doesn't return all proposals that have a blank applicant. Yay!</that></that></the></the>

 

[xenou]

Well just for the record, any table with fields like (Applicant1, Applicant2, Applicant3, Applicant4) is breaking table design principles.

Generally, you prefer to have a table such as (ApplicantID, Applicant). This table would then have four rows, one for each of your four applicants. Or two rows if there are two applicants, and so on. Queries and reports then become easier and more flexible (in the long term).

 

[Thirith]

Thanks, I take your point. Will think about how to implement this.