relate column

Hi,

Split schedules are two kinds of schedules.
-Let's say on Monday and Tuesday you start at 9:00 and you go home at 18:00
then on Wednesday, Thursday, and Saturday you start at 7:00 and you go home at 16:00

I have an access database with 7 columns for Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday
i.e.

Mon  Tue  Wed  Thu  Fri  Sat  Sun
 Y       Y      Y       Y          Y                        -Y signifies you have a schedule for that particular day
____________________________________________________________________________________________

question,

I have database records with couple of situations, Some have only one instance of 'Y' this means he only comes in one day of the week. We don't want that. How do we squeeze that to search for a record that has four 'Y' ?
i.e.
ID Mon  Tue  Wed  Thu  Fri  Sat  Sun   Related
01   Y                                                   1                <-------the new column related tell's that it is related to another schedule
02                          Y      Y     Y    Y       1                <-------the new column related tell's that it is related to another schedule

A complete schedules are 5 working days.
We can have them working on Saturday and Sunday.
we need to search for these types of schedules and related them to one another.
relate uncomplete schedules (we found 3 working days, find 2 working days and see if they are a match)

Ansi sql standard please.


I'm able to pull up those schedules that are less than 5.

     SQL = "SELECT Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday FROM forecastsched " & _
    "WHERE ( " & _
    " Iif(Monday='Y',1,0) + Iif(Tuesday='Y',1,0) + Iif(Wednesday='Y',1,0) + " & _
     " Iif(Thursday='Y',1,0) + Iif(Friday='Y',1,0) + Iif(Saturday='Y',1,0)  + Iif(Sunday='Y',1,0) " & _
     " ) < 5;"

Thanks,
Lynnton