Employer Database Form Issues

[Jenaccess]

Hi All,

I'm running Access 2010, though I'm very familiar with 2007 as well. At my job I've been tasked with creating an Employer Database. The database is supposed to provide the name and address of the employer, as well as accomodate multiple supervisors tied to each employer. I think I've got that part covered. I created an employer table, a supervisor table and a junction table called employersupervisor.

Here comes the tricky part:
I also have to figure out how to track clients and job placements for each supervisor. The way I have it set up is my main form is the employer form, and I have a subform for the supervisor (linked by employer id). My original idea was to put a command button on the supervisor subform and have a clientjob form open, where I'd be able to add new clients and job details for each one such as job title, start date and end date. The client form would not be a subform per se, but I would add some code in the onLoad and onCurrent events to make it act like one.

However, the form is very buggy. It will either refuse to add new records, refuse to autofill the supervisor (even though the SupervisorID field is in both forms), and will sometimes change all client records to the current supervisor. All I basically want is when I click a supervisor, I want to see one client at a time for that supervisor and the job details for that client. I also want to be able to add new clients and new jobs at that point without having to go through a new form. Is there any way to do this? Or do you believe my approach is flawed? I would truly appreciate any advice as I'm definitely stuck. Thank you!

 

[HiTechCoach]

It would really help if you could post a sample of your database with sample data. Nothing confidential.


I would have these three tables:

Companies (Clients)
People

Employees - this is a Junction Table between Companies and People that establish the supervisor relationship.

tblEmployees
EmployeeID - Priamry Key
Employee_CompanyID - foreign key to the Company table
Employee_PeopleID - foreign key to the People table
EmployeeStartDate
EmployeeEndDate
Employee_PositionID - foreign key to look up table(i.e. supervisor, etc)

 

[Jenaccess]

Thank you so much for your reply. I'm attaching a copy of my database with one sample record. I've been trying to figure out the best way to tie client and supervisor information. Right now, I have an option to add/view client. Then once the name has been added I want to be able to find the client in the employer/supervisor page and add the job details that way. It's a bit of a mess, and still only first draft quality, but hopefully it's enough so you'll be able to see where I was trying to go with it.

 

[HiTechCoach]

<<Or do you believe my approach is flawed?>>

I am thinking that the reason this is so difficult to create the needed forms is that the tables design is what is really flawed.

I really don't follow/understand the relationships between the tables.

You have tables with only foreign keys which leads me to think that you have over normalized the design.


My opinion is that you really need to take a step back and work on the table design. Do not think about how the forms will look at this point.

from looking at your database I am not 100% sure I know what it is your are trying to model.

What I see so far is that you have these major objects:
1) Your business
2) People
3) Companies
4) Jobs

Jobs I am not really sure about.

Am I close?

It would really help to understand more about the process that you are trying to management.

 

[Jenaccess]

HiTechCoach,

Thank you for this honest critique. I have such a problem with normalization. I’ve gotten better with it, but still have a hard time and tend to either over normalize or not normalize at all. Is normalization something you either get or you don't, or can it be learned? I know this isn’t part of the question but if you could tell me why I over normalized, that would really help me understand how to do things correctly.

I got the specs directly from my colleague, so my modeling is trying to follow what she asked for. I’m not sure whether that’s the best approach or if there’s a better way. Here’s what was requested and what I’m trying to accomplish:

1. Keeping track of the companies that employ the clients in our program. I also need to capture the addresses of the companies in order to generate mailing labels.

2. Keeping track of the people within those companies who supervise the clients we place. The data model has to be able to accommodate multiple supervisors in each company.

3. Keeping track of the actual clients we place in each company and with which supervisors.

4. Keeping track of each job the client has with a company, as well as the begin and end date of each job.

This is how I planned it:

Company →Supervisor → Client → Job Title →Job Begin and End Date

Ideally in a query I’d be able to pull up a supervisor at any given company and know how many clients he/she placed in any given time period.

Note: At this time, we're not seeking to capture client information or demographics, just what company they were placed at, what supervisor they are placed with, what job title they held, and when they began and ended each job.


Does this make sense?

Regards,
Jen

 

[HiTechCoach]

In my opinion Normalizing the design of a database is an art. Like any art you only get better with lots and lots of practice.

I look at client as a category or relationships between People and/or Companies.

In your case who are clients?

The list of requirement you have tell you want the UI needs to look like and what reports may need to come out. This does not directly reflect the table design.

 

[Jenaccess]

In my opinion Normalizing the design of a database is an art. Like any art you only get better with lots and lots of practice.

I wish I knew where to start. I practice and get it wrong, then end up not sure where to go. It seems like unless the normalization/table structure is right nothing else will be.

I look at client as a category or relationships between People and/or Companies.

In your case who are clients?

So are you saying clients and supervisors should be under the general category of "people" with field to distinguish them? I've toyed with the idea of seperate client and supervisor tables, and also toyed with just one "People" table that encompassed clients and supervisors. My clients are the people who are placed in the jobs. The supervisors are those who oversee the clients, and the company oversees the supervisors, if that makes sense in terms of relationships.


The list of requirement you have tell you want the UI needs to look like and what reports may need to come out. This does not directly reflect the table design.

How can it not? Shouldn't data be modeled for optimal use in extracting it according to the specifications? Or is that the wrong way to look at it? If so, how do you think I can best design the tables correctly?

My problem is that I tend to over analyze and see a many-to-many relationship in everything, hence about 50 junction tables . I don't know whether this has to do with majoring in English Literature, where nothing is clear cut, but it's a real problem. I have a hard time making design decisions because I see everything as right, and at the same time everything as wrong.

Does that make any sense? I'd certainly appreciate any advice you can give me on properly designing the tables.

 

[HiTechCoach]

The list of requirement you have tell you want the UI needs to look like and what reports may need to come out. This does not directly reflect the table design.

How can it not? Shouldn't data be modeled for optimal use in extracting it according to the specifications? Or is that the wrong way to look at it? If so, how do you think I can best design the tables correctly?

The table structure should be for the optimal storage not extracting of data. Queries are use for the optimal extracting of data.

I find that a good understand of SQL and how to create queries helps with the understanding of a good table structure.

So are you saying clients and supervisors should be under the general category of "people"

No. People are not a category. People are a major Entity/Object in the model.

A category is an attribute of on Entity/Object modeled in the database.

A database really only has two things is models:

1) Entities/Objects. People, Companies, Products, etc.

2) Transactions. This is the activity between the Objects/Entities.

Customers > Orders > Product(s)
or
Entity > transaction > Entity

My problem is that I tend to over analyze and see a many-to-many relationship in everything

I never think of or see many-to-many relationship. Only think in terms of one-to-many relationships.

It sounds like you are trying to make a junction table where a query should be used. This relates back to my previous comment on having a better understanding of SQL and queries.

 

[Jenaccess]

I've taken a class in SQL, though I'm by no means a wizard. Here is my attempt at renormalizing according what you've taught me. Am I at least on the right track?

tblCompany
CompanyID (autonumber, primary key)
Companyname
Companyaddress
Companycity
Companystate
Companyzip

tblPeople
PeopleID (autonumber, primary key)
LastName
FirstName

tblEmployees
Employee_CompanyID - foreign key to the Company table
Employee_PeopleID - foreign key to the People table
EmployeeStartDate
EmployeeEndDate
Employee_PositionID - foreign key to look up table(i.e. supervisor, etc)

tblPositions
PositionID (autonumber, primary key)
PositionTitle

I took the tblEmployees design right from your first post. I see it has three foreign keys, but no primary key. Should I include one called EmployeeID? My practice is to always use an autonumber for my primary key, so I can set it and neither myself nor my users have to worry about it. Is this okay?

Thanks for your help!

 

[HiTechCoach]

Yeah ... I think you are really getting this stuff.

Well .... here is what I posted originally:

tblEmployees
EmployeeID - Priamry Key
Employee_CompanyID - foreign key to the Company table
Employee_PeopleID - foreign key to the People table
EmployeeStartDate
EmployeeEndDate
Employee_PositionID - foreign key to look up table(i.e. supervisor, etc)

Yes you want a primary key on every table. I also called it EmployeeID

 

[HiTechCoach]

This might also be helpful:

http://www.rogersaccesslibrary.com/forum/topic238.html

 

[Jenaccess]

Thank you for the links! I'm kind of the self-appointed tech person/database builder in my department, and I want to do it right and learn as much as I can. You're so kind to help me out the way you have.

I'm glad you think my normalization process is coming along. I just remembered that my design also needs to accomodate multiple phone numbers. Would it be as easy as adding an extra phone number table or do you think it would require a total redesign?

Also, as far as sql goes, is it better to define relationships at the table level or the query level? What I mean is, should I create the tables and define the relationships, or should I just leave the tables themselves unjoined and just handle it in the query? Or does it not matter?

Thanks again!

 

[HiTechCoach]

I am really glad you are taking the time to learn and want to make sure that teh table design is correct.

Thank you for the links! I'm kind of the self-appointed tech person/database builder in my department, and I want to do it right and learn as much as I can. You're so kind to help me out the way you have.

I'm glad you think my normalization process is coming along. I just remembered that my design also needs to accomodate multiple phone numbers. Would it be as easy as adding an extra phone number table or do you think it would require a total redesign?

For my People table I haev PeopleContacts child/sub table. Each record holds a single phone number, email, etc. This makes it easy to have as many as you need for each person.

Also, as far as sql goes, is it better to define relationships at the table level or the query level? What I mean is, should I create the tables and define the relationships, or should I just leave the tables themselves unjoined and just handle it in the query? Or does it not matter?

I indubitably recommend Enforcing Referential Integrity fir all relationships between tables. This does require you to define the relationships in the Relationship window. When you define the relationship you will need to select the check box for Enforce Referential Integrity.

 

[Jenaccess]

Sorry this is such a late reply. Things have been crazy, but I just wanted to make sure I said thank you for all the help you've given me. I have a much clearer understanding about how to proceed, and the knowledge will serve me well for future projects. Thank you for what you do on this forum!

 

[HiTechCoach]

You're welcome. Glad we could assist.

And thanks for the update.

Much success with your future projects.