Converting check box data into useable narrative

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

VName Seven Eight Nine Ten Eleven Noon
Ron -1 -1 -1 -1 0 0
Kevin 0 0 0 -1 -1 0
Sue 0 0 -1 -1 0 0

What I now want to do is convert this into data that can be used for a mail
merge which will provide an individual’s Start Times and End Times. For
example:

* Ron will have a start time of 7:00am and a finish time of 11:00am
* Sue will have a start time of 9:00am and a finish time of 11:00am

I’m afraid I don’t even know how to start to tell Access how to do this....

TIA ...

SELECT QrySiteRosterOne.ScheduleDay, QrySiteRosterOne.ContactFK,
QrySiteRosterOne.CombName, QrySiteRosterOne.RegionFK,
QrySiteRosterOne.SiteFK, QrySiteRosterOne.Seven, QrySiteRosterOne.Eignt,
QrySiteRosterOne.Nine, QrySiteRosterOne.Ten, QrySiteRosterOne.Eleven,
QrySiteRosterOne.Noon, QrySiteRosterOne.One, QrySiteRosterOne.Two,
QrySiteRosterOne.Three, QrySiteRosterOne.Four, QrySiteRosterOne.Five,
QrySiteRosterOne.Six, QrySiteRosterOne.Region, QrySiteRosterOne.Site,
QrySiteRosterOne.ContactStatus, QrySiteRosterOne.ContactType,
QrySiteRosterOne.Combined, QrySiteRosterOne.Sight, QrySiteRosterOne.RegionID
FROM QrySiteRosterOne;

UNION SELECT ALL QrySiteRosterTwo.ScheduleDay, QrySiteRosterTwo.ContactFK,
QrySiteRosterTwo.CombName, QrySiteRosterTwo.RegionFK,
QrySiteRosterTwo.SiteFK, QrySiteRosterTwo.Seven, QrySiteRosterTwo.Eignt,
QrySiteRosterTwo.Nine, QrySiteRosterTwo.Ten, QrySiteRosterTwo.Eleven,
QrySiteRosterTwo.Noon, QrySiteRosterTwo.One, QrySiteRosterTwo.Two,
QrySiteRosterTwo.Three, QrySiteRosterTwo.Four, QrySiteRosterTwo.Five,
QrySiteRosterTwo.Six, QrySiteRosterTwo.Region, QrySiteRosterTwo.Site,
QrySiteRosterTwo.ContactStatus, QrySiteRosterTwo.ContactType,
QrySiteRosterTwo.Combined, QrySiteRosterTwo.Sight, QrySiteRosterTwo.RegionID
FROM QrySiteRosterTwo;

UNION SELECT ALL QrySiteRosterThree.ScheduleDay,
QrySiteRosterThree.ContactFK, QrySiteRosterThree.CombName,
QrySiteRosterThree.RegionFK, QrySiteRosterThree.SiteFK,
QrySiteRosterThree.Seven, QrySiteRosterThree.Eignt, QrySiteRosterThree.Nine,
QrySiteRosterThree.Ten, QrySiteRosterThree.Eleven, QrySiteRosterThree.Noon,
QrySiteRosterThree.One, QrySiteRosterThree.Two, QrySiteRosterThree.Three,
QrySiteRosterThree.Four, QrySiteRosterThree.Five, QrySiteRosterThree.Six,
QrySiteRosterThree.Region, QrySiteRosterThree.Site,
QrySiteRosterThree.ContactStatus, QrySiteRosterThree.ContactType,
QrySiteRosterThree.Combined, QrySiteRosterThree.Sight,
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;

--
Sue Compelling

Hi Sue,

I don't know the background of your database but it seems to me that the
reason you're having a hard time converting your query is because of your
table structure.

It would have been easier if you just have two fields: StartTime and EndTime
to enter the times instead of multiple checkboxes.

Just my humble opinion...


"Sue Compelling" wrote:

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

VName Seven Eight Nine Ten Eleven Noon
Ron -1 -1 -1 -1 0 0
Kevin 0 0 0 -1 -1 0
Sue 0 0 -1 -1 0 0

What I now want to do is convert this into data that can be used for a mail
merge which will provide an individual’s Start Times and End Times. For
example:

* Ron will have a start time of 7:00am and a finish time of 11:00am
* Sue will have a start time of 9:00am and a finish time of 11:00am

I’m afraid I don’t even know how to start to tell Access how to do this....

TIA ...

SELECT QrySiteRosterOne.ScheduleDay, QrySiteRosterOne.ContactFK,
QrySiteRosterOne.CombName, QrySiteRosterOne.RegionFK,
QrySiteRosterOne.SiteFK, QrySiteRosterOne.Seven, QrySiteRosterOne.Eignt,
QrySiteRosterOne.Nine, QrySiteRosterOne.Ten, QrySiteRosterOne.Eleven,
QrySiteRosterOne.Noon, QrySiteRosterOne.One, QrySiteRosterOne.Two,
QrySiteRosterOne.Three, QrySiteRosterOne.Four, QrySiteRosterOne.Five,
QrySiteRosterOne.Six, QrySiteRosterOne.Region, QrySiteRosterOne.Site,
QrySiteRosterOne.ContactStatus, QrySiteRosterOne.ContactType,
QrySiteRosterOne.Combined, QrySiteRosterOne.Sight, QrySiteRosterOne.RegionID
FROM QrySiteRosterOne;

UNION SELECT ALL QrySiteRosterTwo.ScheduleDay, QrySiteRosterTwo.ContactFK,
QrySiteRosterTwo.CombName, QrySiteRosterTwo.RegionFK,
QrySiteRosterTwo.SiteFK, QrySiteRosterTwo.Seven, QrySiteRosterTwo.Eignt,
QrySiteRosterTwo.Nine, QrySiteRosterTwo.Ten, QrySiteRosterTwo.Eleven,
QrySiteRosterTwo.Noon, QrySiteRosterTwo.One, QrySiteRosterTwo.Two,
QrySiteRosterTwo.Three, QrySiteRosterTwo.Four, QrySiteRosterTwo.Five,
QrySiteRosterTwo.Six, QrySiteRosterTwo.Region, QrySiteRosterTwo.Site,
QrySiteRosterTwo.ContactStatus, QrySiteRosterTwo.ContactType,
QrySiteRosterTwo.Combined, QrySiteRosterTwo.Sight, QrySiteRosterTwo.RegionID
FROM QrySiteRosterTwo;

UNION SELECT ALL QrySiteRosterThree.ScheduleDay,
QrySiteRosterThree.ContactFK, QrySiteRosterThree.CombName,
QrySiteRosterThree.RegionFK, QrySiteRosterThree.SiteFK,
QrySiteRosterThree.Seven, QrySiteRosterThree.Eignt, QrySiteRosterThree.Nine,
QrySiteRosterThree.Ten, QrySiteRosterThree.Eleven, QrySiteRosterThree.Noon,
QrySiteRosterThree.One, QrySiteRosterThree.Two, QrySiteRosterThree.Three,
QrySiteRosterThree.Four, QrySiteRosterThree.Five, QrySiteRosterThree.Six,
QrySiteRosterThree.Region, QrySiteRosterThree.Site,
QrySiteRosterThree.ContactStatus, QrySiteRosterThree.ContactType,
QrySiteRosterThree.Combined, QrySiteRosterThree.Sight,
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;

--
Sue Compelling

Sue:

The following function will return a string expression from the values passed
into it as a parameter array:

Public Function TimesToText(intStartAt As Integer, ParamArray varTimes() As
Variant) As String

Dim varTime As Variant
Dim dtmStart As Date, dtmEnd As Date
Dim n As Integer

n = intStartAt

For Each varTime In varTimes
If varTime = True Then
If dtmStart = 0 Then
dtmStart = CDate(n & ":00:00")
dtmEnd = CDate(n & ":00:00")
End If
Else
If dtmEnd 0 Then
dtmEnd = CDate(n & ":00:00")
Exit For
End If
End If
n = n + 1
Next varTime

TimesToText = "Start time: " & Format(dtmStart, "h:00 AM/PM") & _
", End time: " & Format(dtmEnd, "h:00 AM/PM")

End Function

Paste it into a standard module in the database. You can then call it in a
query based on your current query, passing the start hour (7 in this case)
and then the values of the Seven, Eight, Nine etc columns into it, followed
by a final Boolean FALSE, which is necessary to cater for any employee who
works to the end of the working day, i.e. has a -1 value in the Six column,
so finishes at 7:00PM.

SELECT vName, TimesToText(7, Seven, Eight, Nine, Ten, Eleven, Noon, One, Two,
Three, Four, Five, Six, False) As TimeRange
FROM YourQuery;

For Ron for instance it would return the string: 'Start time: 7:00 AM, End
time: 11:00 AM'. You can easily modify it to return a different 'narrative'
by amending the final line before the end of the function. Note that this is
a single line of code written as two for readability, with the underscore
continuation character at the end of the first line.

You can of course include any other columns from your existing query in the
final query as well as the name and time range.

I've assumed that in every row the employee in question will be scheduled to
work at least one hour, as if all zeros (which represent Boolean FALSE values)
were passed into the function it would show the employee scheduled to both
start and finish at midnight at the start of the day!

Ken Sheridan
Stafford, England

Sue Compelling wrote:
Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

VName Seven Eight Nine Ten Eleven Noon
Ron -1 -1 -1 -1 0 0
Kevin 0 0 0 -1 -1 0
Sue 0 0 -1 -1 0 0

What I now want to do is convert this into data that can be used for a mail
merge which will provide an individual’s Start Times and End Times. For
example:

* Ron will have a start time of 7:00am and a finish time of 11:00am
* Sue will have a start time of 9:00am and a finish time of 11:00am

I’m afraid I don’t even know how to start to tell Access how to do this....

TIA ...

SELECT QrySiteRosterOne.ScheduleDay, QrySiteRosterOne.ContactFK,
QrySiteRosterOne.CombName, QrySiteRosterOne.RegionFK,
QrySiteRosterOne.SiteFK, QrySiteRosterOne.Seven, QrySiteRosterOne.Eignt,
QrySiteRosterOne.Nine, QrySiteRosterOne.Ten, QrySiteRosterOne.Eleven,
QrySiteRosterOne.Noon, QrySiteRosterOne.One, QrySiteRosterOne.Two,
QrySiteRosterOne.Three, QrySiteRosterOne.Four, QrySiteRosterOne.Five,
QrySiteRosterOne.Six, QrySiteRosterOne.Region, QrySiteRosterOne.Site,
QrySiteRosterOne.ContactStatus, QrySiteRosterOne.ContactType,
QrySiteRosterOne.Combined, QrySiteRosterOne.Sight, QrySiteRosterOne.RegionID
FROM QrySiteRosterOne;

UNION SELECT ALL QrySiteRosterTwo.ScheduleDay, QrySiteRosterTwo.ContactFK,
QrySiteRosterTwo.CombName, QrySiteRosterTwo.RegionFK,
QrySiteRosterTwo.SiteFK, QrySiteRosterTwo.Seven, QrySiteRosterTwo.Eignt,
QrySiteRosterTwo.Nine, QrySiteRosterTwo.Ten, QrySiteRosterTwo.Eleven,
QrySiteRosterTwo.Noon, QrySiteRosterTwo.One, QrySiteRosterTwo.Two,
QrySiteRosterTwo.Three, QrySiteRosterTwo.Four, QrySiteRosterTwo.Five,
QrySiteRosterTwo.Six, QrySiteRosterTwo.Region, QrySiteRosterTwo.Site,
QrySiteRosterTwo.ContactStatus, QrySiteRosterTwo.ContactType,
QrySiteRosterTwo.Combined, QrySiteRosterTwo.Sight, QrySiteRosterTwo.RegionID
FROM QrySiteRosterTwo;

UNION SELECT ALL QrySiteRosterThree.ScheduleDay,
QrySiteRosterThree.ContactFK, QrySiteRosterThree.CombName,
QrySiteRosterThree.RegionFK, QrySiteRosterThree.SiteFK,
QrySiteRosterThree.Seven, QrySiteRosterThree.Eignt, QrySiteRosterThree.Nine,
QrySiteRosterThree.Ten, QrySiteRosterThree.Eleven, QrySiteRosterThree.Noon,
QrySiteRosterThree.One, QrySiteRosterThree.Two, QrySiteRosterThree.Three,
QrySiteRosterThree.Four, QrySiteRosterThree.Five, QrySiteRosterThree.Six,
QrySiteRosterThree.Region, QrySiteRosterThree.Site,
QrySiteRosterThree.ContactStatus, QrySiteRosterThree.ContactType,
QrySiteRosterThree.Combined, QrySiteRosterThree.Sight,
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201001/1

On Fri, 1 Jan 2010 23:38:01 -0800, Sue Compelling
wrote:

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

Ow. So you have three identical non-normalized tables? I fear you've fallen
into the common trap of designing your tables to match your input form, rather
than starting with properly normalized tables (one table in this instance!)
and fitting the data input form to IT.

A proper structure would probably have tables such as:

Employees
EmployeeID primary key
LastName
FirstName
other biographical and contact data
RosterNo the employee is in this roster

Rosters
RosterNo primary Key
any info about this roster

Schedule
ScheduleID autonumber primary key
EmployeeID link to Employees
StartTime date/time
EndTime date/time


It would be possible to populate this table from your existing spreadsheet
format using a "Normalizing Union Query" based on your UNION query... but it
would be complicated and slow. Any chance of changing your form (perhaps by
adding some code to populate the normalized table) instead of sticking with
this table structure?

--

John W. Vinson [MVP]

Ken, you're a LEGEND - thanks soooo much for taking the time to document this
for me. It worked brilliantly.

There is a small fly in the ointment insofar as some people (not many) chose
to do a split shift - how do I capture that? (I do have work around for the
users if this is possible programmatically)

FWIW - my database is used for street appeals - and this one is for
Collecting for the Blind. The street appeal runs over three days and this
registers the Volunteers and when and what days they are free to collect on.
We have over 8,000 volunteers and the check boxes convert into a visual
schedule (sort of like a horizontal bar graph) for the designated Area
Coordinators.

Thanks again
--
Sue Compelling


"KenSheridan via AccessMonster.com" wrote:

Sue:

The following function will return a string expression from the values passed
into it as a parameter array:

Public Function TimesToText(intStartAt As Integer, ParamArray varTimes() As
Variant) As String

Dim varTime As Variant
Dim dtmStart As Date, dtmEnd As Date
Dim n As Integer

n = intStartAt

For Each varTime In varTimes
If varTime = True Then
If dtmStart = 0 Then
dtmStart = CDate(n & ":00:00")
dtmEnd = CDate(n & ":00:00")
End If
Else
If dtmEnd 0 Then
dtmEnd = CDate(n & ":00:00")
Exit For
End If
End If
n = n + 1
Next varTime

TimesToText = "Start time: " & Format(dtmStart, "h:00 AM/PM") & _
", End time: " & Format(dtmEnd, "h:00 AM/PM")

End Function

Paste it into a standard module in the database. You can then call it in a
query based on your current query, passing the start hour (7 in this case)
and then the values of the Seven, Eight, Nine etc columns into it, followed
by a final Boolean FALSE, which is necessary to cater for any employee who
works to the end of the working day, i.e. has a -1 value in the Six column,
so finishes at 7:00PM.

SELECT vName, TimesToText(7, Seven, Eight, Nine, Ten, Eleven, Noon, One, Two,
Three, Four, Five, Six, False) As TimeRange
FROM YourQuery;

For Ron for instance it would return the string: 'Start time: 7:00 AM, End
time: 11:00 AM'. You can easily modify it to return a different 'narrative'
by amending the final line before the end of the function. Note that this is
a single line of code written as two for readability, with the underscore
continuation character at the end of the first line.

You can of course include any other columns from your existing query in the
final query as well as the name and time range.

I've assumed that in every row the employee in question will be scheduled to
work at least one hour, as if all zeros (which represent Boolean FALSE values)
were passed into the function it would show the employee scheduled to both
start and finish at midnight at the start of the day!

Ken Sheridan
Stafford, England

Sue Compelling wrote:
Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

VName Seven Eight Nine Ten Eleven Noon
Ron -1 -1 -1 -1 0 0
Kevin 0 0 0 -1 -1 0
Sue 0 0 -1 -1 0 0

What I now want to do is convert this into data that can be used for a mail
merge which will provide an individual’s Start Times and End Times. For
example:

* Ron will have a start time of 7:00am and a finish time of 11:00am
* Sue will have a start time of 9:00am and a finish time of 11:00am

I’m afraid I don’t even know how to start to tell Access how to do this....

TIA ...

SELECT QrySiteRosterOne.ScheduleDay, QrySiteRosterOne.ContactFK,
QrySiteRosterOne.CombName, QrySiteRosterOne.RegionFK,
QrySiteRosterOne.SiteFK, QrySiteRosterOne.Seven, QrySiteRosterOne.Eignt,
QrySiteRosterOne.Nine, QrySiteRosterOne.Ten, QrySiteRosterOne.Eleven,
QrySiteRosterOne.Noon, QrySiteRosterOne.One, QrySiteRosterOne.Two,
QrySiteRosterOne.Three, QrySiteRosterOne.Four, QrySiteRosterOne.Five,
QrySiteRosterOne.Six, QrySiteRosterOne.Region, QrySiteRosterOne.Site,
QrySiteRosterOne.ContactStatus, QrySiteRosterOne.ContactType,
QrySiteRosterOne.Combined, QrySiteRosterOne.Sight, QrySiteRosterOne.RegionID
FROM QrySiteRosterOne;

UNION SELECT ALL QrySiteRosterTwo.ScheduleDay, QrySiteRosterTwo.ContactFK,
QrySiteRosterTwo.CombName, QrySiteRosterTwo.RegionFK,
QrySiteRosterTwo.SiteFK, QrySiteRosterTwo.Seven, QrySiteRosterTwo.Eignt,
QrySiteRosterTwo.Nine, QrySiteRosterTwo.Ten, QrySiteRosterTwo.Eleven,
QrySiteRosterTwo.Noon, QrySiteRosterTwo.One, QrySiteRosterTwo.Two,
QrySiteRosterTwo.Three, QrySiteRosterTwo.Four, QrySiteRosterTwo.Five,
QrySiteRosterTwo.Six, QrySiteRosterTwo.Region, QrySiteRosterTwo.Site,
QrySiteRosterTwo.ContactStatus, QrySiteRosterTwo.ContactType,
QrySiteRosterTwo.Combined, QrySiteRosterTwo.Sight, QrySiteRosterTwo.RegionID
FROM QrySiteRosterTwo;

UNION SELECT ALL QrySiteRosterThree.ScheduleDay,
QrySiteRosterThree.ContactFK, QrySiteRosterThree.CombName,
QrySiteRosterThree.RegionFK, QrySiteRosterThree.SiteFK,
QrySiteRosterThree.Seven, QrySiteRosterThree.Eignt, QrySiteRosterThree.Nine,
QrySiteRosterThree.Ten, QrySiteRosterThree.Eleven, QrySiteRosterThree.Noon,
QrySiteRosterThree.One, QrySiteRosterThree.Two, QrySiteRosterThree.Three,
QrySiteRosterThree.Four, QrySiteRosterThree.Five, QrySiteRosterThree.Six,
QrySiteRosterThree.Region, QrySiteRosterThree.Site,
QrySiteRosterThree.ContactStatus, QrySiteRosterThree.ContactType,
QrySiteRosterThree.Combined, QrySiteRosterThree.Sight,
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201001/1

.

Hi - thanks for taking the time to respond DB guy.

FWIW - I need the check boxes to be used for every hour of the shift as
these convert into a visual schedule (sort of like a horizontal bar graph)
for the designated Area Coordinators.

My database is used for street appeals - and this one is for Collecting for
the Blind. The street appeal runs over three days and this registers the
Volunteers (over 8,000) and when and what days they are free to collect on.

Sue Compelling


"theDBguy" wrote:

Hi Sue,

I don't know the background of your database but it seems to me that the
reason you're having a hard time converting your query is because of your
table structure.

It would have been easier if you just have two fields: StartTime and EndTime
to enter the times instead of multiple checkboxes.

Just my humble opinion...


"Sue Compelling" wrote:

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

VName Seven Eight Nine Ten Eleven Noon
Ron -1 -1 -1 -1 0 0
Kevin 0 0 0 -1 -1 0
Sue 0 0 -1 -1 0 0

What I now want to do is convert this into data that can be used for a mail
merge which will provide an individual’s Start Times and End Times. For
example:

* Ron will have a start time of 7:00am and a finish time of 11:00am
* Sue will have a start time of 9:00am and a finish time of 11:00am

I’m afraid I don’t even know how to start to tell Access how to do this....

TIA ...

SELECT QrySiteRosterOne.ScheduleDay, QrySiteRosterOne.ContactFK,
QrySiteRosterOne.CombName, QrySiteRosterOne.RegionFK,
QrySiteRosterOne.SiteFK, QrySiteRosterOne.Seven, QrySiteRosterOne.Eignt,
QrySiteRosterOne.Nine, QrySiteRosterOne.Ten, QrySiteRosterOne.Eleven,
QrySiteRosterOne.Noon, QrySiteRosterOne.One, QrySiteRosterOne.Two,
QrySiteRosterOne.Three, QrySiteRosterOne.Four, QrySiteRosterOne.Five,
QrySiteRosterOne.Six, QrySiteRosterOne.Region, QrySiteRosterOne.Site,
QrySiteRosterOne.ContactStatus, QrySiteRosterOne.ContactType,
QrySiteRosterOne.Combined, QrySiteRosterOne.Sight, QrySiteRosterOne.RegionID
FROM QrySiteRosterOne;

UNION SELECT ALL QrySiteRosterTwo.ScheduleDay, QrySiteRosterTwo.ContactFK,
QrySiteRosterTwo.CombName, QrySiteRosterTwo.RegionFK,
QrySiteRosterTwo.SiteFK, QrySiteRosterTwo.Seven, QrySiteRosterTwo.Eignt,
QrySiteRosterTwo.Nine, QrySiteRosterTwo.Ten, QrySiteRosterTwo.Eleven,
QrySiteRosterTwo.Noon, QrySiteRosterTwo.One, QrySiteRosterTwo.Two,
QrySiteRosterTwo.Three, QrySiteRosterTwo.Four, QrySiteRosterTwo.Five,
QrySiteRosterTwo.Six, QrySiteRosterTwo.Region, QrySiteRosterTwo.Site,
QrySiteRosterTwo.ContactStatus, QrySiteRosterTwo.ContactType,
QrySiteRosterTwo.Combined, QrySiteRosterTwo.Sight, QrySiteRosterTwo.RegionID
FROM QrySiteRosterTwo;

UNION SELECT ALL QrySiteRosterThree.ScheduleDay,
QrySiteRosterThree.ContactFK, QrySiteRosterThree.CombName,
QrySiteRosterThree.RegionFK, QrySiteRosterThree.SiteFK,
QrySiteRosterThree.Seven, QrySiteRosterThree.Eignt, QrySiteRosterThree.Nine,
QrySiteRosterThree.Ten, QrySiteRosterThree.Eleven, QrySiteRosterThree.Noon,
QrySiteRosterThree.One, QrySiteRosterThree.Two, QrySiteRosterThree.Three,
QrySiteRosterThree.Four, QrySiteRosterThree.Five, QrySiteRosterThree.Six,
QrySiteRosterThree.Region, QrySiteRosterThree.Site,
QrySiteRosterThree.ContactStatus, QrySiteRosterThree.ContactType,
QrySiteRosterThree.Combined, QrySiteRosterThree.Sight,
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;

--
Sue Compelling

Hi John

Thanks for the sage advice as always. (I do try to normalize though I had
to put this functionality together the only way I knew how)

Ken's code worked a treat ....

--
Sue Compelling


"John W. Vinson" wrote:

On Fri, 1 Jan 2010 23:38:01 -0800, Sue Compelling
wrote:

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

Ow. So you have three identical non-normalized tables? I fear you've fallen
into the common trap of designing your tables to match your input form, rather
than starting with properly normalized tables (one table in this instance!)
and fitting the data input form to IT.

A proper structure would probably have tables such as:

Employees
EmployeeID primary key
LastName
FirstName
other biographical and contact data
RosterNo the employee is in this roster

Rosters
RosterNo primary Key
any info about this roster

Schedule
ScheduleID autonumber primary key
EmployeeID link to Employees
StartTime date/time
EndTime date/time


It would be possible to populate this table from your existing spreadsheet
format using a "Normalizing Union Query" based on your UNION query... but it
would be complicated and slow. Any chance of changing your form (perhaps by
adding some code to populate the normalized table) instead of sticking with
this table structure?

--

John W. Vinson [MVP]
.

Hi Sue,

If you would please allow me to give my humble opinion again...

I am not trying to knock yours or Ken's efforts but by not having a
normalized structure, you could potentially end up with "perpetual
workarounds," such as using codes to keep catering to your requirements that
could have been achieved by a normalized design without the need for codes.

Again, it's just my 2 cents...

Good luck!


"Sue Compelling" wrote:

Hi John

Thanks for the sage advice as always. (I do try to normalize though I had
to put this functionality together the only way I knew how)

Ken's code worked a treat ....

--
Sue Compelling


"John W. Vinson" wrote:

On Fri, 1 Jan 2010 23:38:01 -0800, Sue Compelling
wrote:

Hi (Access 2007)

I have the following union query (full sql at bottom of question) where the
fields Seven, Eight, Nine etc are derived from check boxes in a scheduling
page.

Ow. So you have three identical non-normalized tables? I fear you've fallen
into the common trap of designing your tables to match your input form, rather
than starting with properly normalized tables (one table in this instance!)
and fitting the data input form to IT.

A proper structure would probably have tables such as:

Employees
EmployeeID primary key
LastName
FirstName
other biographical and contact data
RosterNo the employee is in this roster

Rosters
RosterNo primary Key
any info about this roster

Schedule
ScheduleID autonumber primary key
EmployeeID link to Employees
StartTime date/time
EndTime date/time


It would be possible to populate this table from your existing spreadsheet
format using a "Normalizing Union Query" based on your UNION query... but it
would be complicated and slow. Any chance of changing your form (perhaps by
adding some code to populate the normalized table) instead of sticking with
this table structure?

--

John W. Vinson [MVP]
.

Sue:

For split shifts the best thing would be to enter each part-shift as a
separate record. Bearing in mind what the others have said, in a normalized
table you'd then have two rows for the shift with the employee ID and a
DateTimeStart and DateTimeEnd value for each. In fact this is normal in most
time-keeping applications as in the vast majority of cases an employee's
working day is split into two parts separated by a lunch-break. With your
design you'd check the hours for one part-shift in one row and those for the
other part-shift in another row. The function would then return the start
and end times for each part-shift.

I can see why you've gone for the design you've used, but essentially what
you've done is use a database table more like a spreadsheet than a table. In
database terms you are encoding data as column headings rather than storing
them as values at column positions in rows in the table, which is the only
way data should be stored in a relational database. Ideally what you'd have
done would have been to use a normalized table, but instead of entering data
by means of bound check boxes, you'd have used unbound controls, updating the
underlying table in code as values are entered in the controls, and vice
versa. This would give you your 'visual schedule' but the data would be
stored in a more conventionally (and correctly) designed table. This,
however, would require some far from trivial VBA code to be written, and was
why I didn't address the design issue in my first reply, sensing that you'd
welcome a working solution to your immediate problem, rather than comments
which would require a radical redesign of your underlying logical model.
That's not to say that I don't agree with the others' comments, however, and
it would be nice to think that you could move towards a more robust model in
time.

Ken Sheridan
Stafford, England

Sue Compelling wrote:
Ken, you're a LEGEND - thanks soooo much for taking the time to document this
for me. It worked brilliantly.

There is a small fly in the ointment insofar as some people (not many) chose
to do a split shift - how do I capture that? (I do have work around for the
users if this is possible programmatically)

FWIW - my database is used for street appeals - and this one is for
Collecting for the Blind. The street appeal runs over three days and this
registers the Volunteers and when and what days they are free to collect on.
We have over 8,000 volunteers and the check boxes convert into a visual
schedule (sort of like a horizontal bar graph) for the designated Area
Coordinators.

Thanks again
Sue:

[quoted text clipped - 114 lines]
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;

--
Message posted via http://www.accessmonster.com

Hi Ken

On the money again - I liked how you gave me the practical solution for my
immediate problem and then the coaching. Ummm ... the "far from trivial VBA
code" has scared me a little as I thought when I had time I might deconstruct
what I've done (which is workable) and have a crack at normalizing. I might
still try it and hope that this forum continues to offer the priceless
support and assistance it always does (as you can propbably tell DB Design is
not my day job).

BTW - I'm in NZ and we have a somewhat sticky 24 degrees at the moment - I
understand you're knee deep in snow - great for DB design!
--
Sue Compelling


"KenSheridan via AccessMonster.com" wrote:

Sue:

For split shifts the best thing would be to enter each part-shift as a
separate record. Bearing in mind what the others have said, in a normalized
table you'd then have two rows for the shift with the employee ID and a
DateTimeStart and DateTimeEnd value for each. In fact this is normal in most
time-keeping applications as in the vast majority of cases an employee's
working day is split into two parts separated by a lunch-break. With your
design you'd check the hours for one part-shift in one row and those for the
other part-shift in another row. The function would then return the start
and end times for each part-shift.

I can see why you've gone for the design you've used, but essentially what
you've done is use a database table more like a spreadsheet than a table. In
database terms you are encoding data as column headings rather than storing
them as values at column positions in rows in the table, which is the only
way data should be stored in a relational database. Ideally what you'd have
done would have been to use a normalized table, but instead of entering data
by means of bound check boxes, you'd have used unbound controls, updating the
underlying table in code as values are entered in the controls, and vice
versa. This would give you your 'visual schedule' but the data would be
stored in a more conventionally (and correctly) designed table. This,
however, would require some far from trivial VBA code to be written, and was
why I didn't address the design issue in my first reply, sensing that you'd
welcome a working solution to your immediate problem, rather than comments
which would require a radical redesign of your underlying logical model.
That's not to say that I don't agree with the others' comments, however, and
it would be nice to think that you could move towards a more robust model in
time.

Ken Sheridan
Stafford, England

Sue Compelling wrote:
Ken, you're a LEGEND - thanks soooo much for taking the time to document this
for me. It worked brilliantly.

There is a small fly in the ointment insofar as some people (not many) chose
to do a split shift - how do I capture that? (I do have work around for the
users if this is possible programmatically)

FWIW - my database is used for street appeals - and this one is for
Collecting for the Blind. The street appeal runs over three days and this
registers the Volunteers and when and what days they are free to collect on.
We have over 8,000 volunteers and the check boxes convert into a visual
schedule (sort of like a horizontal bar graph) for the designated Area
Coordinators.

Thanks again
Sue:

[quoted text clipped - 114 lines]
QrySiteRosterThree.RegionID
FROM QrySiteRosterThree;

--
Message posted via http://www.accessmonster.com

.