Prev: Linking two tables - a bit different
Next: Simple hack to get $500 to your home.
From: jase118 via AccessMonster.com on 6 Jun 2010 06:23
Hi, I have used this and many other forums to develop what i believe is a
good table layout and have established my relevant relationships between the
tables.
My knowledge of Access is limited (i have built DB's but a long time ago).
I am now trying to build the second phase of my database, the queries.
My DB is a water sports booking system and we are run on a low budget so i
can not afford to get this done professionally.
The functionality i need is to:
Add/remove Courses
Add/remove Sessions to the courses (a course may run 5 sessions a year)
Add/remove members to each course

I require a lot more functions down the line but this is the basics of my DB.
I am not sure were to start with bringing all the tables together to get the
desired results.
This is my table layout:
tblMembers
-pkMembersID
-txtFirstName
-txtLastName
-fkRank
-txtNumber
-txtAddress1
-txtAddress2
-txtCity
-txtCounty
-txtPostcode
-txtMil
-txtTel
-txtMobile
-txtEmail
-ActivInd

tblRank
-pkRankID
-txtRank

tblCourses
-pkCourseID (1 to many link to fkCourseID)
-fkCourseType
-numMax (maximum places available on the course)
-curPrice
-logIsAvailable

tblCourseType
-pkCourseTypeID (1 to many link to fkCourseType)
-txtCourseType

tblSessions
-pkSessionID (1 to many link to fksessionID)
-dteStartDate (session start date)
-dteEndDate (session end date)
-fkCourseID

tblSessionMembers
-pkSessionMembersID (1 to many link to fkMembersID)
-fkSessionID
-fkMembersID
-logPaid
-logInstructionsSent
-numPlacesRequired

Where do i go from here?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/201006/1

From: Jörn Bosse on 6 Jun 2010 07:33
Well,

now you should start creating forms. You may start with the forms for
members. Creating a new one/editing onformation/deleting members.
The you do the same thing with the courses and sessions etc.
When you´re about to design a form and you have a field in it with a
foreigt key, use a combobox to select this value from original table.
For exaample fkRank in tblMembers needs a combobox in a form with the
tblRanks as rowsource. In this combobox you can set the width to 0 for
the first column and then you won´t see any useless ID´s in that field
in form. Well now it´s up to you ;)

Regards
Jörn


Am 06.06.2010 12:23, schrieb jase118 via AccessMonster.com:
> Hi, I have used this and many other forums to develop what i believe is a
> good table layout and have established my relevant relationships between the
> tables.
> My knowledge of Access is limited (i have built DB's but a long time ago).
> I am now trying to build the second phase of my database, the queries.
> My DB is a water sports booking system and we are run on a low budget so i
> can not afford to get this done professionally.
> The functionality i need is to:
> Add/remove Courses
> Add/remove Sessions to the courses (a course may run 5 sessions a year)
> Add/remove members to each course
>
> I require a lot more functions down the line but this is the basics of my DB.
> I am not sure were to start with bringing all the tables together to get the
> desired results.
> This is my table layout:
> tblMembers
> -pkMembersID
> -txtFirstName
> -txtLastName
> -fkRank
> -txtNumber
> -txtAddress1
> -txtAddress2
> -txtCity
> -txtCounty
> -txtPostcode
> -txtMil
> -txtTel
> -txtMobile
> -txtEmail
> -ActivInd
>
> tblRank
> -pkRankID
> -txtRank
>
> tblCourses
> -pkCourseID (1 to many link to fkCourseID)
> -fkCourseType
> -numMax (maximum places available on the course)
> -curPrice
> -logIsAvailable
>
> tblCourseType
> -pkCourseTypeID (1 to many link to fkCourseType)
> -txtCourseType
>
> tblSessions
> -pkSessionID (1 to many link to fksessionID)
> -dteStartDate (session start date)
> -dteEndDate (session end date)
> -fkCourseID
>
> tblSessionMembers
> -pkSessionMembersID (1 to many link to fkMembersID)
> -fkSessionID
> -fkMembersID
> -logPaid
> -logInstructionsSent
> -numPlacesRequired
>
> Where do i go from here?
>

 | 
Pages: 1
Prev: Linking two tables - a bit different
Next: Simple hack to get $500 to your home.