Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 31
  1. #16
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664

    I am still working on processing and applying what I'm told here on our database.
    Since you said "My wife and I will want the database to be as comprehensive as possible", here is more to process...


    Have fun... hope your head doesn't explode!!
    Attached Files Attached Files

  2. #17
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You are probably overwhelmed at this point! Go back to basics:

    1 - on a piece of paper draw some buckets
    2 - label the buckets with what you are going to store in them and draw the links:
    a) "master" tables - tables where the data doesn't change very often:
    (i) Students
    (ii) Teachers
    (iii) Courses
    (iv) Current course schedule (can contain course ID, course number, course date and time, max number of students allowed)
    (b) "transaction" tables - tables where data gets put in there over and over, each time "new/history" data, and link them to the other buckets:
    (i) which students are signed up for which courses, attendance and results/grades - link to Students and Current Course Schedule
    (ii) which teachers are taking which course - link to Teachers and Current Course Schedule
    (iii) payments - each time a student makes a payment - links to Students and Current Course Schedule
    3) Study this picture and see if all your questions are answered
    4) Start going into detail of what exactly is going to be stored on each table (some tables may only be "junction" tables containing pointers to other tables, such as Teacher/Current Course)
    5) Now you are ready to create the tables in Access

    At this point you will need to know about naming conventions. You are a "techy" at this point, not a user, so grammatically correct is not required. Some important points: no spaces (use underscore instead), no funny characters (only letters and numbers), no Access reserved words (such as Date), tables to be either prefixed or suffixed by "tbl" or "t" or some such designation. Name the fields with words that make them immediately identifiable and always use the same name across tables (e.g. StudentID will be called that on every table).

  3. #18
    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
    I know you are being swamped with info, but we're all trying to help you with your project and your introduction to database.

    Here is an older post I found while searching for "related data". There are links in this post that may help with concepts and approach.

    Also, I found a jpg of a data model created to help a poster and have attached it. (Could not find the original post/thread???)
    Hope it helps.

    Click image for larger version. 

Name:	StudentsTermsSubjects.jpg 
Views:	35 
Size:	102.4 KB 
ID:	27410

  4. #19
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Good Tuesday evening, please don't think I'm being swamped with information. I feel so blessed getting this kind of help FREE!! My concern is, not getting too much info, but how to apply what I've learned here. I am so thankful that even at 80 y/o I still have a mine sharp enough to learn and being excited learning what what you all teach here. I am very excited and motivated, so don't stop helping me. My concern now is, I've really got to look at every aspect of how to put everything together. As you all have suggested, I don't want to have to try and go back to make changes. I've got to get it right the first time. Moderator, your StudentsTermsSubjects:ER_Diagram_116 is also a big help. I just have to figure out all the terminology. I will check out the links you sent as well.

  5. #20
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25

    Thumbs up

    Hello all,
    I am picking up great info each time you all send additional experiences to chew on. I am just aware that I've got to think about the fact that some of these students for our Real Estate classes will return later, especially those who must take a certain number of classes to meet the Georgia State Real Estate Board's requirements. Each student has to have a certain number of CE"s in order to keep their real estate license. That means that some will be returning for Code Of Ethics, Post License, and other classes. Plus, later we may be adding classes. Will adding classes later be a big problem? I am learning more about the "One to many and one to one, etc". It's a little confusing going in now. This could be a very simple database if I did not have to search for various info down the line. Thank you all so much. It may take me a little longer but I will eventually get it.

  6. #21
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Hello all, Sorry I have not gotten back to you til now. Had death in family. You all have been just wonderful regarding your welcomed advice and guidance. After your guidance, I slightly jumped in and constructed a "Student Table" that includes the following in the order presented:

    StudentID
    FirstName
    MiddleName
    LastName
    Address
    City
    State
    ZipCode
    HomePhone
    MobilePhone
    Email
    WebSite
    StartDate
    StudentGrade
    Note

    I am also thinking of the additional Tables:

    StudentClasses
    InstructorStaff
    ExamResults
    StudentGrades
    ClassDates

    Now, I wonder if I've already included "Student Grades" in the "Student Table" is setting up a seperate Table for Student Grades needed? Also, right now there is only one real estate teacher, but there may be one or two more down the road. I also want to include a Calendar and be able to create invoices for each student that will also include the grade or whether they passed the test for a particular class. By the way, here are the present slate of Real Estate Classes we plan to present right now and there will be a table for these:

    Pre License pt. 1 (37.5 hrs)
    Pre License pt. 2 (37.5 hrs)
    Fair Housing (6 hrs)
    Georgia License Law (3 hrs)
    Property Value (3 hrs)
    Ready For Real Estate As A Career? (3 hrs)
    Post License (25 hrs)
    Let's Go Figure (3 hrs)
    Code Of Ethics (3hrs)

    What have I left out before I go any further. Again, I've only completed the "Students" table. I would be pleased with additional guidance. Thank you so much.

    Bob

  7. #22
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You should take orange's advice and design the table structures/relationships using pencil & paper, whiteboard, large piece of cardboard, chalk and the driveway, etc BEFORE jumping into Access. You will save yourself time and stress.


    In your "Students" table: "State" is a reserved word in Access and shouldn't be used for object names. I use "ST" or "StateName".

    Also in the "Students" table, you have a field named "StudentGrade". Is "StudentGrade" an attribute of a student? Eye color, yes. Hair color, yes. Height, yes. Grade..... No.
    And grade for which course? What happens if the student takes many courses? Which course is the grade for?
    "StudentGrade" IS and attribute of the course attended, as is Course date. So "StudentGrade" should not be in the "Students" table, but in a different table....probably in the "StudentCourse" junction table. (because the relationship between students and courses is a many-to-many relationship.)

  8. #23
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Good Wed Morning Sir,
    Again, I stress my total lack of knowledge of Access and the need to learn enough to construct a working Real Estate School for students who interested in becoming a realtor, or anyone realtor who has to take continuing educational units to meet Georgia Real Estate requirements. I surely did pay attention to advice offered by Orange and all others here. However, it was still very difficult to design the needed database even though I used pad and pencil to draw the model. I've only gotten to where I am at this point because of everyone of you who has taken time to give me guidance. I appreciate your info re: the Student Grade Table. I will make the change you suggest. You're right, I will have to figure out how to develop a separate Table for Student Grades. I don't know how to do that yet. Again, drawing out an example of what the database should look like then developing a working database has been difficult for me. I will keep trying though with everyone's support here. Again, I want to thank all here for your wonderful help.

    Bob

  9. #24
    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
    Bob,

    Did you work through a tutorial from RogersAccessLibrary?
    Further to Steve's comments and your current activity:

    A Student could enroll in Many Courses
    A Student could take a Test in a given Course and obtain a Grade in that Course (for that Test)

    The information about Student Grade would be recorded in a separate Entity/Table that would involve

    -the Student
    -the Course
    -the Test (could involve semester/date of Test..)
    -the Grade/Mark

    similar to my StudentClassTest in the model in post 18.

    Good luck

  10. #25
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25
    Good Wed afternoon Orange,
    What was very eye opening for me was, "Relational Database Principles, "Donal Ravey 2008". I've got to re-read that again. However, as I read, the concept he is presenting begin to become more clear. Such terms as, normalization, atomic and "class of things", become a little clearer for me and not as abstract as before. I totally missed this, but just found it among the info you sent, and I have got to thank you again sir. As you and everyone here as cautioned, I want to make certain as I can that I construct the database as comprehensive as possible. I am going to do some more "working through".

    bob

  11. #26
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Not trying to overwhelm you, but here is more info that I frequently review/re-read.

    Feel free to ignore this.......... (or read at your leisure)
    Attached Files Attached Files

  12. #27
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25

    Thumbs up

    Good Thursday Morning All,
    I'm eating this info you are providing up and in no way am I being overwhelmed. I can't thank you all enough for taking of your important time to help me and many others. Someone mentioned a while back that I may not know our business as well as I should. That very well may the case. I say and admit that because building a database for our brand new school makes for a task I was not prepared for. Many real estate schools are not like a regular learning institute. There are only about 10 - 14 mandatory courses realtors must complete in order to maintain their real estate or brokers license. While I can readily sense the more obvious Tables required to set up my database there are other areas that I can't sense. Right now and because of each of you, I understand I require the following "Tables"...

    1. StudentID
    2. CourseID
    3. StaffID (right now my wife is the only instructor)
    4. StudentGrades (75 is passing)

    Now, I wonder if I need a separate Table for Enrollments, Monies Paid by students, Attendance, Test Results, etc. Plus, I think I will require a class calendar and a means of creating invoices, reports, and forms. As was suggested right here, my wife and I will want to be able to look back from when we started the school to see what our progress has been. Based on information you all have provided me, I at least know how to get started and that's great! When I came here, I had no clue and that's truth. I really can't thank you all enough. I feel very blessed to have found you all here. While I am moving very slowly, because of everyone here I feel blessed I am moving forward at all. Again, I can't thank you enough for all the help you've already given me.

    bob

  13. #28
    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
    Bob,

    You have to determine what is in scope (what will your database be about; what business processes will it support) for your database.

    If students can be enrolled in courses, then that will have to be represented in your database design. Similarly, if you plan on collecting and managing money/fees--payments, partial payments, method of payment; student balance etc - then that also must be represented in your database.

    Good questions. Make a list of all possible; then determine what MUST be included; what should be included; what could be included. Then design based on your list. If it's in your overall plan, you can build based on priorities --even make some changes --but you'll know where all the pieces fit in your plan/model.
    Good luck.

  14. #29
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25

    Thumbs up

    Hello again Orange and all of my helpers here,

    Of course you're correct regarding your advice here. To be perfectly honest sir, I am afraid I will either leave something important out or, add something that will not benefit the database. My primary fear is, setting up a table for example that may not be required. So far here are the tables I've come up with......


    Student: (All students listed with address, phones, email/web addresses, etc)

    Class/Courses: (list all 9 classes we have scheduled so far) can I add others later?

    StudentGrades: (This will be an empty table until students complete a course). Also, right now we are scheduling classes as we get student. Once we start getting students on a regular basis, we can schedule classes. Would it be wise to schedule classes now even when no students have shown an interest now?

    Staff: My wife is the only instructor presently. Should this be a Table as well? Should I still do a Table even with one (1) instructor?

    ExamResults: I'm not sure this should be a separate Table? Maybe I should do a "TestScores" Table?

    StudentHistory: I thought about this Table but am not sure what would go there.

    Invoice: Should this even be a Table?

    Enrollments: This Table would include StudentID and ClassID

    Right now that's all I can think of that might be included in the Tables. However, I don't want to make this database too complicated. Is it possible to start out and then add Tables later?

    Once my wife (who is the realtor/broker) was certified to start the school, I went on the internet to see if we could find businesses that could provide all the support and data we would need, plus, accept payments from students. They would install a link on our website permitting potential students to go there, pick a course and pay for it. Everyone of these businesses I looked at and contacted wanted to sign a 10 to 15 page contract with lots of very small print. However, these contracts wanted a commitment of at least 3 years and of course they would require us to pay a certain fee for each student which was more than we could afford just starting out. Plus, I never want to sign a contract especially a 3 year contract. So, having lots of recent experience with Microsoft's Publisher and a very limited amount of experience with Excel many years ago, I decided to try constructing a database using Access 2007 which I understand is not that much different from Access 2010 and that's great! Well, that's the long and short story everyone. I was forced into finding out about and using Access. Mind you, I'm not upset since I believe this is one of the best things that could have happened. I am going to keep at this until I learn as many of the pro's and con's as possible. Access is a very powerful and useful tool.

    bob

  15. #30
    musicmanbob is offline Novice
    Windows 10 Access 2007
    Join Date
    Feb 2017
    Location
    Atlanta Georgia
    Posts
    25

    Thumbs up

    Good Friday Afternoon All,
    We're going to have a great weekend here. The temp will be around 70 degrees over the next 7 or so days and I love it. I just wanted you all to know I am really paying close attention to "Entity Relationship Diagramming". I've just been reading and trying to let the various ways to look at this information sink in. Thank you for leading me in this direction. I am sure I will have to look hard at some of the other information you all have presented, but for now, I am trying to take it a step at a time and not trying to cram too much at one time. Thanks again all.

Page 2 of 3 FirstFirst 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