Wisdom wanted. . .

[WebFaktor]

I've got a problematic situation that I think calls for something like a crosstab query to resolve it???

In a nutshell, I have 2 columns of information that I need to relate.
1st column = Course Name (one)
2nd column = Time Element (many)

The application I'm designing needs to accommodate an unlimited number of Course Names. Each Course Name is comprised of differing (smaller) training type events. The time required for Each of these (smaller) training type events to be taught is known and is recorded in the 2nd column, i.e., Time Element.

My Problem then is to add up the Time Elements associated with each Course Name - but I need to find a way of expressing it within reports that is automated to accommodate the new courses entered into the database. . . meaning, what I'm constructing is a prototype database that will later be transformed into Coldfusion and made operable over the internet.

Thanks in advance,

Michael

 

[Tonyi]

I suspect I have missed the point of the question. As I read the question you want to express the sum of the time elements for each specific course.

This is very simple to do.
A simple query on the table,
Add both fields to the query
Press the sum button, (The sigma symbol)
Use "group by" for the course
Use "Sum" for the time.

This can be a separate query, or can be imbedded in the form or report...

The result is "SumofTime Elements" which you can use as a field.

 

[Pat Hartman]

There is a hierarchial relationship between course and training event. Therefore, you need more than a single table to properly represent this structure. You need a table to define courses. And a second table to define training type events for those courses along with the time element.

tblCourse:
CourseId (autonumber primary key)
CourseDesc
etd.

tblTrainingEvent:
EventId (autonumber primary key)
CourseId (long integer foreign key to tblCourse)
EventName
TimeElement

Make sure that you use column and table names that will be acceptable to Cold Fusion. i.e. no embedded spaces, no special characters (except the underscore), each name starts with a letter, length of name within Cold Fusion requirements.