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!

 

[Leeeon]

If I understand correctly, you have all the different courses in 40 different fields, and you want to bring these all together into a single field for reporting on.

I would use a union-query to bring all these fields together.

Create a select query containing employees #ID (I assume this is a unique identifiying number) and the first of the course details. Add an extra column and enter something like - Course Name: "Driving" or whatever the first course is called.

Now go to the SQL view and you should see something like this:

SELECT [Main Table].[ID#], [Main Table].Course1, "Driving" AS [Course Name]
FROM [Main Table];

Now, to get all the other columns in you need to add to this query.

Underneath the all the code type UNION

Now copy all the code and paste it below the UNION text

Amend 'Course1' to the name of your next field and change "Driving" to the name of the next course.

Repeat with all the other fields (yes this takes a lot of time). Make sure you remove the last UNION statement.

You should end up with something like (but a bit longer!)

SELECT [Main Table].[ID#], [Main Table].Course1, "Driving" AS [Course Name]
FROM [Main Table];

UNION

SELECT [Main Table].[ID#], [Main Table].Course2, "Swimming" AS [Course Name]
FROM [Main Table];

UNION

SELECT [Main Table].[ID#], [Main Table].Course3, "Forklift" AS [Course Name]
FROM [Main Table];


When you run it you'll get all your courses in one field.

This is one way of doing it but as usual with Access there's other ways. For instance, you could do this with DAO if your into programming.

 

[Trainingjason]

In a query use the iif and group on it in a report

jason

 

[Toneloc424]

Jason(and all, could you be a bit more specific. Could you give me an example of what such a query might look like? Here is an example of one row of the table.

ID# - 1
First name - Joe
Last name - Blow
Emp ID - 512345678
Director - Adams, John
Cost Center - 6600
Cost Center Name - ICU
Position # - 201
Position name - RN

Then the course titles follow in the next 40 columns. For example:

Admitting and Registration - "A" or "C"

"A" = assigned but incomplete, "C" = complete

The biggest reason for the existing table format was that the form used to record this information by hand off of a web site worked the speediest in this manner.

So now I would like to normalize this data in a way that would be easier than making 40 make table queries based on each course, adding a "course" column, filling it with the title of the course, replacing all of the "C"'s with "Complete", replacing the "A"'s with "Incomplete" and then finally appending each one of these tables to one new Master Status table. Is there a way too automate something like this?

I truly appreciate all of the help I have been given so far. This is truly a powerful resource. Thank you agin in advance!

 

[Trainingjason]

If you create an iif statement

NameofField:iif([assigned]="A","NameofGroup",iif([Assigned]="B","NameofGroup","Remainders&quot)

Then create a report grouping on NameofField as the expression

Jason