Start Free Trial

asked on

Relate

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

Does the related column simply store a 1 to indicate related, or are there many values in this column in pairs? Is there some foreign key column in this table to an employee table of some sort?

ASKER

muzzy2003,

Yes, we use numbers to identify which record is related too. so we can pull up the related record easily.
what do you mean by foreign key column in this table to an employee table?

Thanks,
Lynnton

I mean how do you identify whose schedule this is/these are?

ASKER

muzzy2003,

hmm, they haven't been assign to anyone yet, they are just created to satisfy a required.

required=number of people they need for every half-hour.

those schedules will be assigned in the future though...I think this is another story ???

I'll use the related column to identify that they are two kind combined to one (split schedules)

Thanks,
Lynnton

OK - I think I understand the question now. Does this do what you want?

SELECT s1.ID, s2.ID, s1.Monday, s2.Monday, s1.Tuesday, s2.Tuesday, s1.Wednesday, s2.Wednesday, s1.Thursday, s2.Thursday, s1.Friday, s2.Friday, s1.Saturday, s2.Saturday, s1.Sunday, s2.Sunday
FROM forecastsched s1, forecastsched s2
WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5
AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5
AND s1.Monday + s2.Monday <> 'YY'
AND s1.Tuesday + s2.Tuesday <> 'YY'
AND s1.Wednesday + s2.Wednesday <> 'YY'
AND s1.Thursday + s2.Thursday <> 'YY'
AND s1.Friday + s2.Friday <> 'YY'
AND s1.Saturday + s2.Saturday <> 'YY'
AND s1.Sunday + s2.Sunday <> 'YY'
AND s1.ID < s2.ID

ASKER

muzzy2003,

Amazing, not sure how that works though, if you could kindly explain.

Thanks,
Lynnton

OK. It takes pairs of records from your forecastsched table (I have used aliases of s1 and s2 for the two instances of the table I use). Firstly it filters for ones with fewer than five days in the first, then for the total number of days between the two equalling 5 (this is to avoid 5 - 0 matches). Then it concatenates the values for the two Mondays, and excludes pairs where both are flagged with a Y, so that you filter out schedules that would both involve a Monday; then for Tuesday; etc. Finally, it removes the duplicates you would otherwise get by forcing an ordering on the two ID values. Gotta dash - train pulling in to station ...

ASKER

muzzy2003,

There's a column called related, we need to tag those schedules that we are going to relate. using numbers would be easier?

Thanks,
Lynnton

Train in, and I'm now at work. :)

If you are just going to do it using a column like that, a number's as good a solution as any. You didn't seem to be sure what I meant when I asked about foreign keys earlier on - might be an idea to do a search on the Internet for things like: foreign key relationships, referential integrity, normalisation. This will give you a much better idea of some other ways to achieve the same thing. (Apologies if I have misread your confusion and you know this stuff already.)

ASKER

muzzy2003,

Sorry for the late reply, I bought something from seven-evelen. Thanks very much for the information, I'll do that after this, can we still achieve this though?
in ansi sql please.

Thanks,
Lynnton

Do you mean you want SQL to actually associate the pairs automatically? Does the query work that reliably?

ASKER

muzzy2003,

Yes, associating the pairs using sql query. Tagging two records with same numerical value to create a complete schedule.

complete schedule = 5 working days

incomplete records = 4 working days, 3,2,1

If we relate them together (those imcomplete schedules) we can actually create a complete scheudles.

Thanks,
Lynnton

OK. In SQL Server this would be easy - I could write a cursor to go through the results of the query and assign the values. In Access, though, not so easy. This might work for you though. If you run this query, you get a list of undate statements, which you can then run to make these associations. Do it on a copy of your database just to see if it works!!!!

SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE ID IN (' + s1.ID + ', ' + s2.ID + ');' + Chr(13) + Chr(10)
FROM forecastsched s1, forecastsched s2
WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5
AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5
AND s1.Monday + s2.Monday <> 'YY'
AND s1.Tuesday + s2.Tuesday <> 'YY'
AND s1.Wednesday + s2.Wednesday <> 'YY'
AND s1.Thursday + s2.Thursday <> 'YY'
AND s1.Friday + s2.Friday <> 'YY'
AND s1.Saturday + s2.Saturday <> 'YY'
AND s1.Sunday + s2.Sunday <> 'YY'
AND s1.ID < s2.ID

Sorry, undate = update obviously ...
Not enough caffeine yet this morning ...

ASKER

muzzy2003,

Sad to say, it return an error msg, no value given for one or more required parameters.

SQL = "SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE ID IN (' + s1.ID + ', ' + s2.ID + ');' + Chr(13) + Chr(10) " & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.ID < s2.ID"

Thanks,
Lynnton

Does this work?

SQL = "SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE ID IN (' + s1.ID + ', ' + s2.ID + ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.ID < s2.ID"

ASKER

muzzy2003,

Runtime error, no value given for one or more required parameters.

Thanks,
Lynnton

OK, just to pin down the location of the typo, what does this do?

SQL = "SELECT 1" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.ID < s2.ID"

ASKER

muzzy2003,

My apology, I don't have an ID column, but I do have a column called forecastID which is a autonumber, will this do?

Thanks,
Lynnton

ASKER

muzzy2003,

The last sql query return a single column, all the values are 1

Tried the second query, returned same error message.

Thanks,
LYnnton

Oh, OK. You listed an ID column in your question, that's why I used it. Yes, replace ID with forecastID throughout.

SQL = "SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE forecastID IN (' + s1.forecastID + ', ' + s2.forecastID + ');' + Chr(13) + Chr(10) " & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID"

ASKER

muzzy2003,

Error message, data provider or other service returned a E_FAIL status.

Thanks,
Lynnton

Does this error as well?

SQL = "SELECT 1" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID"

ASKER

muzzy2003,

That query works fine, no problems, it generated single column with values as 1.

-by the way please ready your comments section.

Thanks,
Lynnton

Hmmm. This one?

SQL = "SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE forecastID IN (' + s1.forecastID + ', ' + s2.forecastID + ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID"

Thanks for the feedback, by the way.

ASKER

muzzy2003,

Error message, data provider or other service returned a E_FAIL status.

Thanks,
Lynnton

Sorry, my Access is rusty. Don't suppose this helps?

SQL = "SELECT 'UPDATE forecastsched SET Relate = (SELECT MAX(Relate) FROM forecastsched) + 1 WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID"

ASKER

muzzy2003,

It worked perfectly, returned a single column with values "UPDATED forecastsched"

does your query works something like this....

seach for those incomplete schedules..
first schedule found has 3 working days Monday, Tuesday, and Sunday
search for two working days can be Wednesday, Thursday, Friday and Saturday
found a match, found our first relation, tag both column's in relate as 1

next search again....
found a match, found our second relation, tag both columns in relate as 2

Thanks,
Lynnton

It tries all pairs of schedules (I'll call the S1 and S2) where S1 is incomplete (fewer than 5 days).

It then filters these results to remove ones where S1 and S2 together come to something other than 5 days.

This isn't complete though, as some of the pairs remaining might, for example, both have a Monday. So it then filters out the ones that both have Monday, then the ones that both have Tuesday, and so on.

The list you have now only contains matches, but it contains matches both ways round - not only S1-S2, but also S2-S1. So it now filters out the ones where the first forecastID is higher than the second, removing these duplicates.

Then, for each pair, it constructs a SQL UPDATE statement that assigns the next available Relate value (careful if you don't currently have one in the table - you might need to manually edit the first UPDATE statement to put in a value of 1 or something similar) to both of the schedules in that pair. The query returns the UPDATE statements. You then need to execute these, either in a single batch or inidividually, to complete the task.

ASKER

muzzy2003,

I wish I had your brain, so that I can come up with those stuff.

Thanks,
Lynnton

You don't really want something this warped, believe me...

ASKER

muzzy2003,

The column relate doesn't have any values for all the records..
so I couldn't query using relate column

Thanks,
Lynnton

OK. This should do the trick:

SQL = "SELECT 'UPDATE forecastsched SET Relate = Iif((SELECT MAX(Relate) FROM forecastsched) = Null, 1, (SELECT MAX(Relate) FROM forecastsched) + 1) WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND s1.Relate = Null AND s2.Relate = Null"

This gives you a list of update statements, they should run fine. Could you try it and let me know if there are any errors?

ASKER

muzzy2003,

Worked perfectly, without errors, it returned a single column with one record null value on the datagrid.

when i open the access database, still no change. relate column are all null.

how many hours till you go home?

me i'm still at work. probably 4 more hours here.

Thanks,
Lynnton

I'm here for another 4 or so. All the Relate values are Null, right? As I said, Access a little rusty, so try this. What we are looking for is a list of UPDATE statements. You can then copy them into a new query window and run them to complete the update.

SQL = "SELECT 'UPDATE forecastsched SET Relate = Iif(IsNull((SELECT MAX(Relate) FROM forecastsched)), 1, (SELECT MAX(Relate) FROM forecastsched) + 1) WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)"

ASKER

muzzy2003,

On the grid it showed single column with data as following

UPDATE forecastsched SET Relate = Iif(IsNull((SELECT MAX(Relate) FROM forecastsched)), 1, (SELECT MAX(Relate) FROM forecastsched) + 1) WHERE forecastID IN (49083, 49229);

I think it's treating that like as 'text' ?

Thanks,
Lynnton

Yes, that's right. Now you need to copy all the UPDATE statements into a new query window and run them.

ASKER

muzzy2003,

I see, your really good at this. By the way Visual basic doesn't have a query window.

Thanks,
Lynnton

If you want to automate it, save this as qryGetRelateUpdates:

SELECT 'UPDATE forecastsched SET Relate = Iif(IsNull((SELECT MAX(Relate) FROM forecastsched)), 1, (SELECT MAX(Relate) FROM forecastsched) + 1) WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');' AS [Command]
FROM forecastsched s1, forecastsched s2
WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5
AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5
AND s1.Monday + s2.Monday <> 'YY'
AND s1.Tuesday + s2.Tuesday <> 'YY'
AND s1.Wednesday + s2.Wednesday <> 'YY'
AND s1.Thursday + s2.Thursday <> 'YY'
AND s1.Friday + s2.Friday <> 'YY'
AND s1.Saturday + s2.Saturday <> 'YY'
AND s1.Sunday + s2.Sunday <> 'YY'
AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)

Then create this subroutine in a module:

Public Sub PopulateRelate()

Dim objRS As Recordset

Set objRS = CurrentDb().QueryDefs("qryGetRelateUpdates").OpenRecordset()
intCount = 0

Do While Not objRS.EOF
Call CurrentDb().Execute(objRS.Fields("Command").Value)
objRS.MoveNext
Loop

Set objRS = Nothing

End Sub

When you run the subroutine, it does the lot for you. Assuming the updates work, which you will soon find out. ;)

Sorry, I thought you were in Access. Does the Access-style answer I've just posted tell you enough to get it going in VB against an Access database?

ASKER

muzzy2003,

Did I get this correctly,
SQL="your query"

RSforecast.Open SQL, cnForecast, adOpenKeyset, adLockReadOnly

Do While Not RSforecast.EOF
Call cnForecast.Execute(RSforecast.Fields(0).Value)
RSforecast.MoveNext
DoEvents
Loop

Set RSforecast = Nothing

error message: operation must use an updateable query

Thanks,
Lynnton

OK. You need to disconnect the recordset otherwise the select and the updates will lock each other. You need to add a couple of things here for this to work. Try changing the code slightly:

cnForecast.CursorLocation = adUseClient
cnForecast.Open

SQL = "my query"

Set RSForecast = cnForecast.Execute(SQL)
Set RSForecast.ActiveConnection = Nothing

Do ... Loop

Set RSForecast = Nothing

ASKER

muzzy2003,

I tried all the tricks in the book, no luck, sorry to let you down like this, need your help.

error message when running the code above,
operation is not allowed when the object is open.

Set RSforecast.ActiveConnection = Nothing <----pointing at this

when i remove the line.
error message: operation must use an updateable query

Call cnForecast.Execute(RSforecast.Fields("command").Value) <---pointing at this

Thanks,
Lynnton

Try replacing:

Set RSForecast = cnForecast.Execute(SQL)

with

RSForecast.Open SQL, cnForecast, adOpenStatic

ASKER

muzzy2003,

Sad to say same result.
Would using a ADODB.Command work?

http://www.aspfree.com/c/a/VB.NET/Visual-Basic-Programming-Much-Ado-about-ADO/4/

Thanks,
Lynnton

Sorry about this. Been doing little but .NET recently.

Set RSforecast = New ADODB.Recordset

RSforecast.CursorLocation = adUseClient
RSforecast.Open SQL, cnForecast, adOpenForwardOnly, adLockBatchOptimistic

Set RSforecast.ActiveConnection = Nothing

Then you should be able to go through the Do ... Loop OK.

ASKER

muzzy2003,

I hate my self, been searching in goole with no luck.
The above code returned same error message.

Any other idea's?

Thanks,
Lynnton

Have you tried the one I posted in the VB area in answer to your related question there? adModeReadWrite?

ASKER

Yes sir, I did.

Set cnForecast = New ADODB.Connection
cnForecast.Mode = adModeReadWrite
cnForecast.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\scheduledb.mdb;Persist Security Info=False"

Thanks,
Lynnton

OK, and that didn't work either? You definitely have write as well as read access to the MDB file and the directory it is in? Here are some more suggestions:

Is forecastID defined as the table's primary key? I have heard that ADO can get confused if the table does not have one. If not, set it as the primary key and try again.

Is the MDB slightly corrupted? Try running compact and repair within Access and trying again.

ASKER

muzzy2003,

Yup, I can add and delete data using sql query, already tried those technique (found them at google), repair and compact and primary key as well.

Don't worry my hopes are way up high cause I know you're the best there is.

Thanks,
Lynnton

Right - mmusante has I think cracked it in your VB thread, and certainly deserves the points there. Change the SQL to this.

SQL = "SELECT 'UPDATE forecastsched SET Relate = # WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)"

Then, change your code like this:

Dim intCount As Integer

...

intCount = 1
Do While Not RSforecast.EOF
Call cnForecast.Execute(Replace(RSforecast.Fields(0).Value, "#", intCount))
RSforecast.MoveNext
intCount = intCount + 1
DoEvents
Loop

Let me know if this works, I'm pretty sure it will.

ASKER

muzzy2003,

It worked !!! When I run it on 400 records, it returned some numerical value on the relate column(some have single instance of relate column), but when i tried it on 8 records, didn't show the same..

single instance = it have numerical value but doesn't have a matching record.

forecastsched table
________________________________________
ID Mon Tue Wed Thu Fri Sat Sun relate
01 Y Y Y Y Y
02 Y Y Y
03 Y Y Y
04 Y Y
05 Y Y
06 Y Y Y Y
07 Y Y
08 Y
09 Y Y Y Y Y

record 01 and 07 are related
record 08 and 06 are related

Thanks,
Lynnton

Phew! Glad to have got there in the end, sorry it took a little while.

Hang on, you mean not quite working, don't you.

In your table, 01 and 07 aren't related, as they add up to 7 days. Agree that 06 and 08 should be.

I've just realised a problem - there's nothing in the list of update statements to prevent IDs being allocated twice. You need to restructure. Put this inside the loop:

SQL = "SELECT TOP 1 'UPDATE forecastsched SET Relate = # WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)"

Now, keep opening this as a recordset, and while there is a row (there'll either be 1 row or none now), execute it. Then re-execute this SQL. Keep intCount, and intCount = intCount + 1 in the loop. Hope that makes sense.

ASKER

muzzy2003,

I'm lost sorry, but here's what i did,

SQL="Your kickass query here"

Set RSforecast = cnForecast.Execute(SQL)

Dim intCount As Integer
intCount = 1
Do While Not RSforecast.EOF
Call cnForecast.Execute(Replace(RSforecast.Fields(0).Value, "#", intCount))
RSforecast.MoveNext
intCount = intCount + 1
DoEvents
Loop
MousePointer = vbDefault
Set RSforecast = Nothing

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

muzzy2003,

hmm, not association happend.. what could be missing..
by the way have you saw the NBA riot?

Thanks,
Lynnton

Did you clear the Relate values entered by the previous attempt first? You need to. Just run "UPDATE forecastsched SET Relate = Null".

ASKER

muzzy2003,

Yes sir, I did, I've create 9 record inorder to further troubleshoot (as posted above).

Thanks,
Lynnton

Hmmm. Does my big query not return anything now? Can you check?

Not seen the NBA riots - news of it not reached the UK yet. What's happening?

ASKER

muzzy2003,

The NBA riot happend three days ago, players were fist fighting with crowd. (visitor's)

Still no change sir.
_________________________________________________Please see below for the code used
Private Sub Command4_Click()
MousePointer = vbHourglass
Set RSforecast = New ADODB.Recordset
RSforecast.CursorLocation = adUseClient
Dim intCount As Integer
Dim blnRecords As Boolean

intCount = 1
blnRecords = True
SQL = "SELECT 'UPDATE forecastsched SET Relate = # WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)"

Do While blnRecords
Set RSforecast = cnForecast.Execute(SQL)
If Not (RSforecast.EOF) Then
Call cnForecast.Execute(Replace(RSforecast.Fields(0).Value, "#", intCount))
Else
blnRecords = False
End If
RSforecast.Close
Set RSforecast = Nothing
intCount = intCount + 1
DoEvents
Loop

MousePointer = vbDefault
End Sub

Yes, but if you just run the first query, does it return any records? You could stick a breakpoint on the Do While blnRecords line and see if the recordset is at EOF or not.

ASKER

muzzy2003,

first pass-

Do While blnRecords --returned true

intCount = intCount + 1--returened intCount as 2

second pass

Do While blnRecords --returned false

When you cleared the Relate column, did you replace all the values with Nulls or empty strings?

ASKER

muzzy2003,

Empty strings. ""

Thanks,
Lynnton

That's why it's stopped working.

Either change:

" AND s1.forecastID < s2.forecastID AND IsNull(s1.Relate) AND IsNull(s2.Relate)"

to:

" AND s1.forecastID < s2.forecastID AND s1.Relate = '' AND s2.Relate = ''"

or update them to Nulls, then try again. Doesn't matter which.

ASKER

Thanks so much muzzy2003.

Phew! Sorted it at last. Glad to have got your problem sorted - it's always good to work on one with so many issues along the way and see it right through to the end.

ASKER

muzzy2003,

One question, what do I edit to change the number of relation?
i.e.
we get less than 4 then relate to create 5

-how do we get less than 3 then relate to create 4

-how do we get less than 2 then relate to create 3

Thanks,
Lynnton

Do you mean that once you've matched as many as possible to create pairs adding to 5 days, you want to then pair as many that are left to add to 4 days, then 3 and so on? All you will need to do is to change the two 5s in the SQL I gave you to 4s, then to 3s and so on. Should be as simple as that.

ASKER

muzzy2003,

thanks, that should do it.

by the way, I'm trying to add a little piece of code, at
# WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _

s2.forecastID should meet the following condition
_______________________________________________________________________________
s2.requiredid > iif(s1.requiredid < 7,0, iif(s1.requiredid + 42 > 48, (s1.requiredid + 42) - 48 , s1.requiredid + 42 ))

doesn't work for me..
here's where i'm at

SQL = "SELECT 'UPDATE forecastsched SET Relate = # WHERE forecastID IN (' & s1.forecastID & ', ' & s2.forecastID & ');'" & _
" FROM forecastsched s1, forecastsched s2 " & _
" WHERE (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0)) < 5 " & _
" AND (Iif(s1.Monday='Y',1,0) + Iif(s1.Tuesday='Y',1,0) + Iif(s1.Wednesday='Y',1,0) + Iif(s1.Thursday='Y',1,0) + Iif(s1.Friday='Y',1,0) + Iif(s1.Saturday='Y',1,0) + Iif(s1.Sunday='Y',1,0) + Iif(s2.Monday='Y',1,0) + Iif(s2.Tuesday='Y',1,0) + Iif(s2.Wednesday='Y',1,0) + Iif(s2.Thursday='Y',1,0) + Iif(s2.Friday='Y',1,0) + Iif(s2.Saturday='Y',1,0) + Iif(s2.Sunday='Y',1,0)) = 5 " & _
" AND s1.Monday + s2.Monday <> 'YY' " & _
" AND s1.Tuesday + s2.Tuesday <> 'YY' " & _
" AND s1.Wednesday + s2.Wednesday <> 'YY' " & _
" AND s1.Thursday + s2.Thursday <> 'YY' " & _
" AND s1.Friday + s2.Friday <> 'YY' " & _
" AND s1.Saturday + s2.Saturday <> 'YY' " & _
" AND s1.Sunday + s2.Sunday <> 'YY' " & _
" AND s1.forecastID < s2.forecastID AND s1.Relate=0 AND s2.Relate=0 AND " & _
" (s2.requiredid > iif(s1.requiredid < 7,0, " & _ <--------------------new condition
" iif(s1.requiredid + 42 > 48, (s1.requiredid + 42) - 48 , s1.requiredid + 42 ))) " <-------------------new condition

Thanks,
Lynnton

Do you get any meaningful sort of error message?

Incidentally, if s1.requiredid is an integer and doesn't contain any negative values, as I suspect, you can replace this whole expression with:

s2.requiredid > s1.requiredid - 6

ASKER

muzzy2003,

no errors, it works same as before. Yes requiredid doesn't have a negative value, range is from 1 upto 48.
with the new code in place, it should relate more wiser now, but it doesn't seem to work...

the code is a condition for getting s2.forecastid, only get ID's that are...

" (s2.requiredid > iif(s1.requiredid < 7,0, " & _ <--------------------new condition
" iif(s1.requiredid + 42 > 48, (s1.requiredid + 42) - 48 , s1.requiredid + 42 ))) " <-------------------new condition

did I place the condition at the right place?

Thanks,
Lynnton

Yes, the code is in the right place. The shortened version I have given you is equivalent, and avoids having to nest the IIFs in the SQL.

So, in what way isn't it working? What are you seeing/not seeing that you shouldn't/should be?

ASKER

muzzy2003,

The relate should now be more intelligent,

in forecastsched i've added a requiredid column, values range from 1 - 48
1 is 00:00
2 is 00:30
3 is 01:00
this is every half-hour up to 48 which is 23:30

requiredid is the start time of thier shifts
i.e.

requiredid is 14, this means he/she comes in at 6:30 and goes home at 14 + 18 (9 hours * 2) =22
then the result should be added another 12 hours (48 in half-hour for requiredid) because law states this.
_______________________________________________________________________________________

s2.forecastid shouldn't get those schedules that are less than 21 hours from start of thier shift's ( this is concerning relate column)

-we can let people come in on monday at 6:30
-but we can't let them on tuesday come in at 1:00 <----------the person here would only have 10 hours of rest before he comes to work.

Please advice.

Thanks,
Lynnton

OK, it's not quite as simple as you are trying to make it then. I'll have to have a think about this one!

ASKER

muzzy2003,

Oh, I didn't realize that, I thought adding that piece of code would do it...

Thanks for the info,
Lynnton

ASKER

muzzy2003,

Hmm, is it a good thing that I am lactose intolerance?
My apology, I thought you've abandoned me.

Yes you are correct, only one required id per record.
______________________________________________________________________________
here's what I tried,
" AND s1.forecastID < s2.forecastID AND s1.Relate=0 AND s2.Relate=0 AND " & _
" s2.Requiredid + 48 - s1.Requiredid >= 30 " & _
" AND s1.Requiredid + 48 - s2.Requiredid >= 30 "

stil shows record where
monday relate #21 works starts at 20:00 and goes home 5:00 (9 hours of work) required id is 41 for 20:00 <----this schedule overlaps the next day

tuesday relate #21 works starts at 11:00 and goes home 20:00 (9 hours of work) he has only 6 hours of rest.

shows the same result even with/without the new condition.

Thanks,
Lynnton

ASKER

muzzy2003,

My apology, it did show a difference, with the added code only 37 relationship where created, without created 39.
Sad to say it still related schedules with less then 22 hours from start time for s2.forecastid
by the way, if required id is from 1 to 7 there shouldn't be any restrictions for s2.forecastid, cause s2.forecastid can be any schedule.

Thanks,
Lynnton

Sorry, mental arithmetic failure on my part. Try changing the 30s to 42s, or the single condition to:

s2.Requiredid - s1.Requiredid BETWEEN -6 AND 6

I had taken 18 units for the 9 hours of work but then added 12 for the 12 hours rest instead of 24!

ASKER

muzzy2003,

You are an amazing guy.
-I hope the girls also say the same thing..

Thanks,
Lynnton

Why is that with required id from 1 to 7?

ASKER

muzzy2003,

when s1.requiredid is at 1 to 7..

1 is 00:00 up to 9:00
and 7 is 3:00 up to 12:00

s2.requiredid can have any schedules since it's always more than 22 hours when it comes to this range( when comparing to s1.required id.
Does the condition you've created also apply this?

Thanks,
Lynnton

But what about the days _before_ these early start times? s2 can't be too late, in case it's before one of these early starting s1 days. I think the between -6 and 6 covers everything.

ASKER

muzzy2003,

Amazing, can I have your brain?

thank you for correcting me,
Lynnton