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
|
|||
|
|||
Date Done and Date Due problems
All,
Hopefully, this is the last piece to this database I have been working on with y'all's help. Here's the setup: tblEventType (EventTypeIDpk, EventName, EventPeriodicity) (Dental Exam– every 365 days, Medical exam-every 730 days, Counseling every 182 days, etc.) (All the events) tblEventWhen (EventWhenIDpk, EventTypeIDfk, EventDate, EventLocation) (Event X happened on 1-1-09 at Hospital) (where and when the event happened) tblEmployee (EmployeeIDpk, LName (Smith, Jones) (Who can attend the event) tblEmpEvent: (EmployeeIDfk, EventWhenidfk)(Which employees were at which events) 1) I am trying to do a report showing (for a given person) what event is done and what still needs to be done. I can set up the “done” part but I can’t get the “to-be-done” part because those items do not have an EventDate. (It just needs to show list of all the possible events and a date next to the ones that he did. It’s the events without dates that aren’t showing up.) 2) DateDiff needs a date to do its calculation. With Jones doing his Medical on 1-1-09 and his dental on 12-2-09 and both of those dates are in the same “EventDate” column, is there a way to do “DateDiff the EventDate if the EventType is X”?? (I am trying to use the EventDate to show when the Event is due based on a periodicity.) 3) Is this set up the best way? I appreciate all your help! V/R Lost |
#2
|
|||
|
|||
Date Done and Date Due problems
Hi
Just trying to get my head around what you are doing. Is tblEventType a generic type of event not related to patients? For example are you setting up a table to say for everyone, dental happens every 365 days? If so, how do you create records for tblEventWith? Do you set them up manually? This leads me on to another question. Do all employees have to have a dental Examination every 365 days, and if so, how do you create the first record? What is the trigger for their events to start? Neville Turbit www.projectperfect.com.au "Lostguy" wrote: All, Hopefully, this is the last piece to this database I have been working on with y'all's help. Here's the setup: tblEventType (EventTypeIDpk, EventName, EventPeriodicity) (Dental Exam– every 365 days, Medical exam-every 730 days, Counseling every 182 days, etc.) (All the events) tblEventWhen (EventWhenIDpk, EventTypeIDfk, EventDate, EventLocation) (Event X happened on 1-1-09 at Hospital) (where and when the event happened) tblEmployee (EmployeeIDpk, LName (Smith, Jones) (Who can attend the event) tblEmpEvent: (EmployeeIDfk, EventWhenidfk)(Which employees were at which events) 1) I am trying to do a report showing (for a given person) what event is done and what still needs to be done. I can set up the “done” part but I can’t get the “to-be-done” part because those items do not have an EventDate. (It just needs to show list of all the possible events and a date next to the ones that he did. It’s the events without dates that aren’t showing up.) 2) DateDiff needs a date to do its calculation. With Jones doing his Medical on 1-1-09 and his dental on 12-2-09 and both of those dates are in the same “EventDate” column, is there a way to do “DateDiff the EventDate if the EventType is X”?? (I am trying to use the EventDate to show when the Event is due based on a periodicity.) 3) Is this set up the best way? I appreciate all your help! V/R Lost |
#3
|
|||
|
|||
Date Done and Date Due problems
NevilleT,
a) Every day, you have a to-do list. b) Some items are just done one-time, others weekly, monthly, annually, etc. c) You have 40 employees that you are tracking their to-do list items. d) You would like to be able to print out reports showing what they did when and what they still have to do and what is overdue. e) Some items are by just one person on that date; other items are done at the same date/time by multiple people (monthly training, etc.) tblEmployees is the list of 40 people. (Bob, Bill, etc.) tblEventType is the To-Do list item and how often (in days) in repeats. The user can update this table via a form.(Arrived to work, annual evaluation, annual ethics classes, annual dental exam, biweekly timecard input, etc.) tblEventWhen is when and where that To-Do list item was accomplished. (On 1-1-90 at the Oak Conference Center, there was a session of sexual harrassment training) (The user types in the where and when and selects the To-Do list item via combobox tied to tblEventType) tblEmpEvent relates the employees records to the EventWhen records (Who did what To-Do list item). The user opens up the form, types in the date, location, and picks the to-do item from a dropdown. In the continuous view subform below, the user selects via combobox the first person and any additional people that did that item at that place on that day. My problems a 1) I don't know how to set it up so that on a report, it shows me (for a particular employee) all the possible to-do list items from tblEventType AND the date accomplished for the ones that are done and the date of when it is due. So for Bill, it should be a list of 60 items with 30 of them having a DateDone and DateDue. The other 30 would be for management to bug Bill about to find out why he hasn't done them. 2) Setting up the DateDue for each To-Do list item. That would be based on when that item was done. But each of the 80 items (unless they were a one-time thing) would have their own DatesDue based on the EventPeriodicity.) 3) I still am not certain that the setup above is the best way to arrange the tables. BTW, we aren't a hospital. We are a military organization. We track readiness items (required DoD and local training, flu shots, annual dental exams, etc.) for all employees. We would like to be able to print out this list of "done" and "not done" items for each person to have that at management meetings. HTH and I appreciate your assistance! VR/Lost |
#4
|
|||
|
|||
Date Done and Date Due problems
Hi
It is getting late here in Australia. Almost 11 so I won't be responding again tonight. If I understand it, I join the happy band and have to have a dental examination. There is no record of that until I actually create a record and say I am going for a dental exam next week. In other words, the record is not created by the system. Once I have a dental exam, the system has the potential to say in another 365 days I am due for another exam. Not sure if this happens programatically, or if I have to create a record manually. If the initial date is there, and there is a frequency in tblEventType, I could create the record programatically. If I get to 400 days since my last exam, there is no record to track the fact I missed my appointment. Also, if I skip the first exam, there is no record to build on programatically. I can't add 365 days to nothing. One suggestion is that when you set up a new user, you automatically create a set of recurring event records for that person. Not sure how you set dates but we can come to that. Maybe they are all today, and you manually adjust them to suit the individual. If you create a module that runs every day, you can create new appointments. For example, search all dental exams in the last year and if they are due to be repeated in the next month, and a record does not exist, create a record for the next dental exam. Next you need to add a date completed, or checkbox for completed field on the tblEventWhen table. You can then see it was due on 1 July, and it was completed on that date (or maybe another date if you use date). That allows you to run reports of what is complete and overdue. Looking at requirements it should be a date so you can report on who did what when. I think the key thing is that tblEventWhen becomes a record of both scheduled date, and date completed. It is not just completed date. Leave another post and I will get back to you tomorrow. Neville Turbit www.projectperfect.com.au "Lostguy" wrote: NevilleT, a) Every day, you have a to-do list. b) Some items are just done one-time, others weekly, monthly, annually, etc. c) You have 40 employees that you are tracking their to-do list items. d) You would like to be able to print out reports showing what they did when and what they still have to do and what is overdue. e) Some items are by just one person on that date; other items are done at the same date/time by multiple people (monthly training, etc.) tblEmployees is the list of 40 people. (Bob, Bill, etc.) tblEventType is the To-Do list item and how often (in days) in repeats. The user can update this table via a form.(Arrived to work, annual evaluation, annual ethics classes, annual dental exam, biweekly timecard input, etc.) tblEventWhen is when and where that To-Do list item was accomplished. (On 1-1-90 at the Oak Conference Center, there was a session of sexual harrassment training) (The user types in the where and when and selects the To-Do list item via combobox tied to tblEventType) tblEmpEvent relates the employees records to the EventWhen records (Who did what To-Do list item). The user opens up the form, types in the date, location, and picks the to-do item from a dropdown. In the continuous view subform below, the user selects via combobox the first person and any additional people that did that item at that place on that day. My problems a 1) I don't know how to set it up so that on a report, it shows me (for a particular employee) all the possible to-do list items from tblEventType AND the date accomplished for the ones that are done and the date of when it is due. So for Bill, it should be a list of 60 items with 30 of them having a DateDone and DateDue. The other 30 would be for management to bug Bill about to find out why he hasn't done them. 2) Setting up the DateDue for each To-Do list item. That would be based on when that item was done. But each of the 80 items (unless they were a one-time thing) would have their own DatesDue based on the EventPeriodicity.) 3) I still am not certain that the setup above is the best way to arrange the tables. BTW, we aren't a hospital. We are a military organization. We track readiness items (required DoD and local training, flu shots, annual dental exams, etc.) for all employees. We would like to be able to print out this list of "done" and "not done" items for each person to have that at management meetings. HTH and I appreciate your assistance! VR/Lost |
#5
|
|||
|
|||
Date Done and Date Due problems
This is what I am having trouble with (Forget all the other stuff above for now. This is fundamental stuff that I need help with.) Who when We Bob and Doug reported to Work on 1/1/09. Bill reported Home on 1-1-09. Bob and Bill reported to Home on 1-2-09., etc. So, I have a tblEmployee (EmployeeIDpk, FName) and a tblEvent (EventIDpk, EmployeeIDfk, EventLocation, EventDate). I set up the Date and Location on a form, and then the employees on a continous subform, but I can never get it to work right. I can't get the tables/forms set up to produce these 5 records in the tblEvent: EventID-EmployeeIDfk-EventLocation-EventDate 1: Bill-Work-1/1/09 2: Doug-Work-1/1/09 3: Bill-Home-1/1/09 4: Bob-Home-1/2/09 5: Bill-Home-1/2/09 etc. VR/ Really lost |
#6
|
|||
|
|||
Date Done and Date Due problems
Hi
Try this download I. I tried to set it up from your description. It is a datasheet but you can make it a continuous form if you want. Is this what you are trying to achieve? http://www.projectperfect.com.au/dow...mp/LostGuy.zip Neville Turbit www.projectperfect.com.au "Lostguy" wrote: This is what I am having trouble with (Forget all the other stuff above for now. This is fundamental stuff that I need help with.) Who when We Bob and Doug reported to Work on 1/1/09. Bill reported Home on 1-1-09. Bob and Bill reported to Home on 1-2-09., etc. So, I have a tblEmployee (EmployeeIDpk, FName) and a tblEvent (EventIDpk, EmployeeIDfk, EventLocation, EventDate). I set up the Date and Location on a form, and then the employees on a continous subform, but I can never get it to work right. I can't get the tables/forms set up to produce these 5 records in the tblEvent: EventID-EmployeeIDfk-EventLocation-EventDate 1: Bill-Work-1/1/09 2: Doug-Work-1/1/09 3: Bill-Home-1/1/09 4: Bob-Home-1/2/09 5: Bill-Home-1/2/09 etc. VR/ Really lost |
#7
|
|||
|
|||
Date Done and Date Due problems
NevilleT,
Yes, that is the start. If you look at post #5 above, I changed it up a little and combined the EmpEvent and the tblEventWhen into one table: tblEvent (EventIDpk, EmployeeIDfk, EventTypeIDfk, EventLocation, EventDate). I noticed that you added a tblLocation (lookup table for locations). But I need to track the employees who were at those events via a continuous subform. So for the form, you would enter the data, enter the event, enter the location (which is what you have on the form you sent), but then enter the employees who were at that event. So for the first date, there could be only 2 employees there. For the next date, there could be 20 employees. One of the tables should be storing Date-Place-Event-Employee, but that isn't happening. (See post #5). I really appreciate you working with me on this! VR/Lost |
#8
|
|||
|
|||
Date Done and Date Due problems
Try the latest version in the download. It has a main form datasheet you
double click to open the individual form. Think one of the issues you might have come up against is that you cannot add a subform or datasheet in a continuous form. Let me know how this works for you Neville Turbit www.projectperfect.com.au "Lostguy" wrote: NevilleT, Yes, that is the start. If you look at post #5 above, I changed it up a little and combined the EmpEvent and the tblEventWhen into one table: tblEvent (EventIDpk, EmployeeIDfk, EventTypeIDfk, EventLocation, EventDate). I noticed that you added a tblLocation (lookup table for locations). But I need to track the employees who were at those events via a continuous subform. So for the form, you would enter the data, enter the event, enter the location (which is what you have on the form you sent), but then enter the employees who were at that event. So for the first date, there could be only 2 employees there. For the next date, there could be 20 employees. One of the tables should be storing Date-Place-Event-Employee, but that isn't happening. (See post #5). I really appreciate you working with me on this! VR/Lost |
#9
|
|||
|
|||
Date Done and Date Due problems
NevilleT,
It has taken me a while to get started on figuring out how you worked this. 1) Form1 does not seem to be used for anything. Double-clicking both fields on Form 3 brings up Form 2. Is Form1 extra? 2) My original question was how to set up the report to show all the things that Jones did, and DIDN'T do based on the periodicity. So Jones did Medical on 1-1-09, and has Medical due in 365 days. He still needs to do his Dental. He did his counseling on 1-2-09, but it is overdue based on a 6-month periodocity. Blank and overdue fields would be colored red to flag them. So the report should look something like: Jones Done Due Medical 1-1-2009 1-1-2010 Dental blank blank (colored red because not done) Counseling 1-2-2009 7-2-2009 (colored red because overdue) This whole thing posting has been how to set this report up so that I can take it to management and say "Here is Jones. His Medical is not due until 2010, but all his other stuff is due now beause he hasn't did it (or it has exceeded its periodicity). The red on the report will go away when he gets that other stuff done." Can this be done in this setup? This is good stuff! VR/Lost (but getting assisted out of the confusion) |
#10
|
|||
|
|||
Date Done and Date Due problems
Hi LostGuy
OK. Form 1 was version 1 so it is now obsolete. Would start with Form 3 to find the event then doubleclick to drill down to form 2. Your problem with reporting is that you are trying to report on something that isn't there. If there is no record, you cannot find it. You need to create the missing information before you can report on it. Going back to an earlier post, if you create records for scheduled events, then you can report on whether they are completed or not. That means a number of things. - You have to create the initial record in the series. Smith had a dental appointment on a certain date. - You need to have some code that says if Smith attended a dental appointment on this date, he is due for his next one on this date + 365 days later, so I will create a record for an appointment on that date. The code to do that is not too complicated. Create a table called tblLatestBatchRun with a field for date run. Every time someone opens the program, if date run is before today. If it is, run the batch to go through all records to see if any new appointment records should be set up. Once done, add a record of today's date in tblLatestBatchRun. Can expand on this if you want. If you played around with a query, you might be able to get a result in terms of overdue appointments but it still relies on the first record being in place. Alternatively, if everyone has to do all the same checks, you might be able to run some code and create a temporary table to use in the report. Would still rely on some sort of date to kick off - like a date joined. All very messy. Let me know if all the records in tblEvent apply to everyone. In other words, does everyone have to go through all the checks. If not, you can forget this approach. "Lostguy" wrote: NevilleT, It has taken me a while to get started on figuring out how you worked this. 1) Form1 does not seem to be used for anything. Double-clicking both fields on Form 3 brings up Form 2. Is Form1 extra? 2) My original question was how to set up the report to show all the things that Jones did, and DIDN'T do based on the periodicity. So Jones did Medical on 1-1-09, and has Medical due in 365 days. He still needs to do his Dental. He did his counseling on 1-2-09, but it is overdue based on a 6-month periodocity. Blank and overdue fields would be colored red to flag them. So the report should look something like: Jones Done Due Medical 1-1-2009 1-1-2010 Dental blank blank (colored red because not done) Counseling 1-2-2009 7-2-2009 (colored red because overdue) This whole thing posting has been how to set this report up so that I can take it to management and say "Here is Jones. His Medical is not due until 2010, but all his other stuff is due now beause he hasn't did it (or it has exceeded its periodicity). The red on the report will go away when he gets that other stuff done." Can this be done in this setup? This is good stuff! VR/Lost (but getting assisted out of the confusion) |
|
Thread Tools | |
Display Modes | |
|
|