Help with a timetable Table

S

Sheila

I am a very new Access user, so I hope I manage to explain what I need...!
I am trying to build a database to record details of students attending
various classes for a dance school. I have tables as follows:

Student personal details
Class timetable (fields show day, teacher, venue, dance type)
Class v Student (links students with the classes they attend)

It is the Class v Student table that I have a problem with. Fields as follows:
1) Student Name (created a "look-up" control so I can't mis-spell names -
all ok
2) Class attended!!!!!!!!

I need to be able to only enter the details of a valid class (as per the
class timetable) and I need the table to then show me the FULL class details
(ie day, teacher, venue, dance type). At the moment, I have produced a
look-up control which is great because when I enter the information, I can
see EXACTLY the class I want to link to a student. BUT, the table itself does
not display ALL that information once I've selected it which therefore makes
verification of data entry a bit tricky. It only displays the text contained
within the first column of the class timetable - in this case it is the day
(Mon, Tues etc) the class takes place.

So, I then created extra fields in my class v student table, to show Day,
teacher, venue etc. I thought this would work, but it seems that I still have
to select a teacher, then a venue, then a day, then a dance type. This means
that I could get it all wrong. I expected to be able to select the class I
wanted and then all the data is entered in the relevant fields (a bit like
predictive texting).

My Class timetable will correctly show that teacher X teaches tap at venue Y
on Monday but the way I have my class v student table set up, I could enter a
student name against a teacher X who could teach ballet at venue C on
Thursdays and my Class v Student table will happily accept it! I need it to
only accept the details from the class timetable table without being able to
mix and match.

I also tried setting up the venues and days of the week etc in separate
tables, but of course this made no difference.

I feel sure that there is something quite basic that I am getting wrong, but
I just can't see what. I can't put all the class details intogether in one
field, because I need to run reports/registers, by each of the fields (eg
sort by day, venue or teacher). Because I am a new user, even with the help
of a manual, I am at a loss.

Please can anyone tell me what I am doing wrong.
 
S

Sheila

I have come back to this problem and can liken it to something in the
Northwwind database. If you change a product in the order details table but
leave the pricing unaltered, an invoice can be produced with incorrect costs.
I changed all the products on order no 10248 but left the prices as they
were. When I checked the invoice, I would have charged the customer $10 for
something that should have been $84. Surely this can't be right if there is
no validation.
 
L

Larry Daugherty

First get rid of the "lookup control". Design your tables to
accurately reflect the data. see www.mvps.org/access regarding the
evils of lookup fields in tables. That site is one of the greatest
resources available to Access developers.

Bear in mind that most of the issues of how you want your data to look
and how to enter and edit data are best are best resolved using Forms.
Don't use tables for data entry and massage. Tables give you limited
protection. You'll be able to manage your data much more powerfully
by using Forms.

Typically your Form will be based on a Query which will be based on
one or more Tables.

For some great resources, you might google on John Vinson and
"tutorial" or "learning" in this and other Access newsgroups. John
posts a great list of resources including a tutorial by Crystal which
is great for beginners.

HTH
 
E

Evi

I think you'll find this link useful
http://www.teacherclick.com/access2003/epp_11_4_2.htm
It shows you how to build a form with a subform for what could be your very
scenario

Your Main Form will be Class TimeTable.
Your Subform will be ClassVStudent
You will put a combo box into the subform based on the Student table to
allow you add a student to a class.

I would suggest another couple of tables too, to be used as lookup tables
TblTeacher
TblDanceStyle

(both would be foreign key fields in TblClassTimeTable)


BTW your structure is great to show which students have enrolled for a class
but if you want to use it as a register to record who actually attends a
class, then you will need a further table.
Evi
 
S

Sheila

Hi Larry and Evi,

Thank you both for taking the time to reply. I have taken on board the
advice you have both given me and am beginning to understand how it all works
now...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top