Creating Diaries/journal in Access? (1 Viewer)

[JACKSON]

I know I am posting a little late in this thread, but we did actually create a very nice appointment scheduler within acccess. We followed the steps roughly outline dbelow:

1. Create a recordset of appointment slots for the next several years. We used a quick excell program to generate these records, which started at 8:00am every 15 minutes until 8:00pm for three years (i have attached the excell file). After the file generates, you need to a add fields for the specific representative id# in every record. Use excell cut & paste to add this id# value to each record
2. You need to repeat this process and create a separate worksheet for each provider or representative.
3. Import each of the worksheets from the excel file into a base appointments table in your database. Add any other needed fields to the appintments table (reason (lookup field), notes (memo field), client name (lookup field), etc...
3. Build a query that Links the representative # field in the appts table to a cooresponding field in the representative table. Add Representative name to the query
4. Build a Main Appointments form form with a calander control.
5. Add a subform (single form) displaying records from the clients table.
6. Add a combo box that displays the representatives names on the Appts main form.
7. add a subform (Continuous) displaying appointment slots from the appointments query, and make the recordsource on the query tied to the calander control date value (you might need to first create a date field on the main form tied to the calander control) as well as the representative name displayed on the main form. When you use the subform control to insert the appts subform, it should prompt you to select records based on values on the main form (use both the date and client fields to tie it)
8. Create command button on the appts sub form that inserts the client name from the client sub form record.
In this way your appointments subform only displays the date reflected in the calander control, and only those records for that specific representative selected on the main form.

Anyway, It's just one way to do it. I am sure I left things out but you get the idea.

Hope it helps

jackson

 

[JACKSON]

Sorry guys,

I somehow relied on the wrong post.

Jackson

 

[JACKSON]

I know I am posting a little late in this thread, but we did actually create a very nice appointment scheduler within acccess. We followed the steps roughly outline dbelow:

1. Create a recordset of appointment slots for the next several years. We used a quick excell program to generate these records, which started at 8:00am every 15 minutes until 8:00pm for three years (i have attached the excell file). After the file generates, you need to a add fields for the specific representative id# in every record. Use excell cut & paste to add this id# value to each record
2. You need to repeat this process and create a separate worksheet for each provider or representative.
3. Import each of the worksheets from the excel file into a base appointments table in your database. Add any other needed fields to the appintments table (reason (lookup field), notes (memo field), client name (lookup field), etc...
3. Build a query that Links the representative # field in the appts table to a cooresponding field in the representative table. Add Representative name to the query
4. Build a Main Appointments form form with a calander control.
5. Add a subform (single form) displaying records from the clients table.
6. Add a combo box that displays the representatives names on the Appts main form.
7. add a subform (Continuous) displaying appointment slots from the appointments query, and make the recordsource on the query tied to the calander control date value (you might need to first create a date field on the main form tied to the calander control) as well as the representative name displayed on the main form. When you use the subform control to insert the appts subform, it should prompt you to select records based on values on the main form (use both the date and client fields to tie it)
8. Create command button on the appts sub form that inserts the client name from the client sub form record.
In this way your appointments subform only displays the date reflected in the calander control, and only those records for that specific representative selected on the main form.

Anyway, It's just one way to do it. I am sure I left things out but you get the idea.

Hope it helps

jackson

 

[Rich]

Access 95 used to come with a PIM db, search here to see if you can find a link to it

 

[The_Doc_Man]

Now, I'm all for using Access when it is appropriate, but someone ignored a comment in passing that should not be ignored.

Outlook, ugly as it is and expensive as it is, has scheduling functions built-in and ready to run, working far better than re-inventing the wheel, and it works even in a big environment. Having the function there and ready to use can often be an incentive for stepping up to a newer version of Office. Not to mention that, as a member of Office, Outlook will participate in automation objects (COM methods) from Access just as cleanly as any other Office member would. (OK, OK, it's not saying that much, I know, but from a business standpoint, sometimes BUY is cheaper than MAKE.)

The problem with Access doing scheduling is, of course, that scheduling is a SPARSE activity. I.e. data density is low when compared to the places where data COULD be stored. Or, stated another way, SPARSE data has gaps in it. Excel and Outlook don't care, their storage paradigm allows this. But Access datasheets require something to be there in order to be displayed, and this is where "sparse" becomes a problem. I'm not saying you can't do it. But it is a problem.

Here's another way-out thought. Use EXCEL to store the appointments for a week. EXCEL likes sparse data very well. Access can open an EXCEL spreadsheet as an application object, and can manipulate its contents pretty easily. I'm a firm believer in using existing tools to the hilt and developing the minimum extra stuff required to get the job done.

 

[Pat Hartman]

I'm with Doc on this issue. I probably should have jumped in with this thought earlier also. I use Outlook at home and in the workplace. It really shines in the workplace for managing group schedules and meeting planning. It is very easy for one person (assuming that they have permissions) to schedule time for another person. It is great for setting up meetings also. We assign all of our conference rooms as outlook accounts. That way we can include both people and a place in the meeting setup so we can find a time when all resources are available.

 

[JACKSON]

Hi Guys,

I don't disagree with anything said. In my mind it really depends on how customized your scheduling needs are. Ours happend to be very cusomized, since the schedule was develoepd for a large physician practice to schedule patient vists for multiple physicians using multiple resources at multiple sites. We needed built in visit types, an ability to block out slots over the next year by provider, alerts when you try to book a patient who might owe you money or who has missed several apointments, an ability to view the patients insurance info when scheduling an appointment, end-of-day print outs of who missed appts, etc.. We needed to print schedules not only by day, but by the patient's health insurance plans and co-payments due, ect... It was also very important to use to keep the schedule component integrated within the full electronic medical record application, and not require users to go back to their desktop to schedule appts. By integrating within the full application, we could also easily draw on appointment history info in other areas of the program

Anyway, you get the idea, so although I definetely agree outlook is a good tool for basic scheduling needs, I also think that it can be appropriate at times to build it within access.

Anyway, just my 2 cents

Thanks

Jackson

 

[greaseman]

And for what it's worth....that's what we're looking for at my client's site. Something that provides scheduling ability for multiple people on possibly multiple tasks at multiple sites. I too don't agree with anything discussed but don't think my clients would want the Outlook / Calendar approach. Nor do they want anything looking like or behaving like Project. I'm continuing pursuit of the possible Access approach, if others are still interested. The comments and replies are both interesting and appreciated.

 

[Rats]

Diary

Did anything come of this co-op. Looks like getting a good diary is still pretty elusive. I have tried the one referred to in Utteraccess.com and it would fit my needs but it seems flawed. I have sent off some queries on it and hope to hear back.

 

[Rabbie]

My immediate reaction on seeing this thread revived was "Why use Access? Doesn't Outlook do this?" I see in post #25 The_Doc_Man says the same thing. Good luck

 

[Mike375]

My immediate reaction on seeing this thread revived was "Why use Access? Doesn't Outlook do this?" I see in post #25 The_Doc_Man says the same thing. Good luck

So if we are telemarkerting can we use the appointment maker in Outlook? I have never seen or used it.

If I could use OutLook for that it would shave this DB down in size real quick.

Is there a way to use Outlook so it links to the record selection done by Access and allow for the telemarketer to make calls/appointments for more than one salesman during the day.

If you can tell me Outlook does it or can do it then let me know. I am currently helping a friend make a DB that is essentially the same as I have but since it is from "the ground up" we are trying to meet Access rules. That is all OK. However, we are not far away from installing the diary system..if that can be done by outlook.....that will save huge amounts of work. The reason that putting the diary system in is a lot of work is because field names for instance have been changed.....no more CL Surname, Client Suburb etc and in many instances (except SetValue) macros repaced with code.

 

[Rabbie]

Mike,

I am at work just now and don't have access to all my books. I have never linked to Outlook from Access so I would need to look up the exact way to do it. I will post again when I have more info

 

[Mike375]

Mike,

I am at work just now and don't have access to all my books. I have never linked to Outlook from Access so I would need to look up the exact way to do it. I will post again when I have more info

Thanks for your response.

My guess is that outlook would be fine for a stand alone diary. However, I am only guessing (never used it), but no harm in asking.