Thanks Thanks:  0
Results 1 to 10 of 10

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

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  2. #2
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

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

    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

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  3. #3
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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?

  4. #4
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

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

    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".

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    Jun 2015
    Location
    Ontario, Canada
    Posts
    1,618
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

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

    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.
    Last edited by Micron; Oct 9th, 2018 at 09:34 PM. Reason: clarification
    Tips for posting problems:
    1) "doesn't work" doesn't help. Post error message text/numbers and/or state what's happening.
    2) if posting code or sql, use code tags; specify on which code line errors occur, if applicable
    3) try to be specific; assume we know nothing about your issue - because we don't!

    Make all suggested changes in copies of your database or to its objects.

    "1 out of 1010 people understand binary. The other 1001 don't."

  7. #7
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  8. #8
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    SELECT (relevant fields)  FROM tblProposals
    WHERE Applicant1 = [Forms]![frmProposals].[txtApplicant1]
    OR 
    Applicant1 = [Forms]![frmProposals].[txtApplicant2]
    OR
    ...
    OR
    Applicant4 =[Forms]![frmProposals].[txtApplicant4]
    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!
    Last edited by Thirith; Oct 10th, 2018 at 05:47 AM.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    16,093
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

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

    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).

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Jun 2009
    Posts
    108
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •