?
Solved

relate column

Posted on 2004-11-24
4
Medium Priority
?
253 Views
Last Modified: 2013-11-23
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
0
Comment
Question by:lynnton
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:muzzy2003
ID: 12664786
Hi again Lynnton

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

Author Comment

by:lynnton
ID: 12664819
muzzy2003,

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

Thanks,
Lynnton
0
 
LVL 16

Accepted Solution

by:
muzzy2003 earned 2000 total points
ID: 12664846
Sorry, don't quite understand. I thought you said the solution I posted at:

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21218435.html

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. :)
0
 
LVL 1

Author Comment

by:lynnton
ID: 12675715
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

862 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