I have to create a database of all employees, the training classes they have taken, and when they have taken them. From that information I then need to be able to determine what classes they are missing, and if they are due for a refresher. Currently this information is stored in excel, but across multiple workbooks (one for each class). Do you think, excel experts, this is something I can accomplish in Excel or would I be better served in Access?
Hi JennOlsen,
You would be better with Access, especially if this situation is going to grow in the future.
Access is a 'Relational Database' application & will cope with 'one to many' relationships where Excel cannot do this as well.
What I mean by 'one to many' for example is that you will have data where one Employee has more than one Training Class.
Excel is great, but Access is designed to cope with the sort of features you'll need.
Hth
Remember you are unique, like everyone else
Thanks. That's what I was afraid of. I'm not as familiar with access. Off to the access forum!
I can answer Access questions too.
I think my problem is that the actual creation of the db is not making sense to me. I keep reading tutorials and it's just not clicking.
Have you established what tables you need and how will they be related?
Once you've done that creating the database should be straightforward.
As far as I can see the basic structure would consist of 3 tables, employees, courses and employee/course.
The employee/course table would link the employee and courses table.
If posting code please use code tags, see here.
Thanks. But I think I need more than 3 tables. I have my tables set-up, I just can't get the relationships to work. That's where I"m stuck currently. I'll head over to access for further assistance.
Thanks again!
What tables do you have?
tblClasses
ClassID
ClassName
RecertFreq
tblEmployeeClasses
EmpID
ClassID
ClassDate
tblEmployees
EmpID
First
Last
Department
PositionName
tblJobHistory
EmpID
PositionName
ChangeDate
tblPositions
PositionID
PositionName
TblPositionsClasses
ClassID
PositionID
What I suggested was really the basic tables that you would need.
What problems are you having with the relationships?
Looking at what you've posted you seem to have things set up properly, primary key/foreign key wise anyway.
Trying to establish the first relationship from tblEmployees (EmpID) to tblEmployeeClasses (EmpID), I get the following:
Relationship Error.jpg
I also lost all my leading zeros when I imported my PositionID and PositionName into my tblPositions. Those are also important and I don't know how to get those back,
I figured this one out. I updated the format.Originally Posted by JennOlsen
Are the IDs in the table coming from the original data?
Are you using them for primary keys in the tables?
EmpID in the tblEmployees is the PK.
the tblEmployeeClasses is just a joining table so there is no PK.
Here's a picture of all the relationships as it stands right now. I think the one I'm having problems with is the last one I need to do.
Relationships.jpg
Last edited by JennOlsen; 12-30-2013 at 04:50 PM.
tblEmployeeClasses really should have primary key, you can use an AutoNumber field.
In might also be a good idea to add AutoNumber fields in the employee and classes table and use them as the PK.
That would help with referential integrity and setting up relationships between the tables.
I was told that joining tables don't need a PK... I'm not having any trouble with the relationsships of any other tables just that particular one.
All tables should have a primary key, even junction tables.
As for the problem with a relationship, which tables is it and which fields are involved?
Also, what are the data types of those fields?
I got the relationship to work. I just needed to delete the data I had in there already, and then do the relationship.
My current problem has to do with importing some exsiting data from excel into the tables. When I do it, I get the following error: Import Error.jpg
I'm pretty sure it has to do with the properties of my relationships. I've tried reading up about relationships and it isn't helping me any. The properties of all my relationships are: Edit Relationships.jpgJoin Properties.jpg
You should import the data and clean it up if required before setting up the relationships.
By the way, you still haven't indicated what field types the primary keys are.
So the order in which to do things is
1. create tables
2. data entry
3. create relationships
4. create queries
?
My PK's are all numbers - set up on either AutoNumber or Long Integer
Moved from Excel Charting & Pivots (although now solved)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks