Student Database - Access Newbie!

[PG Oriel]

Hi, this is what I have so far in my attempt to move from my Excel sheets to
Access. Thanks to Karl Dewey for the following structure:

Use the following table structure –
Student ---
StudentID – Autonumber – primary key
Surname – text
Forename – text
Sex – text – validate “M†or “Fâ€
Birthdate – DateTime

Classes ---
ClassID - Autonumber – primary key
Name – text

StudentClass ---
StudentID – number – integer – foreign key
ClassID - number – integer – foreign key
ClassYear – DateTime – school year start (1 Sep 05)

Behaviour –--
BehaviourID - Autonumber – primary key
Name – text (conduct observed)

Warning ---
WarningID - Autonumber – primary key
Name - text

Incident ---
IncidentID - Autonumber – primary key
Occur – DateTime
StudentID – number – integer – foreign key
ClassID - number – integer – foreign key
BehaviourID - integer – foreign key
WarningID - integer – foreign key
Remarks – memo

I have set the database up as shown. I do not know what foreign key means...
I have just made thes fields. Is there a way for me to let Access know that
StudentID is from another table?

Also, where do I go from here to set up a working example with data entry
forms etc?

Thanks
Paul

 

[Daniel]

PG,

Your going to need to create relationships between your tables and then move
on to create forms and query and eventually reports. The following links is
a good source of information for begginners.

http://www.functionx.com/access/

Enjoy,

Daniel

 

[John Vinson]

Hi, this is what I have so far in my attempt to move from my Excel sheets to
Access. Thanks to Karl Dewey for the following structure:

Use the following table structure –
Student ---
StudentID – Autonumber – primary key
Surname – text
Forename – text
Sex – text – validate “M” or “F”
Birthdate – DateTime

Classes ---
ClassID - Autonumber – primary key
Name – text

Change this to ClassName. The word Name is a reserved word -
everything in an Access database has a Name property (tables, fields,
forms, controls, etc.) and Access WILL get confused.

StudentClass ---
StudentID – number – integer – foreign key
ClassID - number – integer – foreign key
ClassYear – DateTime – school year start (1 Sep 05)

You might want to make all three of these fields a joint Primary Key
(ctrl-click all three so they are highlighted and click the Key icon).

Behaviour –--
BehaviourID - Autonumber – primary key
Name – text (conduct observed)

Again, use some other Name than Name for the field.

Warning ---
WarningID - Autonumber – primary key
Name - text
ditto

Incident ---
IncidentID - Autonumber – primary key
Occur – DateTime
StudentID – number – integer – foreign key
ClassID - number – integer – foreign key
BehaviourID - integer – foreign key
WarningID - integer – foreign key
Remarks – memo

I have set the database up as shown. I do not know what foreign key means...
I have just made thes fields. Is there a way for me to let Access know that
StudentID is from another table?

Open the Relationships window and (e.g.) drag the StudentID field from
the Student table to the StudentID field in the StudentClass table.
Select "Enforce Relational Integrity" in the relationship's
properties. This makes the StudentID field in StudentClass into a
foreign key - it doesn't look or act any different, but that's how the
field is being used. You won't be permitted to (say) enroll a
nonexistant student into a class, or to delete a Class if it still has
students enrolled in the StudentClass table; it protects the integrity
of your data.

Also, where do I go from here to set up a working example with data entry
forms etc?

The Northwind sample database - or any of the innumerable sample
databases online: for starting references see

Jeff Conrad's resources page:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson[MVP]

 

[John Vinson]

Would anyone be able to quickly knock a framework up for this so I can then
play around and see whats happeneing and what is doing what? I understand
this is a lot to ask, but it is the sort of thing that will take me hours and
hours maybe, compared to 5 mins for someone else... If so my email is
(e-mail address removed)

And then I will have spent half an hour or an hour of otherwise
billable consulting time, and you will have learned nothing...

Sorry, this is a volunteer-staffed peer support forum. We'll happily
advise you on setting up your own database and getting over
roadblocks, but what you ask IS in fact more than is traditional here.
If you want someone to do your work for you, it might be worth hiring
someone to do so.

Note that openly posting your EMail address is unwise - spammers
routinely harvest addresses from the public newsgroups, and you're
likely to start getting a lot more SPAM and virus messages. Check your
email carefully.

John W. Vinson[MVP]