Not sure how to proceed

[kbrooks]

I have a database that I've been struggling with, and thought I had it all figured out. But I've run into a big roadblock and I'm not sure how to get around it.

The database is for tracking applicant and account information for our financial assistance program (FAP). One table gathers applicant info and one the account info. The applicant must apply for FAP periodically, as it is based on family size, income, etc...which can all change. Therefore, I included a date of request field and a date of determination field in the applicant table.

The other table gathers account information.....total charges, 3rd party payments.....and then calculates the fap payment based on those fields, plus the approved percentage from the applicant table. The applicant's ss# is what joins the 2 tables.

Each applicant could have multiple accounts, so I have the applicant data in a main form and the account data in a subform, and I thought it was working great. But I have now realized that if the applicant applies again, as soon as you enter their ss#, it brings up all the previous accounts, family size, family income, etc, etc. I'm not sure if I need to create a new field joining maybe the ss# and date of request, or really how to proceed.

Each applicant can have more than one date of request.
Each date of request may have many accounts.

Hope that makes sense. I will attach a screen shot of my form, maybe that will help. Thanks in advance for any direction you can give me!!

 

[BigJimSlade]

Hi kbrooks!

It sounds like you need to add another field as you had suggested.

Using the Social Security Number as your primary key will give you all the records associated with the SS#. You probably want to create another field called "Account #" or something like that, and use the account number to determine the records shown in the subform (because the SS# is no longer unique, it now represents more than one account).

Hope this helps!

Big Jim