Autopopulate names from table onto form and more

[Kat-ness]

I'm reposting this here from the General Access sub-forum...may be more appropriate here.....

I'm new to the forum and new to creating an Access database. I'm creating a database for applications. There will be quite a list per application for 'contacts' such as applicants, staff, emergency contacts, etc. And there will be repeated contact names that may be in different 'contact types' on different applications.



What I'd like to do is create a 'contacts' table with a 'contacts' sub form (button or tab) on the main application form for managing all contacts with common fields that will autopopulate if the name already exists, and also automatically update other applications if data (such as a phone number) changes for that individual. I also want to be able to enter in new records for new contacts as well.

However, one 'contact' may be 'staff' in one application, and 'staff' and 'emergency contact' in another. I don't want the 'type of contact' (staff, emergency contact, applicant, etc.) to change per application or line on the application per individual.

Is this possible? And can anyone offer some ideas as to how I might do this? Thansk!

[ssanfu]

Start by getting paper and pencil. Draw you tables and fields/types. Add the relationships. Post it here to have it reviewed for normalization (if you want suggestions).

Also post some example data for testing the concept.

If your table structure is not correct, you will have a hard tome designing your queries, forms and reports.

[June7]

All you want is possible. First step is correctly defining data relationships and designing tables.

Some of what you want might require code but it is possible to build a database without any programming.

Review the 'sticky' thread tutorials at http://forums.aspfree.com/microsoft-access-help-18/

[Kat-ness]

Thanks Ssanfu and June7. I don't think I want to get into code if I can avoid it as it sounds too advanced for me.

If I break this down further are you able to give more specific steps? I want to create a 'Contacts' table and relate it to the main application table. The contacts table will have fields like first name, last name, phone number, email, department, etc. And there will be an additional field titled "Contact type". The type will be things like...staff, emergency contact, etc.

There will be repeat names on one application (this is according to the paper form I did not create). For example, there could be 5 staff and 2 of them are emergency contacts. One (or more) of these could be on another application as just staff, not emergency contact, but still be an emergency contact on a different application.

I'm wondering how to get Access to search to see if a contact is already in the database and autopopulate by name when entering a record on the form. More importantly I'm also wondering how it would be possible to have different contact types for the same contact person on different applications.

I can create the rest of the contacts table. It's this one issue with changing contact type that is confusing me. Any ideas on how I can make that work?

[June7]

So each application will have multiple contacts and each application could have multiple same type contacts.

Consider:

tblContacts
ContactID (pk)

tblApplications
AppID (pk)

tblAppContacts
AppID (fk)
ContactID (fk)
ContactType

[Kat-ness]

Of course! That makes so much sense. Thanks June. so PK stands for Primary key, correct? What does fk stand for? And how would I relate the Contact type table in order for the varying types to match up with the application and not just the contact? Would it be a many to many relationship and would I relate it to the contact ID field in the contact table AND the AppID field in the Application table?

I'm actually really pleased to have this opportunity to create a database. I'm finding Access and it's capabilities rather fascinating and I've only just scratched the surface of what it can do. I sure appreciate your help and this forum to help me learn this stuff.

[June7]

pk/fk = primary key / foreign key

Yes, this is many to many relationship, hence the 'junction' table.

You have a table for the contact types? You can either set the type field as pk and save that to tblAppContacts or save autonumber ID. How long are these type values? If they are short I would just save the actual descriptive value and not use autonumber ID. Less complication in queries. Lookups can be annoying. I use them only when I absolutely have to and NEVER set them up in tables. Review: http://access.mvps.org/access/lookupfields.htm

[Kat-ness]

Hi June,

Thanks for this! I could be wrong but I thought it would make more sense to have a separate 'contact type' table since the same contact may be multiple contact types on one application and then a different contact type in a different application. Make sense? I was going to set the contact types as check boxes...there's about 5 of them. Can I use the contact type table as a junction box if I put contactID and Application# in that table as foreign keys....and use contactID in the contacttbl as pk and application# as pk in the applicationtbl? Do I then not need a primary key for the contacttypetbl..? And will this still allow me to have the varying contact types per application?

Or are you saying in my contacttypetbl I could just have the one field (contact type), list the types as rows to that field (which are things like...staff, emergency contact, etc.) and set those values as the primary key in the table, and then use that as the foreign key in relationship to applicationtbl applicationID pk, and contacttbl contact# pk?

Also with regards to lookup boxes...is this what I would need to do in order to get things to autopopulate? I suppose I could just type out the information over and over but it seems extraneous. Won't that then make multiple rows on my contacttbl with the same contact person? Basically I want to do things as simply as possible without causing other future issues.

[June7]

I recommend against checkboxes for the Type info. This does appear to be a simple solution for data entry but can seriously complicate data output.

My suggestion is 'junction' table to associate contacts with applications. Yes, there will be multiple rows for the same person but for different applications and types. Yes, use comboboxes to 'autopopulate'.

However, the decision on approach is up to you.

[Kat-ness]

Thanks June.

[orange]

If you like Access (database concepts etc) you may find the following info of value:

Here is an article that deals with database design concepts (well worth the read)
http://forums.aspfree.com/attachment...2&d=1201055452

Once you have finished that you may wish to watch these in sequence, or bookmark the links.

These free video tutorials describe the processes of data modelling, normalization and entity relationship diagramming. There are other videos, but this group is by the same presenter and covers an example situation.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

http://www.youtube.com/watch?v=lXAGQ8vmhCY ERD Part 2

[Kat-ness]

Thanks Orange! I'll have a look at these for sure.

[Kat-ness]

Someone told me what may be a simpler way to do this and I wonder if you see any flaws in this....

Application Table will have the contact type fields.
Staff 1
Staff 2
Emergency 1
Emergency 2
etc.
Contact Table will have the contact information.
The relationship is a one to many (app tbl has the primary key, contact tbl has the foreign key)

The application type will then remain with the application and the contact will remain with the application.

Can there be any complications with the many to many relationship? I guess i'm a little nervous to use it because I've never done it before.

[Kat-ness]

Oh, and if there winds up being a need for Emergency 3 (or whatever) I can just add it to the table at the time and have it available for future use.

[orange]

You resolve a Many to many by means of a Junction table.
See June7's comment in post #9.

if you want to see what this entails watch the video at
https://www.youtube.com/watch?v=7XstSSyG8fw