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

 

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

 

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

 

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