New Access User Unsure...

[Glen Chaplin]

I am relatively new to database design, and completely
new to Access. I am designing a database for booking and
viewing riding lessons. A typical booking would be: A
certain RIDER books a lesson on a certain DATE, at a
certain TIME, with a certain INSTRUCTOR, on a certain
HORSE.

I want to create a form that will do the following:

For a selected DATE, show (in column format, one column
per INSTRUCTOR) all RIDERs booked at each TIME and the
HORSE they are booked to ride.

The form should also permit the booking of additional
lessons (any "free" HORSEs available at each TIME should
have an empty space on the form to be filled with a
RIDERs name).

I have given a lot of thought towards designing the
necessary tables, but I am struggling with the concepts
of making a form do what I want.

Can anyone tell me if what I am trying to do is even
feasible?

 

[fredg]

I am relatively new to database design, and completely
new to Access. I am designing a database for booking and
viewing riding lessons. A typical booking would be: A
certain RIDER books a lesson on a certain DATE, at a
certain TIME, with a certain INSTRUCTOR, on a certain
HORSE.

I want to create a form that will do the following:

For a selected DATE, show (in column format, one column
per INSTRUCTOR) all RIDERs booked at each TIME and the
HORSE they are booked to ride.

The form should also permit the booking of additional
lessons (any "free" HORSEs available at each TIME should
have an empty space on the form to be filled with a
RIDERs name).

I have given a lot of thought towards designing the
necessary tables, but I am struggling with the concepts
of making a form do what I want.

Can anyone tell me if what I am trying to do is even
feasible?

Yes it is feasible.
Two things first.
1) Read the applicable Microsoft KnowledgeBase article regarding the
use of Reserved words as Field names. I see you using Date and Time
as possible field names and that would not be a good idea.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) You are wanting to make what is essentially a reservation system.
A good place to gather more information before you get started would
be to search for "Access reservation database" (without the quotes)
at:
http://www.groups.google.com

You might wish to bookmark this site. It contains archived newsgroup
articles going back many years.
Good Luck.

 

[PC Datasheet]

Glen,

You have too many dimensions you are trying to show! You're thinking in three
dimensions while you can only display two dimensions on the screen. You might
start with one form showing time down the left side, instructors across the top
and booked riders at the intersection of the rows and columns. You could then
add a button on the form that opens a similar form for the same date that has
time down the left side and Horses across the top with booked riders at the
intersection of the rows and columns. You could flip between the two forms to
see which instructors are booked and which horses are booked. You could also add
code to the click event of each row filed in each form that opens a popup form
with all the details about that specific booking.

 

[Glen Chaplin]

-----Original Message-----


Yes it is feasible.
Two things first.
1) Read the applicable Microsoft KnowledgeBase article regarding the
use of Reserved words as Field names. I see you using Date and Time
as possible field names and that would not be a good idea.

109312 'Reserved Words in Microsoft Access'
209187 'Acc2000: 'Reserved Words in Microsoft Access'
286335 'ACC2002: Reserved Words in Microsoft Access'

2) You are wanting to make what is essentially a reservation system.
A good place to gather more information before you get started would
be to search for "Access reservation database" (without the quotes)
at:
http://www.groups.google.com

You might wish to bookmark this site. It contains archived newsgroup
articles going back many years.
Good Luck.

--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
.

Fred,

Thanks the information, and warning about reserved field
names. I'll go and check out the newsgroups and see if I
can pick up some tips.

Glen

 

[Glen Chaplin]

Hi there,

I know what you mean, when I first came up with what I
wanted to show on the form I thought it was perhaps a bit
over the top. My solution (theoretical solution!) was to
do as you suggest, with times down the left of the form
and instructors along the top. Then at each
intersection, have ten "entries", one per horse. So -
the form would have x rows, where x is the number of time
slots in the day multiplied by the number of horses.

It's the implementation of this that is worrying me. Can
I make a form that intelligent, so it knows where to
display each booking.

I guess it also impacts on my table design. I've settled
on a Riders table, an Instructors table, a Horses table,
and a Bookings table. I'm wondering whether I need
a "Days" table, to be the base table for the form (to
enable me to move back and forward, treating one record
as a day's lessons).

I like your idea about a click event to bring up
additional details.

 

[PC Datasheet]

Glen,

If everything in Access is designed correctly, all your data should display
down. If ever you see data displayed horizontally without doing something
special, that should throw up a red flag to you that something is wrong in the
design. For example, if you open a form and see the instructors displayed across
in columns, the design of your tables is wrong. The only way for the instructors
to appear in columns without doing something special is to have separate fields
in a table for each instructor and that design is wrong!

That being said and your having the correct design of your instructors table, if
you want to display instructors across the top, you can use a crosstab query.
Time would be the row headings, instructors would be the column headings and the
booking would be the value at the intersection of the rows and columns. When you
look at how a crosstab query is constructed, you will see that you can only have
one value at the intersection of each row and column. This then tells you that
<<Then at each intersection, have ten "entries", one per horse>> is not
possible. So that leaves you with two forms, time vs instructors and time vs
horses as the only feasible solution to what you want to display.

The crosstab query also automatically takes care of this issue for you - <<Can I
make a form that intelligent, so it knows where to display each booking.>>

Steve
PC Datasheet