Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25

    Need Advice re: Building Database

    Good Sunday evening all, I am a brand new member as well as a new user of Access. My dear wife and I are starting a Real Estate School that will accomodate between 5 and 40 students. As I've started read a book re: Access, I discovered that I was way off regarding how to construct a database that will work for us. While I am very excited about learning how to build a strong database, it takes more than a strong desire to learn Access. So, when I was blessed to discover this forum, I am certain someone here can help guide me. I am using Access 2007. I will list all the elements I plan to use below:

    LastName
    MiddleName
    FirstName
    address
    city
    state
    zipcode
    HomePhone
    mobilephone
    emailaddress
    company where realtor works

    coursedate
    coursetime
    coursenumber
    courseCEhours

    studentscore
    studentcomplete (did student complete the course?)
    studentscore
    studentpayment


    howpaid
    notes
    customerID

    My questions are, how should I divide these catagories up so all will work correctly? Should I have separate tables and what should go in each table? How can I and my wife access this information from our different computers? I do hope I've asked all of the questions I needed to. Thank you for your responses.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Dear Moderator, I am more than thankful you took of your precious time to research and send me this information. However, since I am a rank amature I really don't know how to apply this information to a database that relates to the information I provided here. I am sure what you sent would be of immense importants to someone with more skill than I have. I am going to take some time to review what you sent until I am able to use it to do what I need to do. Again, I am more than appreciative to you. Thank you so much.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Good stuff.
    If you watch the Soper videos, then spend 45-60 minutes working through one or two of these tutorials, you will learn--but you must work through the process.

    Entity Relationship Diagramming
    Class Information
    Widgets

    Good luck.

  5. #5
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Dear Moderator, I want to learn the process and will spend time as you suggest working through the info you sent. Again, thank you so much. I very much appreciate.

  6. #6
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    After going back to the information the Moderator sent, here is what I've come up with:
    Table 1 that would include the following:
    LastName (text)
    FirstName (text)
    Address (text)
    City (text)
    State (text)
    ZipCode (text)
    EmailAddress (hyperlink)
    Company (text)

    Table 2 will include:
    HomePhone (text)
    MobilePhone (text)

    Table 3:
    CourseDate (date/time)
    CourseTime (date/time)
    CourseName (name)
    CourseNumber (text)
    CourseHours (text)

    Table 4:
    StudentScore (text)
    StudentCompleteCourse (yes/no)
    StudentPayment (currency)
    HowPaid (text)
    Note (memo)
    CustomerID (auto number)

    Does this make sense to anyone here? I am thinking this would give me the information we need to process each student, making sure they passed classes, paid their bills, spent appropriate time in classes, we are able to track grades and print out results. Thank you all for taking a look.

  7. #7
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    You are also going to want to have fields that you'll be able to use to link your students and courses together. I recommend the following:

    tblStudents:
    StudentID (auto if you don't have one)
    LastName (text)

    FirstName (text)
    Address (text)
    City (text)
    State (text)
    ZipCode (text)
    EmailAddress (hyperlink)
    Company (text)
    HomePhone (text)
    MobilePhone (text)

    tblCourses:
    CourseDate (date/time)
    CourseTime (date/time)
    CourseName (name)
    CourseNumber (text)
    CourseHours (text)

    tblJunction:
    jncCourseNumber
    jncStudentID
    StudentScore (text)

    StudentCompleteCourse (yes/no)
    StudentPayment (currency)
    HowPaid (text)
    Note (memo)
    CustomerID (auto number)

    The two jnc fields would be linked to your other two tables. This is what we call a many-to-many relationship. There are many courses, and there are many students; that said, there are many combinations of students and courses.



  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Primary keys not displayed, they are needed to link the tables together in the Relationships window.

    There is no table which shows which course a student has signed up for.

    Not sure what "CustomerID" is - there don't seem to be any "Customers".

    You may want to separate the payments out into a new table, some people pay a bit at a time. Include date paid.

    Don't know why phone numbers are on a separate table - are there going to be multiple home and/or mobile phones per student?

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Following on from AccessPower's post:
    - tblCourses - separate this out into Course information, name and number; and a separate table to hours date, time, hours. (1) you want to keep each year's information for each course so that you can look back and see what happened last year and by having it all in one table you will lose that history (2) you may have too many students for a course and offer it twice on two separate date/times
    - tblJunction - as per mentioned previously, separate out payment information in case of deposit or such; also I always keep field names the same when they are exactly the same, such as StudentID, that helps me know what is going on across tables

  10. #10
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Good Monday Afternoon Aytee111,
    The Moderator provided links to help me look further into developing a good database. Now, with your information, I will spend time looking over what you have to say here. I am sure it will open my eyes and understanding even more. Actually, I had no idea putting together a database required this kind of time. Because of you all, I learned how important "tables" are, and I am beginning to understand database design more. I still have more reading and thinking to do. I sure am blessed I can here for help. You all are helping me better understand my efforts.

  11. #11
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes getting your tables designed to support your business is critical, and in my view is a first step.

    It is a best practice to describe your business in simple terms -minimal jargon. If you have to resort to "jargonese", you probably do not understand your business as well as you need to.

    I don't know your business in detail, but let me give a hypothetical/analogy.

    You have some Courses (Algebra 101, Chemistry 102, Chemistry 208....) and there are Teachers for such Courses (Bob, Sue, Wendy..) and you have Students (Al, Sam, Jane...). Courses aren't simply taught usually--that is there are Classes or Course Sessions where a group of Students and a Teacher of Course meet (in a room or online session) and Teacher teaches the Course (Chemistry 102). You also have an issue to collect Fees from Students for training. You need to know which Student is enrolled to which Course and what the schedule for that Course(Class sessions) will be. It would also seem necessary for attendance and marking(exams/assignments) whether or not the Student actually attended the scheduled course sessions. In addition you may have to set up your Teacher/Course schedules in the database (if not now, then maybe in future). You may even need to know who could replace Sue if she got sick.... The bottom line of this is What exactly is in scope for this database. Scope creep is where new items are constantly added to a project--you want to avoid it. So get the bounds of the project defined and agreed to. Get a plan established to do the immediate and important and make sure the future stuff is identified -if only at a black box level.

    I'm not trying to be negative or overly picky. I'm just saying get all the facts that are involved in the business and start to build a data model ( potential tables and relationships). Get some sample test data and test the model. ( see my stump the model)

    DON'T jump into Access or any physical database too quickly. Get your facts, test data and data model--and test in order to get a blueprint for your database that you know will meet your requirements.

    I referenced several data models in earlier post so you could see there were more than 1 view of "Students and Courses" etc.

    Good luck.

  12. #12
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Agreed. The table design is the foundation of the house you are building - if it isn't right you will have problems forever!

  13. #13
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    I can second what Orange is saying. Plan BEFORE you create!

    Scope creep is real, and having to redesign a database based on additional or unforeseen needs is to be avoided. Make sure that you have a design that works for you before you implement it. Draw it out with a pen/paper. Line up relationships between tables.

    Something like this might work:

    tblStudents:
    sID
    sLastName
    sFirstName
    sAddress
    sCity
    sState
    sZipCode
    sEmailAddress
    sCompany
    sHomePhone
    sMobilePhone


    tblTeachers:
    tID
    tLastName
    tFirstName
    tAddress
    tCity
    tState
    tZipCode
    tEmailAddress
    tCompany
    tHomePhone
    tMobilePhone




    tblCourses:
    cID
    cDate (date/time)
    cTime (date/time)
    cName (name)
    cHours


    tblJunction:
    jID
    jnc_sID 'Joins your Student table
    jnc_tID 'Joins your Teacher table
    jnc_cNumber 'Joins your Courses table
    jScore
    jCompletedYN (yes/no)
    jAmountPaid (currency)
    jPaymentMethod
    jNote (memo)

  14. #14
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Hello all and I sure do appreciate all the input and help. Here are the classes my wife will be teaching to real estate students, some who are trying to start a real estate career and some who who need credits to keep their license. Here are the classes and others may be added later:
    Real Estate Pre-Licensing (Part 1)
    Real Estate Pre-Licensing (Part 2)
    Fair Housing Is For Everyone
    Georgia License Law
    Valuing/Positioning Property in Todays Market
    Are you really ready for Real Estate
    Post Licensing

    We plan to add other real estate classes as requested or needed. I had actually forgotten that classes needed to be added, so you can see how much your advice and comments mean to me. Again, this is my very first time putting my hands on trying to develop any kind of database. Developing this database is particularly important to my wife and I. You all have opened up a brand new way of looking at this project and how to accomplish it. Thank you and Thank you so much for taking time to help me. I can't thank you enough all. I will look at all of the imput and learn from everything in the way of help i've gotten.

    MusicManBob

  15. #15
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    You all are showing me how to work smarter once I get it. I am still working on processing and applying what I'm told here on our database. Mentioned was a need to seperate out the table for hours date, time, hours. Also mentioned was a need to keep each year for every course so we can look back. That's something I never gave a thought to. Each time you all provide a comment here it gives me additional stuff I never gave any thought to. However, right now I absolutely don't know how to apply this principal. I've got to find a way to fit this concept in so that we can see what's going on from year to year. I've got to figure out how to set this up along with all of the other great ideas you all have given to me. My wife and I will want the database to be as comprehensive as possible and that's what you all are helping me do. I can't say enough "Thank you's". I am still reading and trying to put this all together. If something is left out initially, can it be added later?

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need advice on building DB for company
    By f15e in forum Access
    Replies: 4
    Last Post: 03-07-2016, 05:00 PM
  2. Replies: 12
    Last Post: 09-18-2015, 02:20 PM
  3. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 PM
  4. Building a database from scratch
    By kookiethekat in forum Access
    Replies: 1
    Last Post: 01-20-2011, 06:35 AM
  5. need some advice building DB (noobie)
    By sureshot in forum Access
    Replies: 2
    Last Post: 10-12-2009, 09:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums