I can't seem to find the exact answer in the forum. If this has been posted before I would greatly appreciate the link as I'm sure this is a common problem.

Thanks in advance for any help that you may be able to provide. For starters, I'm not sure that what I want to do is plausible. I'm about two into learning about Access and think that my database is correct but can't seem to figure out how to structure a form appropriately. If I am asking too much please let me know what you can help me with.

I am designing a database for a recognition and reward program at my work. I then want to create a form that any employee can fill out when they want to recognize one of their colleagues, with the information they enter populating the database.

Here's what I have for database tables:

HRIS Report - contains all employee data (personnel #, first name, last name, hire date, supervisor, department, email). Personnel # is the pk.

Nominator - contains information about who made the nomination. It will be related to the HRIS report's personnel # with it's own pk (Nominator ID)

Nominee - contains information about who is being recognized. It will be related to the HRIS report's personnel # with it's own pk (Nominee ID)

Recognition - contains information about the behavior being recognized. It will also contain the Nominee ID and Nominator ID and has a 1-to-1 relationship with the Nominee and Nominator tables respectively.

Nominee's Manager - contains the nominees manager and is related to the Nominee table using a pk Manager ID

The ideal form that I can't figure out how to create:

This first part is where I am clueless: An employee opens a form where they can search for the person they are hoping to recognize (I assume this could be done with a drop down list of some sort). When the name is located the personnel number associated with the name populates a text box that is linked to the Nominee Table. They do the same for themselves and for the manager of the Nominee.

They then proceed to fill out the behaviors and check a few boxes realted to organizational goals and the level of impact on the organization that the behavior had (all of this populates the Recognition table).

Finally they click submit and an email is sent to me telling me that someone has been recognized, meawhile the database has been updated!