Help! I've just recently managed to normalise my database but when I try to run a report based on a query I can't display what I want to. I want to display staff name and details of their training. I have tb_staff and tb_training_done but can only list the training_done_id rather than the course detail associated with it. What sort of a query do I need? Crosstab?
Help!
- Thread starter tmarsh
- Start date
ColinEssex
Old registered user
- Local time
- Today, 20:25
- Joined
- Feb 22, 2002
- Messages
- 9,451
The course detail should be in a separate lookup table. e.g.
CourseID. . . . . . . .Description
1. . . . . . . . . . . . . Excel Basic
2. . . . . . . . . . . . . Access Advanced
3. . . . . . . . . . . . Word Intermediate
etc
Then link that into the query via the training table
Col
CourseID. . . . . . . .Description
1. . . . . . . . . . . . . Excel Basic
2. . . . . . . . . . . . . Access Advanced
3. . . . . . . . . . . . Word Intermediate
etc
Then link that into the query via the training table
Col
In your report's recordsource, list all training records linked to the master staff record associated with it. Next, in Design View of the report, right click and select 'Sorting and Grouping...'. Group records by the master Staff record and then the report will list the individual training records associated with each staff member underneath it.
Hope this makes sense.
Hope this makes sense.
tmarsh said:
You need to have a join table like this:
tblCoursestaken
CourseTakenID (PK Autonumber)
StaffID (foreign key)
CourseID (FK)
You then add your staff table, course table and the above table into a query. Join the staff table to tblCoursestaken on StafID and the Courses table to tblCoursestaken on CourseID. From there select the fields you want for your report. Save the query and use it as the RecordSource for your report. Group on StaffID and you can get a list of all course taken by staffer.
Yeah you need a new table. In database terms, it's called an 'Associative Entity' because the table contains two 'Many-to'Many' relationships. Each staffer can take Many courses and each course can be taken by Many staffers. It is one of the more complex relational tables you can make, but if this is the way your data relates to itself then it's probably neccessary in your case.
So, does that mean the relationships I have set up are wrong? I have 3 tables:ScottGem said:
tb_staff has staff details
tb_courses_done has training done
tb_courses has course details.
tb_staff to tb_courses_ is 1:n with staff_id as pk in tb_staff and staff_id, courses_done_id and start_date as the tb_courses_done key. Tb_courses is linked to tb_courses_done.
I am not sure what you mean by a new table (suppose this should be in tables?), will it be linked to my existing tables? I've tried searching on joins but can't see anything so far. How do I create the new table?
Thanks.
First I would not use a composite primary key. They can cause more trouble. You can use a multi-field unique index to prevent duplication, but use an autonumber for your PK.
The Coursesdone table looks like your join table. it should have its own autonumber PK and StaffID and CourseID as the FKs. If you want to add more info about when the course was taken, whether completed, grade etc. You can do that.
The Coursesdone table looks like your join table. it should have its own autonumber PK and StaffID and CourseID as the FKs. If you want to add more info about when the course was taken, whether completed, grade etc. You can do that.
