New to Access - difficulties with forms (1 Viewer)

Hi all:

New Access user and new member here. I'm not entirely sure my issue is with forms, but thought I'd start here. I am trying to create a database that tracks applications to a program. On the surface, it seems fairly simple. An application is received and contains a host of basic identifying information. Each applicant has a unique nine digit identification number. I need to track the status of these applications and my issue involves multiple applications for a single person. That is, one individual can submit multiple applications across time. For example, John (ID 999999999) submits an application on 2010.01.01 and it is not accepted. He then re-applies five years later 2015.01.01 and is accepted.

For some reason, I can't create a relationship and form which allows for me to track multiple applications from a single person. Again, this seems fairly simple on the surface. The unique 9 digit user ID could be my "anchor" and then attached to multiple applications. I have tried creating two tables - one with basic identifying info (name, age, DOB, etc) and another table that contains application information (date app submitted, status of outcome, etc). I'm at a loss. Any help would be greatly appreciated. Thanks.

Hi. Welcome to AWF! Are you familiar with the term "Normalization?" If not, I suggest looking it up, as it may help shed some light on how to deal with table relationships.


To help you, we might need to see your table structure. You might consider posting a screenshot of your relationship window.

Thanks. At this moment, I do not have any relationships established because each pairing I have tried has resulted in some type of error message. The program is for young students. Each student has an ID number and their address and family info in the first table. I then created a second table with the date of the application, status of the application and if there are any outside parties assisting the family. Fairly straightforward, yes? I have included the nine digit unique ID number as the primary key for both tables, but I did not establish the relationship using these fields. At this time, what I need to do is just establish a basic database that allows for me to manage a situation where one student has multiple applications. I don't see how this can be done with a single table, but if it could, great! Aside form the unique nine digit ID number, there are no recurring data fields in the two tables.

Definitely read up on normalization (https://en.wikipedia.org/wiki/Datab...n is the process,part of his relational model.) which is the process of properly structuring your tables.

Your 2 tables should be in a 1 to many relationship: 1 appllicant can have many applications. For forms, that relationship translates into a Form-Subform (https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b). The main form will be based on the applicant data and the subform will be based on the applications data.

Lastly, you mentioned storing age and DOB. There's no reason to do that and it goes against normalization. Age can be derived from DOB, therefore you don't store it, you simply store DOB and calculate age whenever you need that value. Again, read up on normalization first and foremost.

Hi Tabian. When designing a database, we always recommend starting with the table structure. Form and Reports come later and much easier when you have a good table structure.

theDBguy said:

Hi Tabian. When designing a database, we always recommend starting with the table structure. Form and Reports come later and much easier when you have a good table structure.

Click to expand...

Thanks. I thought I did so, but I'm guessing not well enough! I will review now and see if that fixes things.

tabian22 said:

Thanks. I thought I did so, but I'm guessing not well enough! I will review now and see if that fixes things.

Click to expand...

Another option is to export your table structure to a new database and post a copy here, so we can take a look as well.

Thanks all. Issue resolved (for now). I was not properly executing the form-sub-form layout.

tabian22 said:

Thanks all. Issue resolved (for now). I was not properly executing the form-sub-form layout.

Click to expand...

Hi. Glad to hear you got it sorted out. Good luck with your project.

Do not use the 9 digit number as the PK. It is better to use an autonumber and leave the unique number as a data field with a unique index to prevent duplicates.