Converting check box data into useable narrative

[Sue Compelling]

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, QrySiteRosterTwne, 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;

 

[theDBguy]

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...

 

[KenSheridan via AccessMonster.com]

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

 

[John W. Vinson]

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?

 

[Sue Compelling]

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]

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

 

[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 ....

 

[theDBguy]

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!

 

[KenSheridan via AccessMonster.com]

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

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]

 

[Sue Compelling]

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

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

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;

 

[Sue Compelling]

Hi DBGuy

I so need any opinions (humble or otherwise) and advice I can get - thanks
for the 2 cents and one day I'll normalize this ...

 

[KenSheridan via AccessMonster.com]

Sue:

Yep, its below zero here right now, so don't forget your woolly jumper and
fur hat if you come visit us.

I may have told you this before (I've mentioned it to somebody from New
Zealand in the past, I'm sure) but people sometimes think I must be a New
Zealander. My initials are KWS, and when I was at school somebody inserted
two Is into them on my bag so it read KIWIS. As a result I acquired the
nickname Kiwi, which confuses people no end as I'm actually Irish!

I'm retired from my work in environmental planning now, but I still do quite
a lot of pro bono work for charitable organisations; I've recently been
helping out a hospice for terminally ill children in Australia with their
database of family support programmes. As your database is for charitable
work I'd be more than happy to help on a similar basis if you think I can be
of assistance. Feel free to mail me directly at:

kenwsheridan<at>yahoo<dot>co<dot>uk

Ken Sheridan
Stafford, England

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:

[quoted text clipped - 49 lines]