Results 1 to 5 of 5

Database becoming difficult to manage

  1. #1
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24

    Database becoming difficult to manage

    We issue dosimeters to approximately 400 staff. Each staff is issued a dosimeter for 3 months. The dosimeters are then collected and readout. A file is exported from the dosimeter reader. The file has the dosimeter serial number and the readings but no information on who used it. The next quarter dosimeters are issued to staff again. A dosimeter may be used by different people in different quarters, but a dosimeter will only be used by one person in any quarter.



    We have a database that uses a table for each quarters readings. A query then reads the dosimeter file, looks up who used the dosimeter and puts the readings into their record. We have kept each quarter separate so that only one person matches the dosimeter. However after only two years the number of tables and associated relationships, forms, queries, reports etc is getting difficult to manage. Is there a way to organise our existing database to make it easier to manage or should we try to simplify the number of tables?

    Thank you.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    You could have simplified your system by just putting the date in the table. you then would only need one table for the readings. (I think)
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,563
    However after only two years the number of tables and associated relationships, forms, queries, reports etc is getting difficult to manage
    this sounds like you have unnormalised data, once developed, number of objects should not increase. Recommend you look at simplifying the number of tables - perhaps provide a screen shot of your existing tables and relationships and someone here can probably help make some suggestions.

    A few years ago I developed a similar system for radon tracking. Dosemeters issued to landlords, then returned with addresses. Sent off to lab for analysis and reports returned. Dosemeters were one time use, rather than reused, but only required a total of 11 tables and 12 forms and 3 reports - including access management

  4. #4
    badmem is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Mar 2016
    Posts
    24
    Thanks for the replies. When I started this project I knew very little about databases. Consequently, I split each process into a lot of small steps. This was okay at the beginning, but as time went by the number of tables, relationships etc got more complicated. Now that I know more (I think) I will try to simplify things by combining some of the steps.

    The file that is imported has the date the dosimeter was readout. However dosimeters are worn from January to March for instance, but are not readout until April. Some dosimeters come back late. So the date that the dosimeter is readout cannot be used to put the reading in the correct quarter. The dosimeters are reissued randomly to users each quarter. I originally put each quarters readings into a separate table so that the query would only give one match for each dosimeter. Maybe I need to put the quarter that the dosimeter was worn into the query, so that it ignores dosimeters from other quarters. Then I can use just one table for all of the quarters.

    I originally did everything in small steps so that once I got that working I could move onto the next step. It was easy to troubleshoot. So now the database is working pretty well but it's getting pretty unwieldy. I've learnt a lot from this forum, so it's probably time for version 2.0 of this database. Combining the small steps into a less number of bigger steps should improve things. I hope

    Thanks again.

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,896
    I believe you are now on the right track. Good luck.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. Database to Manage Supplies Inventory
    By Chris2810 in forum Access
    Replies: 2
    Last Post: 02-20-2016, 03:40 PM
  2. Law Firm Database - How to manage people and cases
    By Mersad in forum Database Design
    Replies: 28
    Last Post: 08-06-2015, 06:15 PM
  3. How to manage the manage the input of data?
    By Gambit17 in forum Import/Export Data
    Replies: 4
    Last Post: 07-30-2013, 10:32 AM
  4. Replies: 1
    Last Post: 02-26-2013, 10:31 PM
  5. Replies: 5
    Last Post: 08-22-2011, 01:05 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
  •  
Tech Forums: Microsoft Office Forums