Creating table-fields based on a query 1

[Smapty]

Hello,

Is there a way in Access to dynamically build a table based on the records of another pre-existing table? Basically, are building a "course registration" app. We already have a table that houses all the records of the course information (i.e. Title, Start_Time, Stop_Time, Capacity, etc.). Now we want a second table where individual "students" register for courses based on what they want to take during a certain time-slot. The basic structure is simply a RefNum field followed by individual fields for each possible time slot. The possible time_slots change from semester to semester... so we want TABLE B to generate the time_slot fields automatically by looking up DISTINCT start_times in TABLE A.

This is running off ColdFusion and I've played around with the ALTER command to get some results...

<CFQUERY NAME="GetTimes" DATASOURCE="#sourcename#">
SELECT DISTINCT(start_time) AS TimeSlot
FROM Sessions
</CFQUERY>

<CFOUTPUT QUERY="GetTimes">
<CFQUERY NAME="AlterTable" DATASOURCE="#sourcename#">
ALTER TABLE Schedule
ADD "#DateFormat(TimeSlot)# #TimeFormat(TimeSlot)#" dateTime
</CFQUERY>

That does indeed create new fieldnames based on the "Session" table, but I really need this funcionality in the database itself. In addition, it would be best if the "Schedule" db is updated automatically as new time_slots are added to the "Session" table... instead of having to run a query each time.

Any help would be appreciated.

 

[dhookom]

It looks like you want to create a spreadsheet from a relational database.

I believe there are other methods of creating a solution that meets your needs. You have stated what you think you need to do to meet your needs but haven't explained what your base requirements are.

I would not create an application that relied on creating table structures on the fly.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smapty]

The base requirement is that we build that the same database structure is able to be used across differant semesters. Without having to go in and add/delete fields for time-slots.

Please help.

 

[dhookom]

That sounds less like a "base requirement" and more like an ill-advised solution. A properly normalized table structure shouldn't use data values as field names. I try to never let a form or report display dictate a table structure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Smapty]

But there can be up to 50 column fields needed for the diferant time_slots. It seems like a waste of time to manually write those in when the information is already available in another database. We do this about 6 times a year.

 

[dhookom]

I guess I have still not read why you need a wide table. You have a table of courses and presumably a table of students. You need a table of time slots and a table of registrations like:
[blue][tt]
tblCourseTimes
===================
CTID autonumber primary key
CTCourseID link to tblCourses!CourseID
CTStart start of time slot
CTEnd end of time slot

tblStudentRegistrations
=======================
SRID autonumber primary key
SRCTID link to tblCourseTimes!CTID
SRStudentID link to tblStudents!StudentID
SRStatus status of registration
SRRegDate Date registered
[/tt][/blue]

It seems to me this will allow for any number of time slots per course and any number of students per time slot.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Smapty]

I'd never thought of giving each registration its own record. So far I'd planned to have records organized by each "student" registration... with all of his or her choices as field names.

I'll try your way and see if it gets us what we need...