Query to create an organized start schedule

[Wizxon]

Hello everyone, hoping someone here can help me with a problem I've been having with my program. A couple of weeks ago my works computer crapped out and we lost our showtime program, i have been working to recreate it but i have run into a problem i cant seem to fix.

Basically its a 3 page form, the first page allows the user to enter in new movie info, including Movie Name, Rating, and Run Time. The second page allows the user to create a schedule. This "schedule" is really just a long table:

ID | (1)Theater Num | (1)Movie Name | (1)Start1 ... | (1)Start7 | (2)Theater Num ...

The columns then start repeating for the next movie going up to (18), it repeats because our theater can run up to 18 different movies in a day.

Now the problem I am having is changing the table created by the user into an actual start schedule that should look like:

Start Time | Theater | Movie Name



The schedule made by the user does not always include 7 start times per movie either so i want to make sure that if the start time is blank it is not included in the start schedule. I also want to include a list of schedules created by the user (based on the unique ID) so the user can select which schedule they want to organize and print.

Thanks for any help you guys can give and if you need any additional information i will provide what i can.

[June7]

If I understand your data structure, it is not normalized and will require a UNION query to reorganize the data. There is no designer or wizard for UNION, must type in the SQL View window of query designer. Something like:
SELECT [1TheaterNum] As Theater, [1MovieName] As MovieName, [1Start1] As StartTime FROM Table1
UNION SELECT [1TheaterNum], [1MovieName], [1Start2] FROM Table1
UNION SELECT [2TheaterNum], [2MovieName], [2Start1] FROM Table1
UNION SELECT [2TheaterNum], [2MovieName], [2Start2] FROM Table1;

Once you have this query built and saved, you can use it as basis for other queries/reports with sorting and filtering to not retrieve the undesired records (no Start time).

[Wizxon]

I tried using the method you described but because of how many theaters and start times there are this statement gets very long.

SELECT Schedule.[Schedule ID] As ScheduleID, Schedule.[(1)Theater Number] As Theater, Schedule.[(1)Movie Name] As MovieName, Schedule.[(1)Start1] As StartTime FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start2] FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start3] FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start4] FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start5] FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start6] FROM Schedule
UNION SELECT Schedule.[Schedule ID], Schedule.[(1)Theater Number], Schedule.[(1)Movie Name], Schedule.[(1)Start7] FROM Schedule

Basically that repeats (changing the first line because you only need the regular select once) 18 times. Now when i actually put that into the SQL statement i can only fit 8 sets of that code. When i try to add a 9th set i get an error: "The Microsoft Access DB engine could not find the object ". Make sure the object exists and that you spell its name and the path name correctly."

I know for sure the code is right because if i add the code to a different query it runs fine. Is there a limit to how much SQL code can be handled by a single query? and if so is it possible to merge queries to create one big schedule.

[June7]

Yes, there is a limit. I think I once hit the limit with 50 UNION lines. You could try doing two UNION queries and then a UNION of those two.

Better idea is to normalize the data.