Access Report/Query Conundrum

[Toneloc424]

I am trying to come up with a way to aggregate the on-line courses that employee's have been assigned and/or completed into a manageable report. I need to come up with a query/report that groups each employee by Cost Center, and then shows only the courses they have been assigned and completed, or assigned and have not completed.

I have a table that has employees first name, last name, ID#, Director, Cost Center #, Cost Center Name, Position #, Position Name and then 40 columns of On-line Course titles.

The columns with the course titles have a "C" if the course was assigned and completed, and a "A" if it is assigned but incomplete.

I have no problem organizing the employees by Director and cost center, but I am unable to aggregate the data on their assigned coursework. Any ideas would be greatly appreciated. Thank you in advance!

 

[lameid]

Goto

http://www.datamodel.org/

Click on "Data Modeling Section"

Next Click on 5 Rules of Data normaliztion...

You need to normalize your data. That means adding a table that has an ID representing an employee, course Name, and finally some status field indicating completion etc. Personally, I would use the fact that the record exists as the assignment. Perhaps an assignment date is in order? Then you can append records to this table based on the contents of each of the 40 classes you have. Finally get rid of the 40 columns. Then you it will be easy to summarize this data using the report wizards summary functions.

 

[Toneloc424]

Ok, the normalization makes sense, although I have another question. What would be a way in which I could query the master table in order to have the column headings(the course titles that is) appear as values in the new table? I would then have the current value denoting incomplete or complete in a status column as you suggest. If I could get a query to do that, that's what I would like. I would like to end up with a new table with an ID field(Primary Key), an employee ID#(SS#)field, a course name field, and a status field. That way it would be easy to put it in a report. Each employee would end up with a record for each course they have assigned. That's what I'm going for, but with 1207 records, automating it is key. Any ideas on a query(s)to do this?

 

[lameid]

What you describe is called a crosstab query... Just like you can select update or append query you can select crostab.

This adds a "Crosstab" row to the QBE. In the crosstab row under your course name you need to select Column Heading. For all other fields in your query select row heading except for your Status field. Under status field, select value.

Because Crosstab queries are also aggregate queries, they are not updateable. I hope this solved your problem, otherwise post your situation.