Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19

    Expert advice needed

    Hello everyone. I hope 2018 is being good to you so far?



    I have a database I have designed while teaching myself to use Access and I have now run into some problems I believe are related to table relationships?

    I was wondering, if I upload the database, can someone help me tidy things up and fix these issues? Obviously, I'd like to learn from my mistakes in the process, so I don't run I to this situation again.

    Would anyone be willing to give me a hand with this?

    Thanks,
    Mark

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sure, we can help. We will need to know what your requirements are in detail, what you have done so far and where you think you may have gone wrong. Make sure you have read all about normalization and what that entails, and have followed the rules.

  3. #3
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19
    Aytee111,

    Thank you. I am not at my computer at the moment but I will give much more detail shortly as well as the database to look at.

    I will read about normalisation, which I believe I recently have. A refresh will be good, might have a better understanding now.

    I'll be in touch ASAP.

    Thanks for the response

    Mark

  4. #4
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19
    Hello,

    Sorry for the delay in my post. Hopefully this gives you all the information you need:

    Here is a copy of the database: https://drive.google.com/open?id=1er...M0VS3hX12Pl64f

    Document with screenshots: https://drive.google.com/open?id=1r7...uHefx043lG0XQF

    Here is what I want to do:
    Tables

    A little bit about the tables that make up my database.

    Table Description
    Areas Areas within departments to distinguish different locations.
    Departments Departments within the business.
    Owners Owners of documents (department managers).
    Signers Approved signers of documents.
    SOP Project Contains all relevant information for a Document
    Status Holds the status options for each document
    Database Table Relationships

    I would like to ensure that I have my relationships setup correctly and if I need to clean up tables and move items (if necessary)?

    ALL SOP Form

    This is the main form of the database where users will either create a new Standard Operating Procedure or modify and existing one.

    Form Field Descriptions

    Field Description
    Title Title of the document
    Department Department of document
    Area Area of department
    Planned Date Date document is planned to start
    Due Date Date document is due for completion
    Date Started Date documented was official started
    Date Signed Date the document was completed / signed
    Owned By Person document is owned by
    Signed By Confirmation document is correct and completed
    Approved By Document owner approval
    Top Approver Top level approval
    Produced By Document produced by
    Comment General comments for this document
    Review Date Review date of document
    Status Status of document
    Reports by Status General reports, not a field
    Review Note Document review comments
    Areas of Focus

    Department Drop Down

    I would like to be sure I have linked the Department drop down correctly and the data is being called from the correct table and sorted in the correct table.

    Area Drop Down

    I would like to be sure I have linked the Area drop down correctly and the data is being called from the correct table and sorted in the correct table.


    Owned By Drop Down

    I would like to be sure I have linked the Owned By drop down correctly and the data is being called from the correct table and sorted in the correct table.

    Status Drop Down

    I would like to be sure I have linked the Status drop down correctly and the data is being called from the correct table and sorted in the correct table.

    Conclusion

    • Are the table’s setup correctly?
    • Are the relationships setup correctly?
    • Are the drop down fields setup correctly? Or is there a better way to do this?
    • Is there any advice you can give me on making things better with the database?

    Please ignore

    • All Reports
    • All queries except 6SOP_Issued

  5. #5
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You have a lot of possible responses here, but I'll make some starting observations.

    You have multiple fields for staff assignment in the main SOP table but instead of storing the EmployeeID you are storing the name, you also effectively have two tables for them.
    In an ideal world you would have a single employees table, a table of roles and a junction table storing each employees-roles. This allows an employee to have multiple possible roles concurrently.

    In your drop down lists for those roles you then would query the tables and restrict the available list to those employees with the appropriate role.

    You only have a single Comment field. I would have a comments table linked back to the SOP. You can then store who and when and the comment and have a dated history of comments against the SOP.
    You could even add a comment type and remove the review comment field for the same reason.

    You have a lot of date fields in the SOP. Whilst some are what I would term "fixed" with the SOP (something like a Creation Date), I wonder whether you should have a separate table for "SOP actions" with the dates.
    This way if another process is added it simply becomes another record rather than a redesign.

    These are only thoughts - you know your business and processes a great deal better than we do.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Do not use links, rather attach the files. Not many of us use links.

  7. #7
    Join Date
    Aug 2017
    Location
    Batley, United Kingdom
    Posts
    19
    Taking another stab at it. Any feedback would be great. What do you think of the relationship setup?
    Click image for larger version. 

Name:	image002.jpg 
Views:	19 
Size:	53.1 KB 
ID:	32498

  8. #8
    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

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Can the same area value be found in multiple departments? If not, I don't see the need for an area/department junction table.
    Not sure what the reason for junction for owner and employee since owner was described as the one who owns documents. It may be valid but without really understanding the relationship, not sure of that one either. The only other comment would be your use of what looks like multi-value fields (since I don't use them and probably never will, I can't say what they look like in a relationship). Many of us here would not use them. AFAIK, their primary reason for existing is to work with SharePoint lists. Beyond that, you may find that some of your queries won't work.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Inventory_DataBase_Design Advice Needed
    By ebelingbl in forum Database Design
    Replies: 2
    Last Post: 03-22-2014, 07:19 PM
  2. general advice needed on db structure
    By mike_980 in forum Access
    Replies: 5
    Last Post: 11-27-2013, 05:29 PM
  3. Replies: 3
    Last Post: 11-24-2013, 01:39 PM
  4. On error go to advice needed
    By AndycompanyZ in forum Programming
    Replies: 6
    Last Post: 06-24-2011, 04:49 AM
  5. Form sizing advice needed
    By DanW in forum Forms
    Replies: 0
    Last Post: 11-15-2009, 09:35 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