Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11

    Advice on Improving my Database.

    Hello,



    I work for the local authority music service. We currently use several databases for the different services we offer. The problem is that many of the services we prove are used by the same children for example one child will attend one of our music ensembles, hire an instrument from us and attend any trip/residentials we organise, meaning that their details are repeated across several different databases.

    I'm trying to look for a way to link them all together so that any child that use any of our services will have their own 'account' and i will be able to open up their account and see all of the services they access all in one place.

    I've being using microsoft access for nearly ten years but I only know the basics. I can create tables, define their data type, and create basic queries but thats it. My workplace has free online courses on access that I have completed so I have learned a bit more about forms, macros, relationships etc but the more I learn the more I'm realising that the database I want to create is quite complicated.

    I've tried approaching our IT dept but we now use a shared IT company with other councils and not only do they want to charge me an obscene amount of money to help they are also saying that Access is unsupported. That has lead me to the decision to try and learn my self.

    I understand that this is probably going to be a big job, but is there anyone out there willing to help me?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Welcome to the forum. I am sure we can help. Aside from needing a working application and database, are you able to break off a small chunk and address that issue? I could take a wild guess and say, it would be helpful to normalize your data. However, you are not giving us much to go on.

  3. #3
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    I have three main databases that need improving. The first is our 'Hire' system. It basically acts as a lending library. We just have one table that has all of the instrument details. we tab across the record inputting a customers details and delete the customers details when they've returned it. I also use it to create simple queries for sending the bills of any instruments that need to be re-hired or returned. it's very very basic.

  4. #4
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    The second is 'Music Centre' This holds all of the informatin of children that attend any of our bands, choirs or orchestras. It's one table that holds all of their contact details, medical info, payment details and which bands they attend. I copy and pasta the table every time a new term starts, deleting only the payment info ready for the new term. I create simply queries fromantic that table to create registers for the tutors that run the groups.

  5. #5
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    the third database is for 'Assisted Tuition Scheme' or 'ATS'. ATS is a bursary scheme that helps pupils with the costs of lessons and instrument hire. That database again is one table that holds all of the pupils details that benefit from ATS, whether or not they've applied in the past, if they've been successful and what they get help with.

  6. #6
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    As you can imagine there is A LOT of duplicate data. When ever a child changes their address, very often it isn't updated in all of the right places. Also because ATS isn't linked to Hire or Music Centre very often I send bills out to pupils who don't have to pay. It's just causing so much work. I'm out of the office until Wednesday but when I'm back in I could attach a doc on here that will show exactly the current system I have. I've also got a plan of what I would like (we'll, need) the new system to be like. It would make my job so much easier if we made use of forms and macros and reports.

  7. #7
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    thank you so much for taking the time to reply and help. I really appreciate it!

  8. #8
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    By normalise do you mean breaking the data up? like on our Hire database we have the child's first name and surname in the same field

  9. #9
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I would definitely say you can benefit from rethinking the data structure and apply basic principals of Normalization.

    What are you trying to do? Do you want to make the database more dependable/accurate? Are you in need of making things more efficient by employing fewer users to maintain the data? What do you believe the pain points of the business are?

  10. #10
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    I would like to make it more dependable/accurate and efficient. There's only 4 of us that use the system with 2 of us that maintain it so I don't think that's a problem. The main points of our business is to hire out musical instruments to pupils who attend schools in our local authority and to monitor the students that attend our bands/orchestras and keep track of their membership payments

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    It really boils down to creating a good database, first. It is not an easy process. The first steps towards creating a database is to create an ERD. With your entities defined and their attributes defined, you can begin to diagram how they relate to each other. As you create your Entity Relationship Diagram, you want to follow Rules of Normalization. Every business is different, so you will want to consider all of the Business Rules defined by operations and various business processes you wish to automate.

    Perhaps this video can offer a high level view of what it is I am hinting at.

    https://www.youtube.com/watch?v=-fQ-bRllhXc

    This video touches on Keys. Keys are a big part of, well, everything.
    https://www.youtube.com/watch?v=_aN-8kszIdA

  12. #12
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    Thank you. I had know idea about ERD's. I'm going to plan one out now and hopefully that will help me to get started. Thank you so much for you help!

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    When it comes to Keys, you will likely want to start by understanding the Primary Key and the Foreign Key. A candidate key is relevant during the planning stages.

    Check out this link here and maybe start wit the following topics therein.


    • What Is Normalization?
    • The Normal Forms
    • Entity-Relationship Diagramming


    http://www.rogersaccesslibrary.com/f..._topic238.html

  14. #14
    n.h is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11

    Thanks :)

    Thanks for those. Would you mind looking at 2 documents I created, a basic plan for my database?
    Attached Files Attached Files

  15. #15
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You are going to need a table to bridge the Pupils Entity with the Guardians Entity. Perhaps, without discarding all of your notes, you can start over and concentrate on that. These are the more difficult types of relations to deal with. Looking at your business rules, you are going to have several Many to Many relationships. Since this is the case, you might as well store phone numbers and addresses in their own tables, two.

    tblPupils
    PupilKey (AutoNumber)
    PupilGenderKey (Long)
    FirstName (ShortText)
    LastName (ShortText)
    PupilDOB (Date/Time)

    tblGuardians
    GuardianKey (AutoNumber)
    GuardianGenderKey (Long)
    AddressKey (Long)
    FirstName (ShortText)
    LastName (ShortText)
    GuardianDOB (Date/Time)

    tblAddresses
    AddressKey (Autonumber)
    Street1 (ShortText)
    Street1 (ShortText)
    Street1 (ShortText)
    City (ShortText)
    ...

    Then you will need some Junction Tables
    tblGuardianPupil
    GuardianPupil (AutoNumber)
    GuardianKey (Long)
    PupilKey (Long)

    more tables
    etc ....

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

Similar Threads

  1. Improving the Look of Titles
    By Lou_Reed in forum Access
    Replies: 3
    Last Post: 08-19-2015, 05:51 PM
  2. Improving Speed on a Top N query
    By orange in forum Tutorials
    Replies: 2
    Last Post: 01-05-2015, 01:42 PM
  3. Improving a querry for faster result
    By Aloupha in forum Queries
    Replies: 7
    Last Post: 10-31-2013, 01:07 PM
  4. Advice on Database Design
    By JoeM in forum Database Design
    Replies: 1
    Last Post: 08-15-2013, 08:50 AM
  5. Database advice
    By PRINCE SWAGG in forum Access
    Replies: 29
    Last Post: 06-21-2011, 03:56 PM

Tags for this Thread

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