Looping inside query 1

[SirTECH]

I need to print schedules stored in a table "tblSCHED". The problem is, for a different person, there is a different start point. If the selected schedule is 9 weeks in length, and the start point is week 6, then I want to display the weeks in this order 6, 7, 8, 9, 1, 2, 3, 4, 5.
WHEN I USE...
"SELECT tblSCHED.* FROM tblSCHED WHERE WEEKNUM >= STARTPOINT"
I only get results to EOF.
I wrote a function that gets one record at a time, then loops until the total weeks desired is achieved. But this method is rather slow.
Any help would be appreciated.

 

[Ashank4vba]

Maybe you could use 2 queries?

SELECT tblSCHED.* FROM tblSCHED WHERE WEEKNUM >= STARTPOINT ORDERBY WEEKNUM

SELECT tblSCHED.* FROM tblSCHED WHERE WEEKNUM < STARTPOINT
ORDERBY WEEKNUM

 

[lespaul]

how do you tell what the start point week is?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[SirTECH]

The start point is determined on a form by user input. If I use two queries, how do I output that to a report? Would I need subreports?
Thanks for the feedback.

 

[lespaul]

What if you tried:

SELECT * FROM tblSCHED ORDER BY iif(WeekNumber >= StartingPoint, 0, 1), WeekNumber


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[SirTECH]

The iif(...) test worked perfectly!
Any idea how to loop through the query to show 52 weeks?
Thanks for the help!

 

[lespaul]

Any idea how to loop through the query to show 52 weeks?

I guess I don't understand what you mean?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[SirTECH]

I want to show a selected schedule for 52 weeks in a report. There are several schedules with varying number of weeks in the tblSCHED table.

The iif(...) statement shows the proper number of weeks from a selected startpoint, for a selected schedule (which I am most grateful for that solution by the way).

Using the query;
SELECT * FROM tblSCHED ORDER BY iif(WeekNumber >= StartingPoint, 0, 1), WeekNumber WHERE SchedID = "selected schedule"
displays the number of weeks in the selected schedule, then ends.
Now, I'd like to display a report that repeats the schedule/query 'n' times until 52 weeks are filled.

 

[lespaul]

you could show me the data in your table and what you want to show in the query?

Like
Thread701-650243
or
Thread701-676215

thanks, that will help.

leslie

 

[SirTECH]

One (short) 3-week schedule in the table would be...

SchedID WeekNum Mon Tue Wed Thu Fri Sat Sun
NIGHTS 1 N19 N19 N19 N19 N19
NIGHTS 2 N19 N19
NIGHTS 3 N19 N19 N19


An example of what I'd like displayed using...
selected schedule = NIGHTS
start point = 2
beginning current week.

Weekof Mon Tue Wed Thu Fri Sat Sun
July 25, 2005 N19 N19
Aug 01, 2005 N19 N19 N19
Aug 08, 2005 N19 N19 N19 N19 N19
Aug 15, 2005 N19 N19
...
DEC 26, 2005 N19 N19 N19