Results 1 to 9 of 9
  1. #1
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38

    cross-reference tables

    Good evening everyone,
    in a chemical lab, an analysis is made of several tests (usually 10 to 20). A test is a measure performed on a parameter, with a given unit of measure, using a set of methods (usually 1 to 3). The users first create an analysis, then the tests to be performed, selecting the parameter, the UM and the methods to be used. The same couple parameter + UM could be tested using different methods. Then they associate the tests to an analysis, perform the tests and record the outcomes. I developed the attached database, where tables Outcome and Test are essentially two cross-ref tables.


    Do you think it is correct (as I hope)? Do you have any comment to share?
    I am worried this design is someway fragile, as I used foreign keys to set up an analysis... may this be prone to some problem?
    Thanks for your suggestions
    Attached Files Attached Files

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,365
    @Davide,

    Does the database support your testing, analysis and results and whatever review is required?
    How many concurrent users are there?
    Are there some identified issues that concern you?

    Just opened your database and received this:
    Click image for larger version. 

Name:	Davide1.PNG 
Views:	21 
Size:	24.7 KB 
ID:	47109

  3. #3
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    I also got errors...mostly due to forms missing and field names changed/missing. I don't use Macros, so I didn't spend any time trying to fix the errors.

    This is the way I would design the tables. I don't use compound fields for the PK - I use an autonumber field and use a compound INDEX to eliminate duplicates. The " ID" at the end of the name means (for me) the field data type is an autonumber.

    Click image for larger version. 

Name:	Structure1.png 
Views:	15 
Size:	82.0 KB 
ID:	47113

  4. #4
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Yes, sorry about that, i deleted some unnecessary masks and tables
    I do think the DB does what it should, but i was just wondering whether this design/implementation with X-ref tables is correct, in the sense that all the analysis configuration relies on FKs
    May this create a problem in the future? Is it good design practice?
    Thanks for your advice

    Quote Originally Posted by orange View Post
    @Davide,

    Does the database support your testing, analysis and results and whatever review is required?
    How many concurrent users are there?
    Are there some identified issues that concern you?

    Just opened your database and received this:
    Click image for larger version. 

Name:	Davide1.PNG 
Views:	21 
Size:	24.7 KB 
ID:	47109

  5. #5
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    Hello Steve, and thanks.
    I do understand you simply added an automatic numeric ID to X-refs and I do know the pros (and cons).
    But it seems to me you generally agree with the design then,

  6. #6
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    217
    I would see it more with this structure, as long as I have correctly interpreted your explanations.
    Click image for larger version. 

Name:	Relazioni.jpg 
Views:	12 
Size:	69.9 KB 
ID:	47115
    However in any case you should always manage the properties of the fields:

    • Size (for text fields)
    • Required (set to "Yes" for to make the entry of a fundamental data mandatory)
    • Indexed (set to "Duplicates allowed" for fields that constitute an external relationship)
    • Indexed (set to "Duplicates not allowed" for fields that allow a single entry of a value)

  7. #7
    Davide is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    38
    No, that is actually very far from what I want. A test exists regardless of analysis and outcomes
    You need an outcome table to record the outcome of a test which could be performed many times, in different analysis
    Also, a test may belong to one or more Types, and a Type (or category) is a set of tests. That is why you need a X-ref b/w tests and types

    Quote Originally Posted by CarlettoFed View Post
    I would see it more with this structure, as long as I have correctly interpreted your explanations.
    Click image for larger version. 

Name:	Relazioni.jpg 
Views:	12 
Size:	69.9 KB 
ID:	47115
    However in any case you should always manage the properties of the fields:

    • Size (for text fields)
    • Required (set to "Yes" for to make the entry of a fundamental data mandatory)
    • Indexed (set to "Duplicates allowed" for fields that constitute an external relationship)
    • Indexed (set to "Duplicates not allowed" for fields that allow a single entry of a value)

  8. #8
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    217
    From what you have said it is clear that you do not know how to read the relationships set up.
    It is the type of test that exists independently of the analysis and results.
    The type of test is related to the test with respect to a precise analysis, a parameter and a unit of measurement.
    The Test, then, can be performed through one or more Methods that give rise to a result.
    You never talked about Categories.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    Quote Originally Posted by Davide View Post
    Hello Steve, and thanks.
    I do understand you simply added an automatic numeric ID to X-refs and I do know the pros (and cons).
    But it seems to me you generally agree with the design then,
    No, the first thing I look at is the design of the tables and the relationships. Then I try to use the forms and try to add/edit records.
    But, since there were so many errors, I couldn't make the db function and quit looking at the dB.

    The big question is "What is the purpose of the dB"?
    Quote Originally Posted by Davide View Post
    <snip> in a chemical lab, an analysis is made of several tests (usually 10 to 20). A test is a measure performed on a parameter, with a given unit of measure, using a set of methods (usually 1 to 3). The users first create an analysis, then the tests to be performed, selecting the parameter, the UM and the methods to be used. The same couple parameter + UM could be tested using different methods. Then they associate the tests to an analysis, perform the tests and record the outcomes. <snip>
    After I looked at the relationships posted by CarlettoFed, I would lean towards his design.

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

Similar Threads

  1. Many to many cross reference table as a subform?
    By craigugoretz in forum Forms
    Replies: 2
    Last Post: 02-07-2015, 01:51 PM
  2. Replies: 4
    Last Post: 08-13-2012, 04:39 PM
  3. Cross Checking two tables
    By eric.kung in forum Programming
    Replies: 12
    Last Post: 10-05-2011, 08:14 PM
  4. Cross reference
    By Anne in forum Access
    Replies: 9
    Last Post: 09-23-2011, 08:01 AM
  5. Replies: 1
    Last Post: 06-18-2011, 10:00 AM

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