Relations betwen tables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi i am a beginner in vba
i want some examples of relations between tables (the type of the key,etc...)
and some examples of querys

thank u very much
 
Table relationships and queries aren't really VBA questions. You might want to
try one of the other groups or you might want to consider buying a good book
about Access. There's a ton of them out there and you can get them pretty cheap
via Amazon.com.

HTH,
RD
 
i want some examples of relations between tables (the type of the
key,etc...) and some examples of querys
Click to expand...


Students (
EnrolmentNumber Autonumber Primary Key,
FirstName Text(32),
LastName Text(32),
)

Courses (
CourseCode Text(5) Primary Key,
FullName Text(64),
Cost Currency,
etc
)

Registrations (
Student LongInteger
foreign key references Students(EnrolmentNumber),
Course Text(5)
foreign key references Courses(CourseCode),
DatePaid datetime,
etc

Contraint Primary Key (Student, Course)
)

TutorsOn (
Course Text(5)
foreign key references Lecturers,
StaffID LongInteger
foreign key references StaffMembers

constraint Primary Key (Course, StaffID)
)


This means that there is a relationship between Registrations and
Students (every Registration must apply to exactly one Student, although
an individual student may have several registrations); and another one
between Registrations and Courses (each Registration is for exactly one
Course but each course will usually have several students Registered on
it). The PK is made up of the two fields, which means that the same
student can register for the same course only once.

Pretty much the same situation is created by

Courses -< TutorsOn >- StaffMembers

Note that foreign key data types and sizes must match exactly whatever
they are referencing (usually the Primary Key of the target table). Note
also that an Autonumber is a Long Integer (albeit one with a fixed method
for setting it and not changing it) and that is why FKs that reference
autonumbers have to be Long Integers.

As long as the table design is right, queries are dead simple.

Hope that helps


Tim F
 
Back
Top