Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2010
    Posts
    6

    Question Unanswered: i have a good 'schema, where do i go from here?

    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?

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by jase118 View Post
    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?
    I would start build your forms and report. I normally do not think of the queries I will need. Queries are built as you need then will creating your forms and reports.

    . As you build these objects, you will probably need to create queries.

    There are other answers posted here:

    http://www.utteraccess.com/forum/goo...t1946135.html&
    Last edited by HiTechCoach; 06-07-10 at 00:52.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    With your schema you may not need queries to accomplish some of the things that you want to do. Whenever you have a One-To-Many relationship you can usually handle data entry with a Form and a subform. The form would be linked to your one side and your subform data would come from you many side. Then you would link the two when you add the subform on the Primary Key and Foreign Key.

    For example, create a form that uses your Course table for the data. Add all the fields you want to be able to edit for that course. Then create another form for your Sessions. Add all the fields you want to the frmSessions form. Then open the frmCourses and add a subform to it. When Access asks which what you want to use for the form, choose an existing form and then select the frmSessions. Link the forms together with the CourseID. Then when course 1 appears you should be able to add Sessions to it using the subform.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •