If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab vs Flex Grid
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. |
#2
|
|||
|
|||
Crosstab vs Flex Grid
|
#3
|
|||
|
|||
Crosstab vs Flex Grid
"Steve" schreef in bericht ... 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 And then you are going to give details about that form or just ask money ?? -- Get lost $teve. Go away... far away.... Again... Get lost $teve. Go away... far away.... No-one wants you here... no-one needs you here... This newsgroup is meant for FREE help.. No-one wants you here... no-one needs you here... OP look at http://home.tiscali.nl/arracom/whoissteve.html (Website has been updated and has a new 'look'... we have passed 12.000 pageloads... it's a shame !!) Arno R |
#4
|
|||
|
|||
Crosstab vs Flex Grid
"Steve" wrote in message
... 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 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 |
#5
|
|||
|
|||
Crosstab vs Flex Grid
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. On Tue, 20 Apr 2010 07:08:01 -0700, Jaybird5013 wrote: 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. |
#6
|
|||
|
|||
Crosstab vs Flex Grid
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. -- Jaybird |
#7
|
|||
|
|||
Crosstab vs Flex Grid
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. On Tue, 20 Apr 2010 13:28:02 -0700, Jaybird5013 wrote: 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. |
#8
|
|||
|
|||
Crosstab vs Flex Grid
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. -- Jaybird "Jaybird5013" wrote: 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. -- Jaybird |
#9
|
|||
|
|||
Crosstab vs Flex Grid
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. On Tue, 20 Apr 2010 14:39:01 -0700, Jaybird5013 wrote: 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. |
#10
|
|||
|
|||
Crosstab vs Flex Grid
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. -- Jaybird |
|
Thread Tools | |
Display Modes | |
|
|