By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,059 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

MS Access Calendar

100+
P: 135
Admin Note
This thread has been closed as it has gotten just too big.

If you have any questions about the application discussed in this thread please start a new thread in MS Access by clicking on the Start a Discussion button and link to the this thread in your question.


Original Question
Just wondering...Am I the only one in the access universe that would like an Outlook like calendar option? Is there such a thing as a calendar that can be used for data entry? You know...double click the day and "POP" up comes the event screen to enter data into. I have spent countless hours searching for such a phantom. Any thoughts or sites with a template/example/download?

Admin Edit
For ease of usage I will include a link here to the post in this thread with the latest version of the solution database.

Currently this is at Post #298.

Alternatively, if the attachment is all you require, then you'll find it here (Calendar.zip).

A wide angled version (for wider screen real-estate) has also been posted now at Post #327. The attachment there is Wide Calendar with Switchboard.zip.
Jan 19 '08
Share this Question
Share on Google+
340 Replies


P: 1
Yes I'm back and do apologize. I was wondering how you add data directly into the calendar? Seems to be uneditable. Also, has anyone since created a Calendar report that mirrors the form for printing?

Any help is appreciated!
Jun 23 '09 #101

ADezii
Expert 5K+
P: 8,615
I was wondering how you add data directly into the calendar?
You don't. Data gets added directly into an Event Table (tblEvent), then the Date Range is converted to Single Dates with the appropriate data by Appending to an Intermediate Table (tblEventTemp). The Calendar derives its data from this Intermediate Table. It is a 3, actually 4, Step Process that I have never altered since the Calendar Database is now doing things that it was never originally designed to do.
Jun 23 '09 #102

100+
P: 365
Good day Adezii, how would you allow for constant events (biweekly,monthly etc) can you think of an easy way?

i suspect having a table listing these "perma-vents" (LOL) and the frequency, starting date etc, what i am troubled with is the best way to formulate these?

should i have a function with cases that just dateadds() in the approprite way of can you suggest a better (quicker way)

thanks for any help

Dan
Jul 11 '09 #103

ADezii
Expert 5K+
P: 8,615
@Dan2kx
In the case of the Calendar Application, there is no easy way to implement this logic. Since all data is derived from tblEventTemp, I would:
Expand|Select|Wrap|Line Numbers
  1. Dim MyDB As DAO.Database
  2. Dim rstCal As DAO.Recordset
  3. Dim dteStartDate As Date
  4. Dim intCounter As Integer
  5.  
  6. Const conMAX_NUM_EVENTS As Long = 12
  7. Const conBASE_DATE As Date = #7/1/2009#
  8.  
  9. Set MyDB = CurrentDb
  10. Set rstCal = MyDB.OpenRecordset("tblEventTemp", dbOpenDynaset, dbAppendOnly)
  11.  
  12. With rstCal
  13.   For intCounter = 1 To conMAX_NUM_EVENTS
  14.     .AddNew
  15.       ![Start Date] = DateAdd("m", intCounter, conBASE_DATE)
  16.       ![End Date] = DateAdd("m", intCounter, conBASE_DATE)
  17.     .Update
  18.   Next
  19. End With
  20.  
  21. rstCal.Close
  22. Set rstCal = Nothing
Jul 11 '09 #104

100+
P: 365
Yeah that will do, i can get the dates from another table and update into the read table

Thanks
Jul 11 '09 #105

ADezii
Expert 5K+
P: 8,615
@Dan2kx
You are quite welcome.
Jul 11 '09 #106

ADezii
Expert 5K+
P: 8,615
@donthee
Here is a Calendar that I adapted for a User who wanted to track Employee Vacation Dates and who was covering those Dates. You can use this as a Demo, just double click on any Date to Open the Vacation Request Form.
Jul 14 '09 #107

P: 7
ADezii - intermediate to advanced user that doesn't like re-inventing the wheel, but never had need to have scheduling functions in previous db's - Thanks So Much - downloaded your sample calendars and excited to get started! Thanks again for your generosity with your time/talent
Jul 16 '09 #108

ADezii
Expert 5K+
P: 8,615
@tankera13
Glad to help you, any questions just feel free to ask. Please keep in mind that this Database has been modified to do things that it was never intended to do, so it is not the most efficient example of programming concepts. It is, however, extremely popular, and has been adapted for varied usage.
Jul 16 '09 #109

P: 7
LOL - I've added on to my own db's with things they were never intended to do because once 'customers' find out how easy their life can be, then it's "can you make it do such-and-such?" LOL -
Thanks for the offer of help - I've pretty well designed the structure and laid out what all functionality it needs to have, just wasn't looking forward to writing all the code - but, as I say, self-taught, etc., so thanks for the offer of assistance - (you know, I'd been doing vba coding very elementary for 2 years and a co-worker helped me accomplish what I wanted to and I looked at the code and said, "What's 'Len' mean and what do you use it for besides this? LOL

Thanks!
Jul 16 '09 #110

ADezii
Expert 5K+
P: 8,615
@tankera13
You are quite welcome. good luck in your undertaking.
Jul 17 '09 #111

P: 8
ADezii: Sorry if this is a dumb question, but How is tblEvent populated?
Jul 17 '09 #112

ADezii
Expert 5K+
P: 8,615
@Egwurg
  1. I usually populate tblEvent via a Form interface, entering a Date Range and any other relevant Data via the Form. Sometimes I'll open a Form via a Dbl-Click on any Calendar Date. I just wrote a simple Database including the Calendar functionality, for a Business Entity. This DB tracks Lateness, Call-Outs, Sicknesses, and No Call - No Shows for Employees. They wanted the capability to visually look at a Calendar and see who the culprits are among other things such as Reports, etc. Clicking on a Date expands the data for that Date and displays it in a List Box below the Calendar. If you like, I can modify the Names in the DB for you so as to not expose the real Employee Names.
  2. Keep in mind that the actual Table Data that populates the Calendar comes from tblEventTemp which is dynamically generated and breaks the Date Range in tblEvent into Linear Dates for Calendar display.
Jul 17 '09 #113

P: 8
I used the version of the database posted for tracking postal codes, surnames etc. and tried to tailor to my purposes. Now I'm getting "Run time error 94 -- invalid use of Null" on the following piece of code:

In the private function MonthAndYear --

strMonth = Choose(intMonth, _
"January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")

Any clue what might be causing this?
Jul 20 '09 #114

ADezii
Expert 5K+
P: 8,615
@Egwurg
The MonthAndYear Function is called from code in the PopulateCalendar() Sub-Routine, namely Line # 23 in the posted code below. It appears as though somehow you are passing a NULL Value to either 1 or both of the Function Arguments (intMonth and intYear). These Values are derived from the Month and Year Properties of the Class in Lines 19 and 20 respectively. Try temporarily replacing Lines 19 and 20 with:
Expand|Select|Wrap|Line Numbers
  1. intMonth = 7
  2. intYear = 2009
The MonthAndYear Function should now work verifying that at least 1 NULL is being passed to this Function. After Line# 20, place the following Line of code and see what you get:
Expand|Select|Wrap|Line Numbers
  1. Msgbox intMonth & " ==> " & intYear
Expand|Select|Wrap|Line Numbers
  1. Private Sub PopulateCalendar()
  2. On Error GoTo Err_PopulateCalendar
  3.  
  4. Dim strFirstOfMonth As String, bytFirstWeekdayOfMonth As Byte, bytBlockCounter As Byte
  5. Dim bytBlockDayOfMonth As Byte, lngBlockDate As Long, ctlDayBlock As Control
  6. Dim bytDaysInMonth As Byte, bytEventDayOfMonth As Byte, lngFirstOfMonth As Long
  7. Dim lngLastOfMonth As Long, lngFirstOfNextMonth As Long, lngLastOfPreviousMonth As Long
  8. Dim lngEventDate As Long, bytBlankBlocksBefore As Byte, bytBlankBlocksAfter As Byte
  9. Dim astrCalendarBlocks(1 To 42) As String, db As Database, rstEvents As Recordset
  10. Dim strSelectEvents As String, strEvent As String, strPlatoons As String
  11. Dim intMonth As Integer, intYear As Integer, lngSystemDate As Long  'CFB added 1-25-08
  12. Dim ctlSystemDateBlock As Control, blnSystemDateIsShown As Boolean  'CFB added 1-25-08
  13. Dim strSQL As String    'Added 4/16/2008
  14. Dim blnRetVal
  15.  
  16. 'If Not fPopulateTempTable() Then Exit Sub
  17.  
  18. lngSystemDate = Date    'CFB added 1-25-08
  19. intMonth = objCurrentDate.Month
  20. intYear = objCurrentDate.Year
  21. lstEvents.Visible = False
  22. lblEventsOnDate.Visible = False
  23. lblMonth.Caption = MonthAndYear(intMonth, intYear)
  24.  
Jul 20 '09 #115

P: 8
Adezii: When I replaced lines 19 and 20 with intMonth = 7 and intYear = 2009, that eliminated the invalid use of null error. However, the quickyear dropdown box on the form only showed the digits -12 to 12 rather than the month names. I resyncd and that problem was resolved although I'm not sure what caused it initially. I put the msgbox code after line 20 and it correctly identifies the month and year. At this point, I'm not sure how I am passing the NULL value to the function arguments.
Jul 21 '09 #116

ADezii
Expert 5K+
P: 8,615
@Egwurg
Hello Egwurg. If you like, provide me with the following detailed information, and when I get a chance, I'll try to implement it into the Calendar Database for you.
  • Table Name
  • Field Names and Data Types in your Table. Keep in mind that you will either need a single Date Field for single day Events, or a Start and End Date Fields.
  • What exactly you want displayed within the Calendar, keeping in mind the severe restriction on the amount of data that can be displayed within the Calendar.
  • Sample Data, say 10 Records for the Month of August and Year 2009.
  • Sample detailed information required:
    Expand|Select|Wrap|Line Numbers
    1. Table Name: tblEvents
    2. [Start Date] - DATE/TIME
    3. [End Date] - DATE/TIME
    4. [Event Name] - TEXT(15)
    5. [Supervisor] - TEXT(3)
  • Sample Record
    Expand|Select|Wrap|Line Numbers
    1. [Start Date]   [End Date]   [Event Name]   [Supervisor]
    2.  8/2/2009       8/19/2009    Pocono 2000       JMG
Jul 21 '09 #117

P: 8
Adezii-- I just want to say thank you for all of your help. What I'm actually looking to do is track scheduled reviews that my staff needs to conduct. All events to populate the calendar will be single day events (review date). The start date and end date listed below are for informational purposes only in the context of the review. Rather than a table, I need this to populate from a query. The query is called qryCalendarData which (inner) joins 3 tables (tblLender, tblQIP_Tracking_Archive, tblAll1stReviews).

The fields selected in qryCalendarData are as follows:


tblQIP_Tracking_Archive.ClientID, Number
tblLender.ClientName, Text (30)
tblAll1stReviews.ReviewDate, Date/Time
tblQIP_Tracking_Archive.QIPStartDate, Date/Time
tblQIP_Tracking_Archive.QIPEndDate, Date/Time
tblQIP_Tracking_Archive.QIPAnalyst, Text (30)


Sample Records:

[ClientID],[ClientName],[ReviewDate],[QIPStDt],[QIPEndDt],[Analyst]
123, RandomClient1, 8/13/09, 8/1/09, 12/31/09, Tim
345, RandomClient2, 8/17/09, 8/1/09, 11/30/09, Bill
653, RandomClient3, 8/8/09, 7/15/09, 9/30/09, Susan
12315, RandomClient4, 8/5/09, 6/30/09, 10/31/09, John
357, RandomClient5, 8/22/09, 8/1/09, 12/31/09, Jim
456, RandomClient6, 8/25/09, 7/1/09, 12/31/09, Casey
22456, RandomClient7, 8/3/09, 6/1/09, 11/30/09, Tim
10875, RandomClient8, 8/19/09, 8/1/09, 12/31/09, Martha

Let me know if you need any more information or if any of the above is unclear. Thanks.
Jul 21 '09 #118

ADezii
Expert 5K+
P: 8,615
@Egwurg
Keeping in mind the severe restriction on length of Text to be displayed in the Calendar for a given Date, which Field(s) will be displayed in the Calendar Dates. Do not list [ReviewDate] which will graphically be displayed in the Calendar.
Jul 21 '09 #119

P: 8
Adezii: I'm sorry, I think I misunderstood previously. The fields I listed above that I wanted displayed are actually the fields I want displayed in the bottom portion of the form when you click on the date. The fields that I actually want displayed on the calendar itself are just ClientID and AnalystName.
Jul 22 '09 #120

ADezii
Expert 5K+
P: 8,615
@Egwurg
Hello Egwurg, look at the following Attachment and let me know if this is close to what you want. Any questions, feel free to ask.

P.S. - You will have to make some modifications, especially to the SQL Statement, since you are Joining 3 Tables as opposed to a Single Table in the Demo.
Jul 22 '09 #121

P: 8
Adezii: I took the your revised attachment and inserted the form / VBA into my db and it worked like a charm for a while. But now I'm back to getting the invalid use of null run time error on this piece of code

strMonth = Choose(intMonth, _
"January", "February", "March", "April", "May", "June", _
"July", "August", "September", "October", "November", "December")

in the MonthAndYear function. I'm struggling to figure out how I'm passing the null value. The values in the Quick Year dropdown show from -12 to 12 and the date at the top center of the calendar form shows "January 0".
Jul 23 '09 #122

ADezii
Expert 5K+
P: 8,615
@Egwurg
  1. By any chance, are you from Europe or the United Kingdom?
  2. Do you know how to Set a Breakpoint and Single Step through code?
  3. As a last resort, can you Zip the entire Database and send it as an Attachment to my Private E-Mail Address?
Jul 23 '09 #123

P: 8
1) No, I'm from the US.
2) I'm not sure how to set break points and step through code. I'm familiar with the step into function and a little debugging. My VBA is self-taught and I'm still learning.
3) I will send you a zipped copy of the DB to your private email. If you have time to look at it, it would be appreciated. If not, I understand as I'm sure you're very busy. Thanks for all your help and suggestions to date.
Jul 24 '09 #124

ADezii
Expert 5K+
P: 8,615
@Egwurg
  1. I wish all problems were as easy as this one to solve (LOL)! It appears as though you inadvertently deleted Critical Code from the Open() Event of frmCalendar, probably when you removed my customized message to you in this Event. In the future, before you remove any code, Remark the code lines first by preceding them with an Apostrophe, to make sure there are no repercussions after the deletion. In any event, this code should should reside in the Open() Event of frmCalendar:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub Form_Open(Cancel As Integer)
    2. Dim dtmTodaysDate
    3. Dim strMsg As String
    4.  
    5. dtmTodaysDate = Now
    6. objCurrentDate.Month = Month(dtmTodaysDate)
    7. objCurrentDate.Year = Year(dtmTodaysDate)
    8. End Sub
  2. If there is no Analyst for a given Record, you should not display empty Brackets as in []. You can revise the code that assigns a String to strEvent in PopulateCalendar() in order to eliminate this problem (Lines 2 and 3 below):
    Expand|Select|Wrap|Line Numbers
    1. Do While Not rstEvents.EOF
    2.   strEvent = rstEvents![ClientID] & "  " & rstEvents![ClientName] & _
    3.   vbCrLf & IIf(IsNull(rstEvents![QIPAnalyst]), "", "[" & rstEvents![QIPAnalyst] & "]")
    4.   bytEventDayOfMonth = (rstEvents!ReviewDate - lngLastOfPreviousMonth)
    5.   bytBlockCounter = bytEventDayOfMonth + bytBlankBlocksBefore
    6.     If astrCalendarBlocks(bytBlockCounter) <> "" Then
    7.       astrCalendarBlocks(bytBlockCounter) = _
    8.       astrCalendarBlocks(bytBlockCounter) & vbNewLine & strEvent
    9.     Else
    10.       astrCalendarBlocks(bytBlockCounter) = strEvent
    11.     End If
    12.     rstEvents.MoveNext
    13. Loop
  3. Why the need for Duplicate Records?
  4. Personally, I think you are displaying too much text in the Calendar. Why display ClientID, ClientName, and Analyst in the Calendar when this information is duplicated below in the List Box via a Mouse Click? Display the minimal amount of information in the Calendar (your original request was to display ClientID and Analyst only).
  5. Please do not interpret all this as criticism of your technique, I simply think that there may be more effective approaches regarding certain things.
Jul 24 '09 #125

P: 8
Adezii: I just wanted to say thank you again for all of your help. Everything seems to be working smoothely. I really appreciate it.
Jul 27 '09 #126

ADezii
Expert 5K+
P: 8,615
@Egwurg
Glad I was able to help you out.
Jul 27 '09 #127

P: 2
Thanks, ADezii.
I don't need the QIP / Client review application at all. Do you have a more generic version that lets you enter simple text (event description) and displays it?

Otherwise, I can remove all the Review functionality in the version you posted.
Oct 2 '09 #128

ADezii
Expert 5K+
P: 8,615
@garyo
I don't need the QIP / Client review application at all. Do you have a more generic version that lets you enter simple text (event description) and displays it?
Sorry, I do not have a more generic Version.
Oct 2 '09 #129

P: 2
OK, thanks for checking. I'll see if I can strip it down without harming its basic functionality! When I get that done I'll post it back here.
Oct 2 '09 #130

P: 6
I found this thread, and was so happy! I have to create an appointment-style database for a school project using Access only, with many treatments for one appointment and many appointments in one day. I don't really know VBA though, not enough to implement a calendar from scratch, or even get this working with the ActiveX control one that comes in access.

Would any of the attached test databases posted suit my problem? Thanks.

I also have to include in my design document how I created the forms/tables/queries/etc., so I can't really download a form and use it without explaining how I made it.
Dec 6 '09 #131

ADezii
Expert 5K+
P: 8,615
I think that your initial concern should be how to define the Relationships between the three Entities involved, namely: Dates, Appointments, and Treatments. Since any given Day can have Multiple Appointments, and any single Appointment can have Multiple Treatments, I feel as though you need to create a 1 to MANY Relationship between a Dates Table and an Appointments Table and then a 1 to MANY Relationship between the Appointments Table and a Treatments Table. Download the Attachment to view a Graphical Representation/Relationship between these Tables, then see if you understand the concept. I'm sure that other Moderators/Experts/Members will join in and provide you with invaluable insights on how best to implement this Project. Be advised that we will not do it for you, but only point you in the right direction.
Dec 6 '09 #132

P: 6
Thanks very much for your reply. I was thinking earlier that I could have a date or timeslots table, but I was worried I'd have to end up entering 365 different days into it to start with! Is there any way then to show a weekly calendar sort of thing in a form then? I have appointments and treatments broken down into one-to-many and one-to-many to an appointment-treatment table, as that's how we were told to normalise it in class.

Here are the relationships I have in my prototype at the minute, although I will have to implement the date table somehow as I have date as field on its own in the appointments table:
http://img138.imageshack.us/img138/2...ationships.gif

And here is my prototype appointments form:
http://img138.imageshack.us/img138/2...ntmentform.gif

The appointments can be booked, of course, the main problem is just booking them without any clashes, as there's no easy way to view a schedule or calendar of all other appointments on that day.

The most similar thing I found to what I was looking for was the calendar reports on this page, http://www.access.hookom.net/Samples.htm, but it shows schedules in a report rather than on the form.

Don't worry, I'm not expecting anyone to do it for me, just some guidance in the right direction would be great so I can figure out the rest myself. Thanks again! (Sorry for hijacking this thread also...)
Dec 6 '09 #133

ADezii
Expert 5K+
P: 8,615
Is there any way then to show a weekly calendar sort of thing in a form then?
Yes, but it definitely will not be an easy task. Let me toss it around for awhile since I have a much clearer and concise idea of exactly what you are trying to accomplish, and it is not nearly as simplistic as I originally envisioned.
Expand|Select|Wrap|Line Numbers
  1. The appointments can be booked, of course, the main problem is just booking them without any clashes
Without a doubt. The way I see it is that the combination of 3 factors must be 'Unique', namely:
  1. Date
  2. Practicioner
  3. Appointment 'Interval', namely:
Expand|Select|Wrap|Line Numbers
  1. Between [StartTime] And DateAdd("n", [Length(mins:)], #[<StartTime>]#)
Again, give me a little time to sort this out.
Dec 6 '09 #134

P: 6
Thanks again :) Yes I've also been trying to figure out how to add the length to the start time, since the appointment length will vary depending on the treatments selected within it. I have converted the treatment length into hours/mins in reports, but adding it to make an end time never really worked!

Last year when I did a similar project (wasn't as complicated then, the second year of A-Level coursework has to be far more detailed) I was manually entering the appointment dates/times into an Excel spreadsheet as I was booking them in the forms to prevent clashes, like so:

http://img689.imageshack.us/img689/5...tyearexcel.gif

It worked, it was just very tedious and would have required my imaginary end-user to have some Excel formatting skills and understand what they were doing. Also last year, each appointment only had one treatment haha. But just giving you an idea of what I tried before.
Dec 6 '09 #135

ADezii
Expert 5K+
P: 8,615
I threw together something, paralleling your logic, that you may be interested in. I will not go into extensive details concerning the Demo and how it works. Look it over if you are interested, and then feel free to ask any questions that you may have concerning it.

P.S. - Almost forgot, download the Attachment.
Dec 7 '09 #136

P: 91
Hi Adezii,

I am developing a database for the attendance of our staff in the company. I have created the forms for add/update. I am stuck on the REPORTING section, where I have to display info, according to the attached image for a given month. How can I tune your calendar to give me a report of attendance of different departments of my company. For example if a user selects OCT 2009 from drop down box then the attached report image should display.
Many thanks for guiding & helping me.
Dec 7 '09 #137

P: 91
sorry mistakenly deleted the image. Here it is again
Attached Images
File Type: gif Attendance-Report-Dept.gif (10.1 KB, 1308 views)
Dec 7 '09 #138

ADezii
Expert 5K+
P: 8,615
Hello kashif73, I will need much more detail in order to assist you. I suggest you Download the Attachment in Post #58 in order to see how the Calendar functions.
Dec 7 '09 #139

P: 91
Thanks for replying sir. I have downloaded the Appointments calendar & had a look at it. I am trying to create a monthly report of staff attendance. Is it possible to display a calendar for a particular month like in my image. I mean all the dates should show in 1 row (e.g. from 1st OCT - 31st OCT) at the top and then on the left hand side of my report I have all my department names (HR, Admin, Finance etc). & then against every date I show the total number of staff present for a particular department. And at the bottom I should have a Grand Total for EVERY DAY.
I hope I've been able to make you understand.:( :).
Thanks for helping me.

Kashif
Dec 7 '09 #140

P: 6
Thanks very much ADezzi for your attachment, I'll have a look and see what I can do with it for my coursework. Thanks :)
Dec 7 '09 #141

P: 91
Hi Adezii,

Any thoughts or directions as to how to re-programme it? would be grateful, I need this badly. Thxs.

Kashif
Dec 8 '09 #142

ADezii
Expert 5K+
P: 8,615
I'll need the Name and Row Source of the Combo Drop Down and an example of some Sample Entries, then I'll see what I can come up with.
Dec 8 '09 #143

P: 91
Hi Adezii,

I have attached the sample DB. Many thanks for your help, inputs & guidance.

Kashif
Attached Files
File Type: zip SampleDB.zip (29.4 KB, 274 views)
Dec 8 '09 #144

ADezii
Expert 5K+
P: 8,615
I cannot open the File - Unknown Compression Method
Dec 8 '09 #145

P: 91
Here it is again. I hope it works.
Attached Files
File Type: zip dailyAttendance.zip (30.9 KB, 330 views)
Dec 8 '09 #146

ADezii
Expert 5K+
P: 8,615
Here is a little something that you can work with (Attachment):
Dec 9 '09 #147

P: 91
Thanks Adezii, that's a great help to me & surely puts me on track here:)
Just 2 quick questions please.

1. Is it possible to do a count for number of persons on a given day & display it? e.g. on 1st october there are 4 staff on leave, how can I do a count & display it in the calendar (in the box for 1st october)

2. Is it necessary to create a TEMP table of original employees table? Is there a way of counting leave days without creating this TEMP table n displaying on Calendar? I mean e.g. if Employee X is on leave from 10 Oct 18 Oct, so the TEMP table basically do 9 entries for employee X and then display it on the calendar.
Since we have more than 200 employees in our company so I was probably thinking that this TEMP table could grow huge with the passage of time. will this cause a problem for my DB??

many thanks for bearing with me & your support.

Kashif
Dec 9 '09 #148

ADezii
Expert 5K+
P: 8,615
1. Is it possible to do a count for number of persons on a given day & display it? e.g. on 1st October there are 4 staff on leave, how can I do a count & display it in the calendar (in the box for 1st october)
Expand|Select|Wrap|Line Numbers
  1. SELECT tbl_employees_TEMP.FullName, Count(tbl_employees_TEMP.LeaveDateFrom) AS [Count Of Leaves]
  2. FROM tbl_employees_TEMP
  3. GROUP BY tbl_employees_TEMP.FullName, tbl_employees_TEMP.LeaveDateFrom
  4. HAVING (((tbl_employees_TEMP.LeaveDateFrom)=[Enter Date:]))
  5. ORDER BY tbl_employees_TEMP.FullName;
Is it necessary to create a TEMP table of original employees table?
As far as I know, it is the only way given the Calendar restrictions. The Primary purpose of the TEMP Table is to translate the Date Range in tbl_employees into a Linear Series of Dates suitable for display in the Calendar. It is redundant and probably inefficient, but I know of no other way to do this and I will not redesign the entire Database.
Is there a way of counting leave days without creating this TEMP table n displaying on Calendar? I mean e.g. if Employee X is on leave from 10 Oct 18 Oct, so the TEMP table basically do 9 entries for employee X and then display it on the calendar.
Not that I am aware of.
Since we have more than 200 employees in our company so I was probably thinking that this TEMP table could grow huge with the passage of time. will this cause a problem for my DB??
This is a legitimate concern, but the current logic requires this approach. The Records in the TEMP Table are dynamically Deleted and Appended, that is why the suggestion for periodic Compact/Repair as indicated in the Opening Form.
Dec 9 '09 #149

P: 91
Thanks for all the support Adezii, you've been a great help to me. really appreciate that.
Dec 9 '09 #150

340 Replies

This discussion thread is closed

Replies have been disabled for this discussion.