ACCESS 2003 - Creating a report that grows

[CurtisFBuck]

I have a table set up for employee training where the record's primary key is employee number, and each field is a training course. In the data field is the date in which they were trained.

The database is set up to add fields when a new training is added.

I want to be able to run a report to show the last time an employee was trained in a certain course. I'm not sure to have a report set up where it can ADD controls depending on the number of fields in the table.

Any thoughts would be appreciated.

[Rabbit]

This is a poor design for a database. You should not be adding new fields everytime you add a new training. When you don't normalize your data, you run into the very problem you are experiencing right now. Instead, you should normalize your data and redesign your tables, forms, and reports so that it won't matter how many training classes you add. You can read more about normalization from our article here: http://bytes.com/topic/access/insigh...ble-structures

[CurtisFBuck]

Hi Rabbit,

Thanks for the response. This is sort of the conclusion I was getting to in the toying with the database, but didn't quite have the vocab to express my concerns.

Thanks you! I will reconsider how I'm structuring the tables.

[Rabbit]

No problem, if you have any questions about your specific design, don't hesitate to ask.

[CurtisFBuck]

I suppose I do have a question Rabbit.

As I see it, here is my current situation:
I have one table (tblPeople) that contains the key information about each employee, including the primary key: employee number.

An additional table (tblTraining) includes key information about each training, including a primary key: the autonumbered ID field.

Where I'm drawing a blank, is how to "overlap" these two chains to contain information on both, without either being in the "field" section.

That is to say, I want to somehow identify when each employee was last trained in each training. This will allow me to find out when the employee is due for refresher training.

Any thoughts would be great!

[Rabbit]

What you do is create a third table with 3 fields: a person ID, a training ID, and a date field.

[CurtisFBuck]

Well it seems so obvious when you say it...

Thanks, Rabbit.

[Rabbit]

No problem, let us know if you run into anymore issues.