Report/Query question. In over my head :-(

[Kwertyboy]

Hi all, i am new to the forum and i hope somebody can help me..

Apologises in advance if this post is out of place..but i really do need help!

I have created a database in work that will be used to track training history for employees. It consists of two main tables:

Employees: Name, Start date, Department, Manager
Class: Class name, Class Date, Duration, Attendee1, Attendee2, Attendee3 upto Attendee 24 (Max amount of trainees in a class)

All is working ok, and i can populate the tables fine using forms. I can now view class information, and see who attended what class.

However i need to be able to view an individual employees training history. ie

Joe bloggs has attended the following classes:

Class 1, 12/01/06, 8 hours
Class 2, 14/01/06 8 hours

I think i need to create a form which will allow me to enter an employee name. This name will then be checked against attendee1, attendee2 etc in the class table and it will then flag back any classes that the employee has attended

I hope i have explained this ok, but i really do need some advice on how i can go about doing this..

I have very quickly realised that there is a wealth of knowledge on this site, so i am hoping that you guys may be able to help..

I have been working on this for a few weeks now, and books dont seem to have the answers!!!

Many thanks in advance

Noel

 

[Fifty2One]

probably not the suggestion you want to hear after populating your tables but i tried the same with a roster in the past and found that the
Attendee1, Attendee2, Attendee3 upto Attendee 24
idea is where it would fall apart for me and perhaps this is the snag that is bogging you down.
i suggest instead of
Class name, Class Date, Duration, Attendee1, Attendee2, Attendee3 upto Attendee 24
go
Class name, Class Date, Duration, Attendee, ClassAttendee
make ClassAttendee a combination of the two and index it as no duplicates so you dont have double listings. you can then build your forms or reports with the class portion as the main and the attendee portion as the sub

 

[Jonathan Kok]

How much information is in your database? Hopefully...not much. Because, well...you're going to hate me if there is.

You need to re-design your database. You should, instead, have three tables:
Employees: EmpID(autonumber/PK), Name, StartDate, Dept, Mgr
Class: ClassID(autonumber/PK), Name, ID, Date, Duration
Attendance: AttID(autonumber/PK), ClassID, EmpID.

On your Class form, you would then add a subform for the Attendance table to which you would add the attendants.

You could then very easily do what you'd wanted.

 

[Rickster57]

tables

I guess I would have done this slightly differently where:

A one to many relationship exists between class and attendees

So for each class there are many attendees. That way a simple form with a dropdown box containing all employees could be used for doing a simple search on a query for all classes attended by the employee.

 

[pbaldy]

You may want to rethink your design. If you research "normalization", you'll find that the Attendee1, Attendee2... fields are called repeating fields, and not a good idea. You should have a third table with fields for class id and employee id. If a given class had 10 attendees, there would be 10 records in that table. That makes searching for a specific employee's classes easy.

 

[Jonathan Kok]

So...we all agree

 

[Kwertyboy]

Many thanks guys for such a speedy reply!!!

Luckily most of the data has been imported from excel files so it should not be a big issue modifying my table design..

I think a light bulb has just come on..My design does seem flawed!!!

Looking forward to getting back into work tomorrow to give it a go!!! (what have you done to me )

Have a great Christmas, and a peaceful new year!!!

Take care

Noel

 

[pbaldy]

4 people typing at the same time, with the same thought. Great minds think alike!

 

[boblarson]

It would have been 5, but I was in a meeting

 

[Jonathan Kok]

Pshh...excuses, excuses. Perhaps I should decrease your reputation for being late.

 

[boblarson]

<holding head down in shame>

 

[The_Doc_Man]

If I hadn't been answering someone else at the time, I would have been in the list, too.

I won't hang my head in shame. Around here, if you hang your head, someone will throw a coat over it, thinking you were emulating a coat-rack.