Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14

    Unanswered: Microsoft Access 2010 database for college with a dual curriculum

    Hello,

    I have been using Microsoft Access 2010 to create a database for a college with a dual curriculum and have taught myself what ever I have managed to do so far. I have got to a point where I just need to make a few changes here and there before I fill in all the information I want to keep a record of. I just need someone who is an expert in this kind of stuff to check that what I have done so far is correct and just give me that little bit of advice to fix anything that looks like it could be problematic later on. After that i'll need some guidance on creating forms and queries for this kind of information. I have attached what I have done so far, I'm happy for you or anyone you recommend I ask to play around with it. Please bear in mind that I have very basic knowledge of DB so any advice given would have to be as simple as possible

    I hope to hear from someone asap as i'm pushed for time and have been pulling my hair out over this for weeks.

    Thanks
    Last edited by QurAdmin; 12-05-12 at 13:13. Reason: Posted by accident

  2. #2
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    how do I attach the database? Its not filled with info yet but the tables have been created XD I'll screen shot the relationships...that should show you what kind of info im trying to record and how I want it to relate...
    Attached Thumbnails Attached Thumbnails DB relationship scr.jpg  

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    whenever you see repeating columns (such as months of the year, Term1..Term3 and so on it's sure sign of non normalised design

    There's other symtoms such as the t_phone Entity
    you have a primary (Phone_ID), but it doesn't add anything to the mix unless you are saying a student could have multiple entries in t_phone. Personally either those columns should be in t_students OR (a better bet in my books) in a sub/child table to t_students where you'd have the student_ID, a phone number, and say a phone number type (a FK to say PhoneNUmberTYpes Table)

    same as t_Email, are you expecting multiple rows for one student....
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    to attach a DB you'd need to compact and repair a copy, then compress the file into a zip file and attach it as part of a post
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    Sorry the Screen shot needs to be clearer
    Attached Thumbnails Attached Thumbnails DB relationship scr.jpg  

  6. #6
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    Cool that was quick... ok if I put the database up here...maybe you could play with it cuz i'm a little confused with just written suggestions

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    nope
    from my perspective this DB is a mess
    I don't have the time (or to be honest the inclination) to download your db, modify it and repost

    for help on normalisation / db design these links seem to stand the test of time
    Fundamentals of Relational Database Design -- r937.com
    AND
    The Relational Data Model, Normalisation and effective Database Design

    If you have a specific design problem or thorny issue I'm sure there's lots of people who can and will help out, me included.
    but general issues you can count me out.

    but to get going

    I'd suggest you ditch your current t_fees design.. its redundant
    instead have a composite primary key of Student_ID AND something that identifies the year and month. If it were me then I'd expect the date of the payment to be a significant piece of data. I'd want to know if a student can pay mnore than once in a day. if not I'd use the student_id and transaction date as the PK, and record the value
    you could also stuff the fees owed into the mix using say a negative value ie the amount of fees owed. that way round yoiu hgave a very simple mechanism to know if a student has outstanding fees ( do a sum by/on the student id.. if its negative they owe miney, if its postivie they (probably.. depends on the data) don't owe money

    t_attendance seems a woolly design. in my experience schools/universities want to know attendance either daily or per class/lecture. I've never seen one on a month basis.
    usually the information is then used in some form of report comparing attendance against scheduled classes/lectures/days. to do that ypu'd need to know what classes/lectures/days a student should attend.


    take telephones
    EITHER
    I'd limit the number of phone numbers you need to store for a person and stroe them in t_student
    OR
    I'd allow as many phone numbers as required by having a PhoneType table eg:- home,Work,Mobile,EmergencyContact etc,
    then have a StudentsPhones tabel with the StudentID + PhoneType as the composite primary keym the studentid beng a FK to t_students, the PhoneType being a FK to PhoneTypes. Actually I'd probably have a communicationsType table and store email addresses + phone numbers in StudentCommunicationDetails (which would thenh replace StudentPhones)
    Last edited by healdem; 12-05-12 at 14:09.
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    oh ok, Well thank you for your help

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Incidentally a UK address can have up to 5 lines + post code. I can guarantee 2 lines + post code will break the system
    If your system needs to track more than one address for a student then you need soem form of qualification of what the addresses are. I'd expect a priomary address (the one that is effectivley used for legal purposes and as many others as you feel you need for that person.
    The primary key should be either the Student ID (in which case this is a redundant entity the information could be stored in t_student) or add some form of qualifier (say AddressType which in conjunction with the studentid is the PK, and the addresstype is a FK to antoher table which identifies what type of address it is
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    Thank you again for your response, I will read through the links you've given to me and sort the 'mess' out.

    *sighs* I was given this link and followed the advice from the website...I feel like scrapping this alltogether! But I would definitely have wasted my time if I give up now.....

    Allen Browne's Database and Training

    A couple of students have two addresses and most of them have three contact numbers, I will do what you have suggested and if I get stuck i'll be back to ask for help again

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    FWIW, I've never gone wrong following Allen Browne's advice.
    Also, for a first effort you're not too far off the mark, so don't give up. As an aesthetic point, you use a number of different ways to name fields - underscore_separation, MixedCase and [Names with spaces]. Pick one and stick to it, as it will make your code easier to read and write. Whichever one you pick, don't use spaces (unless you like wrapping everything in square brackets).
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    I think its because I never knew what a database was until I had to take on this task. what's on the website is not bad, its just that I'm a novice and the websites advice for beginners is too basic for what i'm trying to do and the advice for what i'm trying to do is explained using terms I don't understand and so what I've produced is whatever I understood from it. The reason I ended up breaking down address and contact info was because I followed a youtube tutorial that was put up on the website by a lady called Crystal.

  13. #13
    Join Date
    Dec 2012
    Location
    UK, Leicestershire
    Posts
    14
    Crystal's Access Basics I skimmed over the Pdf's and got confused, so I watched the videos instead and based the tables on what I learnt through that..

    I basically want to have a way of having a student profile for each individual student that would show all contact info, class records and grades and a summary of attendance. The fee records would be private and the attendance records would need to be in a format that is easy to send.

Posting Permissions

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