Crosstab vs Flex Grid

[Jaybird5013]

I've kind of danced around this topic for a week or so. I hope you will
forgive me for not knowing the sort of questions to ask initially. I'm
trying to create a scheduling matrix which will do something like this:
04/19/10 04/20/10
04/21/10
CLIENTS
Monday------------------Tuesday---------------Wednesday---
Cargiver/ Time Cargiver/ Time
Cargiver/ Time A. Levign B. Shaw /900-1230 C.
Califlower/10-2 J. Byrd/11-4
D. Bowie A. Murphee/8-12 G. Tigliabu/4-8 Y.
Mama/1-5
F. Crissake O. Boyaboy/7-5 G. Durnit/10-12 D.
Gonnit/730-5

Lacking the imagination and expertise to come up with something on my own, I
thought I would confer with the wise and wonderful wizards of the Discussion
Group! (Let me know if I'm laying it on too thick.) I saw Peter Hibbs' use
of Flex Grid and how it does almost Exactly what I want. But I don't know
how to use it. Then I played around with a crosstab query until I got
something similar too. I'm beginning to think that I need to totally rethink
my concept. As I type this, I suspect that I need is three or four tables.
One for clients, one for Caregivers, one for Appointments, and maybe one that
creates one field for every day of the year. None of these methods uses an
updatable query as the source for the form as near as I can tell. However, I
know that I can use events to call up forms that will update the table and
form upon entry. I would like to figure out what the merits and pitfalls are
for these methods. As you can see, my requirements are fairly specific.
Perhaps my concept is myopic. Perhaps someone can help me to see my way
through the forest, or at least, a path I can follow.

 

[Steve]

Contact me and give me your email address and I will send you a screen shot
of my calendar form/report that does just what you want.

Steve
(e-mail address removed)

 

[John... Visio MVP]

Contact me and give me your email address and I will send you a screen
shot of my calendar form/report that does just what you want.

Steve
(e-mail address removed)

stevie: So whose calendar add on are you trying to flog?




Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[Peter Hibbs]

Jaybird,

It seems to me that you have two issues here - the design of your
tables and then devising a form to show the data in a 'user friendly'
way (and the FlexGrid would seem to be the preferred option).

Regarding the tables, you haven't really supplied enough information
to give a definitive answer but I would guess you would need a table
for Clients and a table for Appointments. I am not sure that you would
need another table for Caregivers though, I would think that you could
use the Clients table to store that information and just add a field
to differentiate between a record used for a Client and a record used
for a Caregiver. I am assuming that they would have similar fields,
i.e, name, address, telephone, etc (but I could be wrong since I don't
know enough about your set up). I certainly don't see the need for a
table to hold a field (or record) for each day of the year since the
Appointments table would already have that information.

Regarding the Flex Grid form - my Newsreader program scrambled your
attempt as showing the scheduling matrix so it is a bit difficult to
see what columns and rows you have, perhaps you could explain in
words. I remember that in reply to your earlier post I suggested using
a Crosstab query to populate the Flex Grid matrix but looking at your
design I think that would be a bit limited. I would suggest using the
same method that I used in the Time Sheet demo that you have already
looked at, you can just modify the code slightly to give the layout
you want. With this method you would first draw the Flex Grid control
on the form with the appropriate number of rows and columns and then
create a record set for the data that is to be shown on the grid and
iterate through the recordset and paste each bit of data into the
appropriate cell. If you need any assistance with that then post back
and I will try and help (but you will need to write a fair bit of VBA
code yourself, it is not difficult).

Regarding your data - you have not supplied much so here are a few
questions that need to be addressed. I am guessing that you want to
show one weeks worth of data (presumably 7 days) although you only
show Monday to Wednesday in your post. So does that mean you want 7
columns (plus one for the row label)? How many rows do you need, is
this one row for each Caregiver plus the headings? What information
will go into each cell, is it the client name plus the appointment
times? Can there be more than one client for an appointment? Can
appointments overlap?

As I mentioned above, you need to set up your tables first and then
move on to the Flex Grid code. Let us know how you get on.

Peter Hibbs.

 

[Jaybird5013]

Thanks, Peter. You really are amazing, answering my humble post in this way.

"Regarding your data - you have not supplied much so here are a few
questions that need to be addressed. I am guessing that you want to show one
weeks worth of data (presumably 7 days) although you only show Monday to
Wednesday in your post. So does that mean you want 7 columns (plus one for
the row label)?"

You are correct.

"How many rows do you need, is this one row for each Caregiver plus the
headings?"

No, it's one row for each CLIENT, not caregiver. Not more than 50 rows.
Probably not more than 30.

"What information will go into each cell, is it the client name plus the
appointment
times?"

I need the form to reflect the Client and the associated Caregivers and
Appointment Times. Sometimes clients have appointments in the morning and
afternoon.

"Can there be more than one client for an appointment?"

No, only one client per appointment, but there could be more than one
caregiver.

"Can appointments overlap?"

No, but several appointments could happen simultaneously.

 

[Peter Hibbs]

Jaybird,

OK, so you have a column for each day of the week plus the left column
will show a client name on each row and in the cells you would have
the Caregiver name (perhaps initials would be better to save room,
maybe) plus the appointment times in that day. So your grid would look
something like this :-

Appointments for Week 18 Apr - 24 Apr 2010
Sun Mon Tue Wed Thu Fri Sat
Client A ABS CDR DES HTR
0800 1100 0900 1000
Client B CDR DES HTR
0800 1200 1400
Client C ABS DES DES
1100 0900 1500

The first row shows the current week. The second row shows the days of
the week. The third and subsequent rows show the client name and under
the day of the week, the Caregiver name (i.e. ABS, CDR, DES, etc)
along with the appointment time (I have just shown the start time but
you could also show the end time, if required). So on Sunday 18th
Client A has an appointment with ABS at 08:00, Client B has an
appointment with CDR at 08:00 and Client C has an appointment with ABS
at 11:00. On Friday, Client A has an appointment with HTR at 10:00 and
Client B has an appointment also with HTR at 14:00 and so on. Is this
an accurate assessment of what you are wanting?

If this is correct I don't see any real problems with implementing
this in a Flex Grid but the actual code required will, of course,
depend on your table names, field names, etc. The way I would do this,
I think, is to create a grid with all the Client names shown in the
left rows which means that there will be one row for each client plus
two more rows for the two header rows. This will probably mean that
you will need vertical scroll bar if there are 50 or so clients. I
presume you already have some means of adding clients to your Client
table via a form. Is it acceptable to show all client names in the
grid even if they don't have any appointments for the selected week or
do you ONLY want to see clients that do have appointments, (which may
not work because you will presumably want some mechanism to add
appointments to the grid which means that you would need every
possible client name to be visible in the grid).

Anyway, before you can build the Flex Grid form you need to sort out
your tables, perhaps you could post back your table structure so that
we can see the table names, field names and field types.

Peter Hibbs.

PS It is getting late in the UK, I will check out this thread again
tomorrow.

 

[Jaybird5013]

I guess my best description of the form I want is:

A column list of clients followed by columns representing the days of the
week. Each of which corresponds to the calendar date for that day.

Under those days of the week headings I need the caregiver corresponding to
that day of the week and that client, and the hours for that shift.

Right now I have three tables: tblClients, tbleCaregivers, tblAppointments.

The point of this is to be able to see at a glance that all shifts for all
clients have been covered and by who and when. I can use a scroll bar if I
have too many clients to see at once.

 

[Peter Hibbs]

Jaybird,

You did not answer the questions I asked so it is a bit difficult to
give more precise information. It sounds now as if you have one
Caregiver for each day of the week which would be shown at the top of
each weekday column but I am still not sure what should appear in the
cells at the junctions of the clients and caregiver, you seem to be
talking about 'hours in a shift' rather than appointment times.

The problem here is that it is taking me longer to explain how you
should write the code than it is for me to write it myself. Perhaps it
would be quicker and easier if you can send me a copy of your database
and I will get the Flex Grid form working for you. Then you can carry
on with the rest of the database design yourself. If this is
acceptable to you perhaps you could send me the file (zipped, of
course) to the email address in the documentation on the Web site.

Also can you provide the following information as well :-

A valid returm email address (obviously).
What version of Access are you using.
What is your screen size (in pixels).
What do you expect the final grid to look like. It would be useful if
you could make up an example in Excel and include that (or a screen
shot of it).
What is this database supposed to do, a fairly detailed explanation
would be useful and how you see it working in practice.

I am not guaranteeing that I can do this and I am not proposing to
write the whole database but I will try and help with the Flex Grid
code, if I can.

Peter Hibbs.

 

[Jaybird5013]

My table structure as it exists now is primarily for testing purposes, but I
thought it would go something like this:

tblClients:
PK ClientID
txtClientName

tblCaregivers:
PK CaregiverID
txtCaregiverName

tblAppointments:
PK AppointmentID
txtStartTime
txtEndTime
CaregiverID
ClientID

I can add more information to the tables later if I can get this structure
to work. I've worked with simple vba in the past and while it takes me
forever I can usually figure it out by looking at other examples. My idea
was to call a form by clicking on a cell, updating the appointment table that
way, then using the OnClose event to trigger an update of the form.
Similarly, I can change the date range of the form by using the same method
you used in your example of the Time Sheet. That is, a text box where the
current week can be input. Presumably, all the cells of the form are updated
with the correct date from this textbox.

I have toyed with the idea of using a dumb form and populating all the cells
with code... but that seems like a lot of code and I don't want to reinvent
the wheel if there is a tool out there that will make my job easier.

I've taken a blank form and added the Flex Grid control, but I don't know
what to do with it. I've found the Flex Grid Properties. I presume that I
can dictate how many rows and columns I have with it. I don't know how to
control it from the form, however. I can't find any documentation for how to
use the Flex Grid aside from yours so guidance would be appreciated.

 

[Steve]

With my calendar form/report you can select any caregiver and then view all
the client appointments for the selected caregiver. To see this send me your
email address and I will send you a screen shot.

Steve
(e-mail address removed)

 

[Jaybird5013]

Sounds good, Steve, but I've got no money and I would like to learn how to do
this myself! Thanks, anyway...

 

[John... Visio MVP]

With my calendar form/report you can select any caregiver and then view
all the client appointments for the selected caregiver. To see this send
me your email address and I will send you a screen shot.

Steve

Stevie is our own personal pet troll who is the only one who does not
understand the concept of FREE peer to peer support!
He offers questionable results at unreasonable prices.

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

Please do not feed the trolls.

John... Visio MVP

 

[Jaybird5013]

Mr. Hibbs,

I've managed to isolate you timesheet example from the rest of the download
to avoid confusing myself... Hey, it happens! I've also managed to
substitute Clients for Employees without incident. Functionality is pretty
much the same. I've tried to substitute StartTime for HoursWorked and
EndTime for HoursOvertime, but the error checking has me stumped. I've
changed the data types on the tblTimeSheets to date and changed the formats
to be added to the tables, but something about the error checking is catching
me. Well, I'm not feeling real good about all the cutting and pasting I'm
doing, but it should help me to learn the basics... Here's the code:

Private Sub flxgrd_LeaveCell()

'Cell loses focus, add new record or update table with amended data

Dim vRow As Long, vCol As Long, vEmployeeID As Long
Dim vWork As Date, vOvertime As Date
Dim vDate As Date

vRow = flxgrd.Row
'fetch Row
vCol = flxgrd.Col
'and Col of leaving cell
If vRow >= conTop And vRow <= flxgrd.Rows - 2 And vCol >= conLeft And
vCol <= conRight Then 'if user exits an editable cell then
vEmployeeID = Val(flxgrd.TextMatrix(vRow, 17))
'fetch EmployeeID for this cell
vDate = txtWeekEnding - (6 - ((vCol - 1) \ 2))
'calc date from Col number and W/E date
If vCol Mod 2 = 0 Then vCol = vCol - 1
'force Col number to 1st col (not Overtime col)
vWork = Format(Val(flxgrd.TextMatrix(vRow, vCol)), "H:nn")
'fetch hours worked from grid
vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)), "H:nn")
'fetch overtime worked from grid
' If vWork > 24 Or vOvertime > 24 Then
'check if not over 24 hours
' Beep
' MsgBox "ERROR. Number of hours worked in a day cannot be
greater than 24 hours, please enter number again.", vbCritical + vbOKOnly,
"Invalid Hours"
Else
If Nz(DLookup("ID", "tblTimeSheets", "EmployeeID = " &
vEmployeeID & " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#")) = 0 Then
If flxgrd.TextMatrix(vRow, vCol) <> "" Or
flxgrd.TextMatrix(vRow, vCol + 1) <> "" Then 'if current day is NOT blank
then
CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"
'add a new record for this employee/date
End If
Else
CurrentDb.Execute "UPDATE tblTimeSheets SET " _
& "HoursWorked = " & vWork & ", " _
& "HoursOvertime = " & vOvertime & " " _
& "WHERE EmployeeID = " & vEmployeeID & " AND WorkDate = #"
& Format(vDate, "yyyy/m/d") & "#" 'update tblTimeSheets table with amended
data
End If
End If
'End If
FillGrid
'refresh grid

End Sub

It catches right here and won't insert the new format into the cell:

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"
'add a new record for this employee/date
End If

 

[Peter Hibbs]

Jaybird,

Well, there is probably more wrong than this but - you changed the two
fields called HoursWorked and HoursOvertime from Number fields in the
table to Date/Time type fields and renamed them to StartTime and
EndTime- correct?

In the INSERT statement you must also change the format that you use
to enter times into those fields. So the line :-

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& vWork & ", " _
& vOvertime & ")"

should be something like (watch for word wrapping) :-

CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "H:nn ") & "#, " _
& "#" & Format(vWork, "H:nn ") & "#, " _
& "#" & Format(vOvertime, "H:nn ") & "#)"

The same applies to the UPDATE statement after the Else command.

Having said that, I think it is a bad idea if you are storing the
times separately from the date part, the StartDate and EndDate fields
in the table should store the date AND time together. It will make it
easier to check for overlapping appointments and other things at a
later stage.

Whether that will fix your code I can't say for sure but this is the
first thing to correct.

Peter Hibbs.

 

[Jaybird5013]

I feel so stupid for doing things this way, but I have to be forced to think
things through or I won't do it correctly. Mr. Hibbs, If I understand your
code correctly, the changes made to the underlying table take place during
the flxgrd_LeaveCell event. If I want everything about your example to
remain the same other than the data in the two cells discussed above, I take
it that it I should concentrate on this section of the code.

If that's true, then I'm guessing that the problem is that I'm re-formatting
either vDate or WorkDate into a format that can't be used by the form. And,
of course, I've disabled the error catcher. Don't see how that's affecting
things, though. The data in my table is fine. The problem is my code. Here
it is again:

Private Sub flxgrd_LeaveCell()

'Cell loses focus, add new record or update table with amended data

Dim vRow As Long, vCol As Long, vEmployeeID As Long
Dim vWork As Date, vOvertime As Date
Dim vDate As Date

vRow = flxgrd.Row
'fetch Row
vCol = flxgrd.Col
'and Col of leaving cell
If vRow >= conTop And vRow <= flxgrd.Rows - 2 And vCol >= conLeft And
vCol <= conRight Then 'if user exits an editable cell then
vEmployeeID = Val(flxgrd.TextMatrix(vRow, 17))
'fetch EmployeeID for this cell
vDate = txtWeekEnding - (6 - ((vCol - 1) \ 2))
'calc date from Col number and W/E date
If vCol Mod 2 = 0 Then vCol = vCol - 1
'force Col number to 1st col (not Overtime col)
vWork = Format(Val(flxgrd.TextMatrix(vRow, vCol)), "yyyy/m/d")
'fetch hours worked from grid
vOvertime = Format(Val(flxgrd.TextMatrix(vRow, vCol + 1)),
"yyyy/m/d") 'fetch overtime worked from
grid
' If vWork > 24 Or vOvertime > 24 Then
'check if not over 24 hours
' Beep
' MsgBox "ERROR. Number of hours worked in a day cannot be greater
than 24 hours, please enter number again.", vbCritical + vbOKOnly, "Invalid
Hours"
Else
If Nz(DLookup("ID", "tblTimeSheets", "EmployeeID = " &
vEmployeeID & " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#")) = 0 Then
If flxgrd.TextMatrix(vRow, vCol) <> "" Or
flxgrd.TextMatrix(vRow, vCol + 1) <> "" Then 'if current day is NOT blank
then
CurrentDb.Execute "INSERT INTO tblTimeSheets
(EmployeeID, WorkDate, StartTime, EndTime) VALUES (" _
& vEmployeeID & ", " _
& "#" & Format(vDate, "yyyy/m/d") & "#, " _
& "#" & Format(vWork, "yyyy/m/d ") & "#, " _
& "#" & Format(vOvertime, "yyyy/m/d ") & "#)"
'add a new record for this
employee/date
End If
Else
CurrentDb.Execute "UPDATE tblTimeSheets SET " & "StartTime =
" & Format(vOvertime, "yyyy/m/d") _
& ", " & "EndTime = " & Format(vOvertime, "yyyy/m/d") & " "
& "WHERE EmployeeID = " & vEmployeeID _
& " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#"
'update tblTimeSheets table with amended data
End If
End If
'End If
FillGrid
'refresh grid

End Sub


It hangs up at the second execute command.

 

[Peter Hibbs]

Jaybird,

I can't be sure because I don't know exactly what data you are
entering into the grid but in the execute UPDATE statement (which I
assume is the one that is not working) you should be enclosing the
variables in # characters because they are Date/Time type variables.

Try replacing the UPDATE statement with this :-

CurrentDb.Execute "UPDATE tblTimeSheets SET StartTime = #" _
& Format(vOvertime, "yyyy/m/d") & "#, EndTime = #" _
& Format(vOvertime, "yyyy/m/d") _
& "# WHERE EmployeeID = " & vEmployeeID _
& " AND WorkDate = #" & Format(vDate, "yyyy/m/d") & "#"

If you are relying on the users to enter a time in a cell and then
copying that data back to the table I think this is a bit fraught
because it relies on the users entering the time in exactly the right
format, i.e. something like 09:00 or whatever, if they enter something
like 09am instead the code will just fail. Remember, a Flex Grid can
only store text strings, it cannot automatically correct the date
and/or time for you if it is entered incorrectly. If this is what you
need it might be safer to add some checks on the entered text before
you copy it to the grid.

Anyway, try the mods above, if that doesn't work you can send me a
copy of the database and I will have a look at it.

Peter Hibbs.

 

[Jaybird5013]

Perhaps I should be calling a form which will fill the grid for me in the
proper format. (of course, I don't know the peculiarities of the flexgrid
yet)

 

[Peter Hibbs]

Jaybird,

If you mean that when you click on a cell it pops up another form
which shows the information relating to that cell (i.e. the Client
info from the row and the Carer info from the column) then, yes, I
think that would be preferable (and easier to code) in your situation.

The method you are currently using (with a hidden sub-form appearing
over the selected cell, etc) is quite difficult to do in code and has
some limitations.

If you need an example of using a separate pop-up form you can look at
the Northwind Orders Summary form (on the green menu) in the Flex Grid
Demo database which does something similar or you could download the
Crosstab Flex Grid demo from my Web site which shows the same thing in
more detail.

Basically, when you click on a cell you would use the click event to
open a form in acDialog mode and pass the ID of the Client (from the
Row) and the ID of the Carer (or the date, if that is easier) from the
Column to the pop-up form as an OpenArgs and then in the pop-up form
you can show all the information for that appointment by collecting it
from the relevant tables. The user would make any changes or add a new
appointment or whatever and click an OK button which would trigger
some code to write the data back to the tables. When the form closes
the Flex Grid control would be then redrawn to show the amended data.

It sounds complicated (I suppose it is really) but it gives you more
flexibility than your current method.

Actually, if you download the Appointments/Bookings demo form the Web
site, all this code is already done for you (although you would
obviously have to change your field and tables names, etc).

Peter Hibbs.