Appointment Booking System (1 Viewer)

Hey Guys,

I am looking to create an Appointment Booking system which will allow you to Book, View, Edit and Delete appointments relating to a specific contact. I would like it to display the customer's contact details, show bookable dates and times (15 min intervals), have an overview of which appointments you have booked, create pop-up reminders for the DB user, allow you to send "Confirmation" and "Reminder" emails to the customer and allows you to record details about the appointment on the form.

The way I would like the form to look:

Main Form:
Customer ID
First Name
Last Name
Home Phone
Mobile Phone
Street
City
State/Province
Zip/Postal Code
Country/Region
Email Address
Opt-Out? (Y/N)
Notes
(This form should have a button which will allow you to "Book Appointment".)
I am then looking to have 2 subforms:

Future Appointments: (Shows Upcoming Appointments for this contact)
Appointment Date
Appointment Time
Notes (About this particular Call)
Registration Link Sent (Y/N)
Registration Follow Up (Y/N)
Registration Complete (Y/N)
Inactive (Y/N)
Opt-Out (Y/N) (this should link to the Opt-Out on the customer details form if possible)

Past Appointments: (Shows past appointments for this contact)
Appointment Date
Appointment Time
Notes (About this particular Call)
Registration Link Sent (Y/N)
Registration Follow Up (Y/N)
Registration Complete (Y/N)
Inactive (Y/N)
Opt-Out (Y/N) (this should link to the Opt-Out on the customer details form if possible)
I also need a schedule form which will show you an overview of what appointments you have on that particular day/week/month etc.
I would like a form specifically to create appointments. When you select the customer name that you are creating the appointment for, it should autofill their contact details.
I would like the database to create pop up reminders for the user 15 minutes before an appointment is due to start.
I would like it to be able to send Confirmation/reminder emails to the customer.
You should not be able to double book an appointment, therefor it should have a form showing you which dates/times are available for booking.
The database that I am looking to add this information to is on my OneDrive. It won't let me add a link at the moment but if you need it, let me know and ill find a way to post it.

The login information for the database is Username: admin Password: admin

I know there is a lot of information looking to be put in the system but I can't figure out how to do it.
Any assistance would be much appreciated.

tAppointments table:
--------------
ApptID (autonum)
PersonID
StartTime (full date/time)
EndTime
ClientID
Subject
Room
Notes
etc....

'those marked to attend, child table to tAppointments
tApptAttendees table
ApptID* (long)
PersonID*

you would need code/queries to show upcoming events.
forms to input data.

All things are possible but you are starting from the wrong end - you need to start from the tables, get the relationships right and fields in the right tables, the forms come later.

By the looks of it you need an minimum of two tables, one for customers and one for appointments. Also be clear about what fields mean - inactive means what for example? And how does it relate to an appointment?

First tip: It doesn't matter about forms at this stage of development, you need to work on tables. Tables are the foundation of any data system. Some of the UI features you've discussed here may be relevant to deciding on your table design, but stop. Back up. Read about database normalization. Begin to understand how to store data in tables, and questions you should be asking at this stage should be all about tables. None about forms.
hth
Mark

CJ_London said:

All things are possible but you are starting from the wrong end - you need to start from the tables, get the relationships right and fields in the right tables, the forms come later.

By the looks of it you need an minimum of two tables, one for customers and one for appointments. Also be clear about what fields mean - inactive means what for example? And how does it relate to an appointment?

Click to expand...

At the moment, I have a table for appointments (Fields are: CustomerID, AppointmentDate, TimeSlot, ReasonForCall). I also have a table which stores the customer details. The field "Inactive" wasn't actually supposed to be there. I meant to remove that when I added Opt-Out.

Ranman256 said:

tAppointments table:
--------------
ApptID (autonum)
PersonID
StartTime (full date/time)
EndTime
ClientID
Subject
Room
Notes
etc....

'those marked to attend, child table to tAppointments
tApptAttendees table
ApptID* (long)
PersonID*

you would need code/queries to show upcoming events.
forms to input data.

Click to expand...

I have an appointments table which I will update to include the above information. I don't get what you mean by
'those marked to attend, child table to tAppointments
tApptAttendees table
ApptID* (long)
PersonID*

MarkK said:

First tip: It doesn't matter about forms at this stage of development, you need to work on tables. Tables are the foundation of any data system. Some of the UI features you've discussed here may be relevant to deciding on your table design, but stop. Back up. Read about database normalization. Begin to understand how to store data in tables, and questions you should be asking at this stage should be all about tables. None about forms.
hth
Mark

Click to expand...

I'll have a look into that. Thank you!

Suggest you describe your business and what the app is intended to do - clearly responders are taking different views on what that is

So appointments for what? hairdresser? physio? bank manager?

What resources are required? a room? chair? someone to meet?

CJ_London said:

Suggest you describe your business and what the app is intended to do - clearly responders are taking different views on what that is

So appointments for what? hairdresser? physio? bank manager?

What resources are required? a room? chair? someone to meet?

Click to expand...

I suppose that would be helpful information! My bad!
The app is going to be used for recruitment purposes. The Customers table will store all of their contact details, I will book telephone interviews and use this application to view all information about the lead/customer including appointments etc. There are no specific resources required. Does that make sense?

Sort of - so the customer is a company looking to recruit someone?

Are you a one man band or do you have a team of recruiters? If so don't you want to know which recruiter (which is a resource) and their availability before making an appointment?

From how you have your form structured I don't see a company name - and if a company isn't it likely they will have more than one person to meet - perhaps the end client and someone from HR?

And where would the appointment be? at your premises? the clients premises? somewhere more discreet? What if the client has multiple premises?

CJ_London said:

Sort of - so the customer is a company looking to recruit someone?

Are you a one man band or do you have a team of recruiters? If so don't you want to know which recruiter (which is a resource) and their availability before making an appointment?

From how you have your form structured I don't see a company name - and if a company isn't it likely they will have more than one person to meet - perhaps the end client and someone from HR?

And where would the appointment be? at your premises? the clients premises? somewhere more discreet? What if the client has multiple premises?

Click to expand...

So the Customer is the Recruitee. I am using the "Customer Service" Template that Microsoft provides and it would be too much hassle changing "Customer" to "Lead".
There will be more than recruiter (Didn't realise those classed as resources). I would like to know their availability before booking said appointment. There is a company as it's individual recruitees.
The appointments will be carried out over the telephone. No travelling required.

CJ_London said:

Sort of - so the customer is a company looking to recruit someone?

Are you a one man band or do you have a team of recruiters? If so don't you want to know which recruiter (which is a resource) and their availability before making an appointment?

From how you have your form structured I don't see a company name - and if a company isn't it likely they will have more than one person to meet - perhaps the end client and someone from HR?

And where would the appointment be? at your premises? the clients premises? somewhere more discreet? What if the client has multiple premises?

Click to expand...

Here is the Database. Username is Admin and the password is Admin.
View attachment Sponsoring Database Blank.zip

so you need a table for recruiters, another for their availability and to book appointments - they may not be available because they are on holiday, sick, admin meetings, whatever so you really need a diary system as well. You may also need to record things like public holidays, maybe timezones etc.

You still have not explained how your business actually works - the processes, the people so any help is going to be vague at best.

Recommend you don't just dump a whole database and expect someone here to help. Focus on one thing at a time - but as previously advised, start with the table structure.

CJ_London said:

so you need a table for recruiters, another for their availability and to book appointments - they may not be available because they are on holiday, sick, admin meetings, whatever so you really need a diary system as well. You may also need to record things like public holidays, maybe timezones etc.

You still have not explained how your business actually works - the processes, the people so any help is going to be vague at best.

Recommend you don't just dump a whole database and expect someone here to help. Focus on one thing at a time - but as previously advised, start with the table structure.

Click to expand...

Thanks for this! The reason I posted the DB was to show what information I already have. I would like to apologise if it looked as though I was expecting someone to complete it for me.

What information do you need to understand how the business works? Short version, it's a distribution business. I have a separate database which records all of the orders etc. but this database will be used for the recruitment process. For recruitment, Applications are received and their contact information is placed on the database attached above. It will then be used to book appointments and record any information related to their application and recruitment.

On the database, I have a table for "Employees", which I assume will be the same as the one you suggested for "Recruiters"?

For the "Availability" table, what would the required fields be? I already have one that shows different times of the day (15 minute intervals), and I have a table that shows dates for the next 10 years, but what else - if anything - do I need to add?

Diary System - What information do I need for this?

I really do appreciate all of your help! I am fairly new to Ms Access. I learned the basics in high school like 10 years ago but I am pretty much learning as I go. Everything else I have added to databases in the past I have been able to find the answers pretty quickly online. With this appointment booking system, I have been really struggling. :banghead: