[profdev]

Hi all,

Thanks in advance for any help that you may be able to provide. I'm using Access 2003. 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!

The DB is 3 megs zipped so I can't attach it.

Thank again for any help you can provide.

[OBP]

profdev, welcome to the Forum.
What you want to do is straight forward in Access, you do however have a design fault, in the Recognition table. The Relationship should be 1 to Many from both sides, otherwise when someone has made a nomination they will never be able to make another and anyone nominated will never be able to be nominated again.
Have you Compacted & Repaired the database, that will reduce it's size dramatically.

[jimr381]

Within your HRIS Report table you will want to setup a Manager field. This will be a lookup list that allows them to select their manager via a drop-down list. (I can show you how to pull a query showing people and their current managers via a self-join if you like later on as well) This will alleviate redundant data by not having to have the person's name typed in multiple times. You will then have a nominations table which will have a multiple drop-down lists referencing the IDs from the HRIS Report table. The first will reference the HRIS ID and be for the nominee, the second will reference the ID and will be for the nominator and the third can be for their current manager referencing the ID again. The reason why I am not using the manager's ID from the HRIS Report table is because their manager might change and as such you would want to see their current manager for that nomination and as such will be put in the secondary table. Besides these three drop-down lists you will want to have the nomination date, details about the nomination, IE the checkboxes and all the other good stuff. If you have further questions then feel free to ask.

[profdev]

Thank you ODP and jimr381 for your replys.

ODP -

I adjusted those relationships. I also went ahead and Compacted and Repaired from the Tools --> Database Utils drop down menu but the size of the file didn't change (23 megs before zipping and 3megs after). Any ideas why this is? There's only one row of data in each table. Hmm....

jimr381 -

The HRIS report already has a supervisor column. Are you suggesting I create a new column or just change the properties of the current supervisor column? When I try to make it a lookup column using the lookup wizard I assume I would want to use existing values. The values that I want though are in the same table (this is probably why the self-join query you mention will come in handy)

Also, it looks like then you are suggesting 2 (or 3) tables. I'm not clear what the "secondary" table is. Basically a Nomination Table and the HRIS report table and then possibly this "secondary" table. Could you please clarify?


Thank you very much for your help.

[profdev]

for some reason when I backedup the database, the back-up was significantly smaller. Please let me know if you have trouble opening it.

[jimr381]

What I was specifying would be in two tables. All of the fields I typed about would be in the nomination table. The Nomination table would be considered the secondary table or related table. There are a few names for it. The MDIS Report table will be called the primary or main table.

[profdev]

Thanks jimr -

I've reorganized the database per your recommendation. it's makes a bit more sense now, more straightforward design.

I am now struggling with the form design:

I've figured out how to have a drop down list names but I would like to know...

1) How to create a form field in which people can search for a name in the HRIS table. The HRIS table will contain about 1,000 names so may be a little unruly as a drop down list that you have to navigate.

2) Given a selection in one list (e.g., last name) give options for a first name only related to the last name.

Would this all be done with queries of some sort?

[OBP]

profdev, create a query based on the HRIS table, sorted by Lastname and then Firstname.
When sorting is set, Access queries, sort first by the column to the left and then the next and then the next, so you can sort as many columns as you like. But people are used to seeing names with lastname first and then Firstname next.
Now create a query based on your recognition table, then create a form based on the new query.
On the new query add a combo box using the HRIS query as it's Record Source with the key field (Personnel # ?) as the first column and then lastname and then Firstname, when it shows you the layout set the Personnel # coulmn width to 0 (zero) so that it is hidden.
When it asks do you want to store a column choose Personnel # and tell the wizard to store it in the recognition field for the person nominated.
Copy that Combo and change it's "Control Source" to the person making the nomination.
Combo boxes are very fast at find ing names because when you type in each letter it jumps to the names with those letters in so you can very quickly find the one that you want.

If you need further help to do it, Compact a copy of your new version of the database and post a zipped copy on here, do not leave any personal data in the copy.

[profdev]

OBP - Thanks a lot! That's what I was looking for. I've still got quite a bit to learn considering that was a big hurdle.

I'm sure I'll be back with more questions shortly!!