relate column

Posted on 2004-11-24
Last Modified: 2013-11-23

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

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


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' ?
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;"

Question by:lynnton
    LVL 16

    Expert Comment

    Hi again Lynnton

    Can I have the points here as well? ;)
    LVL 1

    Author Comment


    I wasn't able finish what you've started so I'm asking for other people's opinion.

    LVL 16

    Accepted Solution

    Sorry, don't quite understand. I thought you said the solution I posted at:

    was working perfectly? If you are still having problems with it, please post them so I can sort them out for you. I don't like providing partially working solutions. :)
    LVL 1

    Author Comment


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction While answering a recent question ( in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
    I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now