forms/subforms

[jzwp22]

I linked it in this table since job is tied first to the Group.

tblJobQuals
-pkJobQualsID primary key, autonumber
-fkGrpJobTitleID foreign key to tblGroupJobTitles
-fkQualID foreign key to tblQualifications

 

[sunshine076]

I just ran across it there are so many I was way off with my original database. Thanks for your quick response.

 

[jzwp22]

No problem; good luck with your project.

 

[sunshine076]

I have another question when I am working on the tblEmpJobQual I have noticed that when I enter the data in for EmpJobsID I have a total number of records equaling 281 and for the JobQualsID I have total number of records equaling 511. Was this one mismarked and should be something else?

 

[sunshine076]

I think I know what I did the table mentioned above is for the basic requirements for each job I ended up just putting them under the Qual Desc to make it easier for myself. Now wouldn't I just delete this table since I won't be using it?

 

[jzwp22]

To clarify, you have 2 different things going on. First, each job has various qualifications associated with it. That is captured in this table

tblJobQuals
-pkJobQualsID primary key, autonumber
-fkGrpJobTitleID foreign key to tblGroupJobTitles
-fkQualID foreign key to tblQualifications

Once you set up these records, they will stay fairly constant over time (I assume)


Since you are evaluating your employees' performance on each job qualification, you have to tie the correct qualifications to the employee with this table:


tblEmpJobQual
-pkEmpJobQualID primary key, autonumber
-fkEmpJobsID foreign key to tblEmpJobs
-fkJobQualsID foreign key to tblJobQuals

You can populate the table above for every current employee by using an append query once your qualification, employee, job title and group tables are populated (hence the need for tblJobQuals). When you hire a new employee or a current employee changes positions, you will have to run an append query at the time of either of those events.

Since you evaluate an employee multiple times you need a table to record the evaluation for each qualification. That is what this table is used for:


tblEmpQualEval
-pkEmpQualEvalID primary key, autonumber
-fkEmpJobQualID foreign key to tbEmpJobQual
-dteEval (evaluation date)
-fkEvalID foreign key to tblEvaluation

 

[sunshine076]

tblEmpJobQual
-pkEmpJobQualID primary key, autonumber
-fkEmpJobsID foreign key to tblEmpJobs
-fkJobQualsID foreign key to tblJobQuals

Why then do I have two different number of records for the EmpJobsID and JobQualsID. Shouldn't they have the same number of records.

 

[jzwp22]

I don't understand what you are saying. tblEmpJobQual is a table with a certain # of records, but fkEmpsJobsID and fkJobQualsID are fields in that table?

tblEmpJobQual
-pkEmpJobQualID primary key, autonumber
-fkEmpJobsID foreign key to tblEmpJobs
-fkJobQualsID foreign key to tblJobQuals

 

[sunshine076]

ok these fields then stay empty until the data has been entered. I have filled in the rest of the tables with data so I think then if I am correct I move onto creating the queries.

 

[sunshine076]

I am experimenting with queries although I know that I am probably not ready for them but I keep getting errors that says something like mismatch and it brings be to the query design screen. Why

 

[jzwp22]

You should probably not have any records in the tblEmpJobQual until your more basic tables (group, employee, qualifications, jobtitle) are populated. To populate those you will need some simple forms, so I would get that taken care of first. You will need the data in those tables to evaluate whether the queries are returning the correct information.

Usually a data type mismatch error indicates that the datatypes of the fields in the tables that are being joined are not the same. This is especially important for the primary key and foreign key fields that I have identified as pk...ID and fk...ID respectively. The primary key field is always an autonumber data type field, the matching data type is long number, so the foreign key field MUST have a data type of long number. Is that how you have set up the data types of the fields?

 

[sunshine076]

Yes it is all ID have number in the data type field box. This is just getting frusterating for me and I know I can get it. I have been able to create the employee/group id box but when I try to create the next query is when it pops up with the error.
I have also added data to groups, employee, qualifications, and jobtitle from here I should be able to query what I need but it doesn't allow me to

 

[sunshine076]

When I am creating a query this is the message that I am receiving
The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in Deisgn view.

I click ok and then try to view it in datasheet form it will not open the document

 

[jzwp22]

Even though the data type is number it has to be a long number which should show in the lower window in the table design view.

The wizard is unable to open your query in Datasheet view, possibly because another user has a source table open in exclusive mode. Your query will be opened in Deisgn view.

This error suggests that the table is open. Close the table view & try it again. If the error continues, can you zip and post the database and I will take a look at it?

 

[sunshine076]

The link here does not allow me to send it with all my documentation because of the size. but I can try again.

 

[sunshine076]

I take that back here it is. It is a possibility that I could have some of the data mixed up. I will check back later on to see what I have messed up on. I am running Access 2000

 

[jzwp22]

I found 1 error in your relationship to the job table. You inadvertently liked to the description rather than the key field. I corrected that. I also adjusted the joins in the relationships to enforce referential integrity and enabled the cascading updates and deletes. I put in a couple queries to illustrate. The DB is attached.

 

[sunshine076]

TY for your help I see where I went wrong. I am currenlty setting up another query for the evaluation date. However, I have tried it several ways and found that it ends up placing my basic, proficient, Master columns into each separate qualdesc. Each qual should have basic, proficient, master. Did I set up the evaluation table correctly? Or am I missing a table.

THe tables that I am using are Groups, JobTitle, Qualifications, Evaluation and EmpQualEval. I also noticed that if I have entered into the query dteEval nothing shows up except for the headers

Whenever I do get to creating the forms is it wise to use a dropdown menu for the Job Title and is there a way of setting up where it wil pop up the training data when the Job Title has been entered.

 

[jzwp22]

Since a person can have many evaluations (basic, proficient, master) over time, the evaluations must be separate records in a related table i.e. one-to-many relationship. To get the most current evaluation, you need to create a totals query to get the MAX of the evaluation date for each Emp/JobQual.


SELECT fkEmpJobQualID, Max(dteEval)
FROM tblEmpQualEval
GROUP BY fkEmJobQualID

Then create another query to join this back to tblEmpQualEval to get the evaluation detail for the max date for each employee. You can then reference this query in the query where you are bringing in all of your other details.

 

[sunshine076]

I would like for you to look at the updated query that I created just to make sure I read your notes correctly. The new query is called qrytest and qryQual. To me it doesn't look right but I could be wrong.