Results 1 to 9 of 9
  1. #1
    Flyboy is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3

    Smile Need help with Microsoft Access - Beginner Level

    Hello! I'm new here and also new to Microsoft Access. I just picked it up last week and has been watching alot of Youtube videos on it too. However there are some things that I still don't quite get and need further explanation. Would appreciate any help that I can get from anyone =)))



    Reason I picked up Access is for my small business. I run a music school (under 100 students at any point - looking to grow more!) and currently we are using Google Excel Sheet. However our system has grown a little and I just hired an admin staff to help maintain our database. Another reason I picked up Access also we're looking to grow more and I really like the forms and the queries that Access provide.

    The most recent achievement is learning the Relationships between tables and I was wondering if anyone could foolproof read this diagram that I came up with and can point out anything I can improve on it, I would really appreciate it!

    Really looking forward to learn Access more!

    Thanks!
    Ben
    Attached Thumbnails Attached Thumbnails Untitled.png  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    without having a better understanding of what the db is required to do and the datatypes, your relationships look OK.

    The only things I would do differently

    1. provide an autonumber PK for the payment and class details tables and simply create a multifield, no duplicates index for the two FK fields.
    2. I would also remove the PaymentMethod field from the payment table since you already have that value in the payment method table
    3. change the 'Em' field names to indicate which table e.g. StuContact, TeaContact or similar

    Other thoughts (with no understanding of what the db is required to do)

    If a student pays for multiple courses, you might want another table to record the payment - and link that to payments
    is it the same teacher per courseID? if so, teacher should be linked to courses, not the class details
    EmRelationship looks odd - what form of relationship? shouldn't there be a relationships table?
    Remarks - might want these in separate tables if there could be multiple remarks
    How is the quantity field relevant to payments?
    No date fields - when is a payment made? when did a student join? leave? ditto teachers

  3. #3
    Flyboy is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3
    Thanks for getting back to me! =)

    This db is supposed to help me keep track of our students monthly payments and what course they sign up for on a monthly basis. The date actually doesn't matter to me since it's a monthly recurring payment so i'll keep the "payment date" to just month/year where i don't mind typing it myself but of course if the system has the format for month/year, i'd be happy to do it. i just don't know how to lol

    1. So after abit of research in Microsoft Access, I understand that between Registration table and Courses table is many-to-many relationship, I followed the same format as this youtube video I found, https://www.youtube.com/watch?v=Ou1z...ndex=31&t=527s

    It seems that I need a "Junction Table" to connect a many-to-many relationship. Hence why i came up with a "Payment Table" to connect Registration Table and Courses Table. He explained pretty well in the video about why this Junction Table needs 2 PK. It sorta make sense to me but please enlighten me if there's another way to look at it. I'm ready to learn!

    2. In the youtube link above, he taught me how to create a form (Student details etc frm Student Database) and sub-form (from a query (Registration, Payment and Courses)) to view the history of courses and payments a student has made. I found out that whatever new updates I entered, it doesn't update in the Student Database =(( Also pertaining to the Payment Method, I would like to make it a combo box of choices like Bank Transfer, Cash, QR Code etc in this subform and have it updated in the Payment table but it's not updating and I don't know what's wrong. I've removed the Payment Method and deleted the relationship as you suggested

    3. Em is the short form for Emergency Contact xD We have a lot of kids so that's kinda required for us to contact next of kin for emergency purposes.

    4. If you look at the youtube link, I made it exactly like mine lol I just changed the terminology to suit my music school criteria. I can see the "Order" (Courses) in the sub form he taught me to create and it lists all the multiple courses with the Amount Due. I thought that would suffice but like I mentioned in No. 2, when I update in the sub-form, it doesn't update in the main Student Database table and I'm not sure why.

    Note: I notice in the diagram above, I didn't pull down the Payment Table (sorry!) There's Amount Due at the bottom as well as Remarks. I made a formula to calculate, Amount Due: Quantity*CoursePrice-Discount in the query to be displayed in the subform.

    5. We have multiple teachers teaching multiple courses so that's another many-to-many relationship, I tried the same Junction Table again like what I did with Registration and Courses table. This time I created Class Details as the Junction Table but the last time I tried anything, I screwed up big time and I had to delete everything to start all over again =( I'm pretty sure I made some boo boo in this relationship diagram hence why i posted it here for help =)) Hope you have some input for me!

    6. As for the remarks, I need it for Student Database to note if they have any allergies or people who are not allowed to visit etc, for Registration Table, it would indicate if the student is taking bi-monthly classes or alternate week classes or is on break and finally for Payment table, I need to to indicate if the student has over paid or underpaid, amount to bring forward or owing etc. I've already removed the remarks column from Courses table. I might remove it from Class Details as well as i'm not sure if I need it. And finally for the teachers it would just be anything we need to take note of e.g if teacher is visiting or need salary at a certain time etc etc

    7. Quantity is for number of classes students sign up for e.g vocals classes in a month, some may take 2 or 3 or any private dance classes, some may take odd numbers.

    8. Enrolled and Exit is the date student join. The date doesn't really matter to me as we operate mostly on a monthly basis so basically month/year would be enough for me. At the moment, the query I have to filter Active/Current students is to check on "Null" on the Exit column. Something I picked up on learning from youtube videos =))

    Thank you so much for taking the time to help me out with this! And I apologize if there are not enough information, I'll try my best to remember what I did and why I did lol
    I owe you a big one!

    Cheers, Ben

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 3 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    1. having a single field to uniquely identify a record (which is what a PK is all about) is much easier to handle. There was a thread recently on this or another forum where the OP hit problems with using a multifield PK. But I suppose it is a matter of preference.

    2. providing your form is bound to the table, then changes should update automatically when you either update the record using code or when you leave the record. If it is not doing that, you are missing something. I didn't say remove the paymentmethod relationship, just the paymentmethod method field.

    3. OK - but would still differentiate them from students/teachers. My methodology is to make each fieldname unique to avoid any confusion. But again, a matter of personal preference.

    4 - see 2

    5 - correct it is a many to many relationship, was just questioning 'teachers with what?'. Still not clear from your description quite what this is. All depends on how you define courses and classes. e.g. you have basic, intermediate and advanced piano courses and you have 2 teachers who teach piano. A student books an intermediate piano course - will they have the same teacher for each class? or different teachers? If they have the same teacher the many to many relationship is with the course, if the teacher can vary, it will probably be with classes. But you might still have it with course, with a sub relationship on class if a teacher is on leave/ill/absent for any reason.

    6. I would have allergies in a separate table with a many to many junction table. Rest of your comments imply there could be other side tables with multiple records - such as breaks

    7. OK - so how do you know which classes the quantity relates to? What is to stop a student paying for 3 classes but attending 4? Or only 2, so you still owe them a class?

    8. 'null' will only work if you don't complete the exit date until on or after that date. That may work for you, but 'null' basically means 'unknown' which implies you don't know when a student is due to exit. Comments like 'we operate mostly' says to me that just storing year/month is insufficient.







  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also note:
    "Exit" (Student Database), "Day & "Time" (Class Details) are reserved words in Access and shouldn't be used for object names.
    Should not use spaces in object names. Object names include Field, Table, Query, Form, Report and Module names.

    I use a suffix of "_PK" for the primary key field name and "_FK" for the foreign key field name. Example would be "StudentID_PK" and "StudentID_FK"

    It is also helpful to use a prefix to name objects.
    Example:
    "tblStudents" (your "Student Database" is actually a table),
    "frmStudents",
    "qryStudentsActive" and
    "rptStudents".

    See
    Problem names and reserved words in Access
    Microsoft Access Tables: Primary Key Tips and Techniques
    (<<-- read at least twice )

  8. #8
    Flyboy is offline Novice
    Windows 10 Office 365
    Join Date
    Jan 2021
    Posts
    3

    Talking Thank you!

    Quote Originally Posted by ssanfu View Post
    Also note:
    "Exit" (Student Database), "Day & "Time" (Class Details) are reserved words in Access and shouldn't be used for object names.
    Should not use spaces in object names. Object names include Field, Table, Query, Form, Report and Module names.

    I use a suffix of "_PK" for the primary key field name and "_FK" for the foreign key field name. Example would be "StudentID_PK" and "StudentID_FK"

    It is also helpful to use a prefix to name objects.
    Example:
    "tblStudents" (your "Student Database" is actually a table),
    "frmStudents",
    "qryStudentsActive" and
    "rptStudents".

    See
    Problem names and reserved words in Access
    Microsoft Access Tables: Primary Key Tips and Techniques
    (<<-- read at least twice )

    Thank you so much for your help!

    I've actually updated my Relationship Diagram a little bit to make it a little easier. I guess I will read on all the links that you have provided before I go any further.

    I will be back with a new and improve diagram! Thanks again Ajax!

    Cheers,
    Ben

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 8 was moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Microsoft Access beginner question
    By bigboy in forum Database Design
    Replies: 4
    Last Post: 05-01-2018, 06:34 AM
  2. Replies: 2
    Last Post: 01-15-2018, 07:46 PM
  3. Replies: 13
    Last Post: 10-06-2015, 09:12 AM
  4. User-level Security Beginner
    By Monty51 in forum Access
    Replies: 3
    Last Post: 03-17-2015, 05:01 PM
  5. Replies: 1
    Last Post: 09-13-2011, 01:52 PM

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