Access beginner looking for simple instructions! (1 Viewer)

neilq5 · Mar 26, 2012

So basically my table layout is like this:
Appt
ApptDate
ApptStartTime
ApptEndTime
ApptNotes
ApptLocation
ApptInstruct
ApptBike

my ApptInstruct and ApptBike Fields are both combo boxes.
Based on the ApptDate and Start and End Times, i want to create a constraint that will block me using the same instructor and bike during the given date/time.
will this be difficult?

neilq5 · Mar 28, 2012

anyone at all?

RJGoodhead · Mar 28, 2012

To get more help you'll need to describe your problem more thoroughly.
From waht I can tell though you want to create a database that allows you to control the booking of appointments and which bikes and instructors are used.

First you need to plan your tables. I'd imagine you need at least 3
Table 1 : Appointments
Table 2 : Instructors
Table 3 : Bikes
(Table 4 : Customers)?

Each table holds data unique to that table eg Intructors might have First Name, Second Name, Phone number, address line 1, line 2 etc.

You then need to think about how these relate, e.g. each appointment will have a bike and instructor assigned

Draw all this out and make sure no data is repeated. The possibilites for what to do next are vast and depend on your requirements.

Read the Access help information on tables, relationships and queries. Then let us know what you've come up with and what you'd like to achieve.

Also: Try to avoid using the lookup wizard on tables, lookups can be added on forms later and it's much better to do it this way.

jdraw · Mar 28, 2012

http://www.rogersaccesslibrary.com/Tutorials/Whatisnormalization.zip

neilq5 · Mar 28, 2012

RJGoodhead said:
To get more help you'll need to describe your problem more thoroughly.
From waht I can tell though you want to create a database that allows you to control the booking of appointments and which bikes and instructors are used.

First you need to plan your tables. I'd imagine you need at least 3
Table 1 : Appointments
Table 2 : Instructors
Table 3 : Bikes
(Table 4 : Customers)?

Each table holds data unique to that table eg Intructors might have First Name, Second Name, Phone number, address line 1, line 2 etc.

You then need to think about how these relate, e.g. each appointment will have a bike and instructor assigned

Draw all this out and make sure no data is repeated. The possibilites for what to do next are vast and depend on your requirements.

Read the Access help information on tables, relationships and queries. Then let us know what you've come up with and what you'd like to achieve.

Also: Try to avoid using the lookup wizard on tables, lookups can be added on forms later and it's much better to do it this way.

Thanks for your reply,
I've attached a picture of my relationship layout below, do you think that's okay?

Basically, i'm wanting it to work like a diary planner,
where i can view a calendar, click on a particular date and have a form open and i will be able to enter an appointment in on that date.
the main features i need is:

Have an interactive calendar, in monthly view that i can see what days have appointments.
Avoid overbooking of Bikes and Instructors
Make the interface as userfriendly as possible.

I don't know much about writing code in VBA, the most i have done is made a login with username and password.

Quick Question: instead of having an Autonumber for the likes of Customers and the Bikes as the primary key, would it cause problems having the License number for the customer and the registration number for the bikes as a primary key, since each individual one would be unique?

thanks for the help

The John Rambo · Mar 29, 2012

Quick Question: instead of having an Autonumber for the likes of Customers and the Bikes as the primary key, would it cause problems having the License number for the customer and the registration number for the bikes as a primary key, since each individual one would be unique?

It is better to store numbers than text. When storing text, Access stores the amount of data that the field size will permit E.g. Field size is set to 50, if the data is 3 characters long or 25 characters long it will use the same space that 50 characters would. This increases the size (MB) of your database. Access also performs searches much faster with numbers. You may not notice the difference immediatly but as your database becomes more complex these factors are compounded.

If you'd like to show the Registration instead of the ID on your form, this can be done by using a combo box on your form.

One solution that I can think of (without using VBA, more of a workaround) would be to:

1. Add a new tbl_DATES table with (DateID, Date). Add field DateID to tbl_Appointments and update relationships.
2. Create a Main Form (e.g. frm_Dates) with tbl_DATES as the Record Source.
3. Create a Subform (e.g. frm_Appts) on the Main Form with the tbl_Appointments as the recordsource. Set the "Default View" property of the form to "Continuous Forms".

Here you will be able to select a date using a combo box or scroll through the dates on the Main Form and see the appointments for that date below on the subform. You can use the same method for month/year.

Like I said, not the best way and there is probably an easier solution out there. Consider using a VBA

?

neilq5 · Mar 29, 2012

The John Rambo said:
It is better to store numbers than text. When storing text, Access stores the amount of data that the field size will permit E.g. Field size is set to 50, if the data is 3 characters long or 25 characters long it will use the same space that 50 characters would. This increases the size (MB) of your database. Access also performs searches much faster with numbers. You may not notice the difference immediatly but as your database becomes more complex these factors are compounded.

If you'd like to show the Registration instead of the ID on your form, this can be done by using a combo box on your form.

One solution that I can think of (without using VBA, more of a workaround) would be to:

1. Add a new tbl_DATES table with (DateID, Date). Add field DateID to tbl_Appointments and update relationships.
2. Create a Main Form (e.g. frm_Dates) with tbl_DATES as the Record Source.
3. Create a Subform (e.g. frm_Appts) on the Main Form with the tbl_Appointments as the recordsource. Set the "Default View" property of the form to "Continuous Forms".

Here you will be able to select a date using a combo box or scroll through the dates on the Main Form and see the appointments for that date below on the subform. You can use the same method for month/year.

Like I said, not the best way and there is probably an easier solution out there. Consider using a VBA ?

I would consider using VBA i just need a lot of guidance doing it.
I can't seem to get the grasp of it
Where would i start?

The John Rambo · Apr 5, 2012

Where would i start?

Try these sites... They may help.
*See attached Notepad file
This forum has also helped me alot by just reading the questions and the replies.

I suspect you will need these two functions, not only to solve this problem but in many others. I use them quite regularly.
1. Dlookup()
*See attached Notepad file

2. If, Then, Else, End IF
*See attached Notepad file

It is a long, frustrating journey but once you get the hang of it it's actually pretty fun.
Attempt to write some code for this problem and post your code when you are running into problems.

Never work on your original database when learning to code, you may corrupt your database and lose it

. Always make a copy and work on the copy. Make a sample database or a dud copy of your actual database and start messing around with it to achieve what you are looking to do. Once you are happy that the code is doing what it is supposed to do, then implement it in your database.

PS: I can't insert links in posts yet, hence the notepad file. I haven't reached 10 posts yet

.

Pat Hartman · Apr 6, 2012

Something like this in the form's BeforeUpdate event will look for any conflicts and prevent the new appointment from being saved if it conflicts

strWhere = "ApptDate = " & Me.ApptDate & " AND ApptBike = " & Me.BikeID & " AND " & Me.ApptStartTime & " <= ApptEndTime AND "
strWhere = strWhere & Me.ApptEndTime & " >= ApptStartTime"
If DCount("*", "YourTable", strWhere) >0 Then ' there is at least one conflicting appointment
Msgbox "Please change this appointment, it conflicts with one already scheduled.", vbOKOnly
Cancel = True
Exit Sub
End If

Access beginner looking for simple instructions! (1 Viewer)

neilq5

Registered User.

neilq5

Registered User.

RJGoodhead

Registered User.

jdraw

Super Moderator

neilq5

Registered User.

Attachments

The John Rambo

Baggins

neilq5

Registered User.

The John Rambo

Baggins

Attachments

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread