View Single Post
  #5  
Old June 4th, 2010, 10:26 PM posted to microsoft.public.access.tablesdbdesign
Jörn Bosse[_3_]
external usenet poster
 
Posts: 20
Default Help needed with table design/layout for newbie

When you setup relations, the fields need the same type: both integer
values. As it seems, your tblSessionMembers is the table to create the
m:n-relationship between members and sessions. Thats why you have to put
your yes/noe-field into this table. If you realise it that way, you can
directly see who has paid by selecting the session, or which session a
user has already paid by selecting the user.
But your entitity-relationship-model seems to be fine at all.

How to create a m:n in your case. Open the Relation dialog and add the
tables: tblSessionMembers, tblmembers, tblSession.
Then creat a relationship using drag and drop. Drag from the Primkey and
drop it on the foreignkey.

Regards
Jörn.

Am 04.06.2010 22:49, schrieb jase118:
jase118 wrote:
Hi, i have just joined the forum and have built a few DB's in the past but
found i have forgotten almost everything!

[quoted text clipped - 41 lines]
Cheers
Jase


just received this from another forum, what do you think as a start point?

You can use Access automation with Outlook to send out e-mails, but that is
down the road at this point. The table structure is the most critical aspect.

You'll need tables for members and courses

tblMembers
-pkMemberID primary key, autonumber
-txtFName
-txtLName

tblCourses
-pkCourseID primary key, autonumber
-txtCourseName

You'll also need a table for the course dates or sessions

tblSessions
-pkSessionID primary key, autonumber
-dteStart (session start date)
-dteEnd (session end date)
-fkCourseID foreign key to tblCourses

Now you'll need to associate the members with the applicable session

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers

In order to determine if there are vacancies in a particular session, you
will need to know the maximum # of students/session or course. If a session
is limited by its location, then the field for the max # belongs in the
session table. If the max # only depends on the course, then it belongs in
the tblCourses. The same would be true for the course fee. If the fee is
dependent on the session (you might charge less for sessions conducted in the
off season versus those conducted during the season), then the field belongs
there. If the fee is just dependent on the course, then it belongs in
tblCourses.



My Table layout so far:

tblMembers
-pkMemberID primary key, autonumber
-txtFirstName
-txtLastName
-txtAddress1
-txtPostcode
-numTel
-numMobile
-txtEmail

tblCourses
-pkCourseID primary key, autonumber
-txtCourseName
-numMax (maximum places available on the course)
-curPrice

tblSessions
-pkSessionID primary key, autonumber
-dteStartDate (session start date)
-dteEndDate (session end date)
-fkCourseID foreign key to tblCourses

tblSessionMembers
-pkSessionMemberID primary key, autonumber
-fkSessionID foreign key to tblSessions
-fkMemberID foreign key to tblMembers


As for tracking who has paid, would i be right to put that under tblSessions
with a yes/no option?
I am not sure how to setup a foreign key, went into relationships and setup a
1 to many link, is this correct?