Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2013
    Posts
    4

    Unanswered: Do I need a master chronological table?

    I need to write a database application that involves entering the results in real time for a variety of tests and then periodically generating a report on the results in chronological order over some time period with some summary calculations at the end.

    The application would have two primary functions:

    1. Enter test results. The program would present a screen with fields for each type of test. The user (me) would enter the results and the program would store a record in the appropriate table with a time stamp.

    2. Generate report. The program would allow the user to select a date range and the types of tests to be included. It would then generate a chronological report showing the results of the selected tests over the selected dates. The results would be in chronological order across all tests. That is, the results would be intermixed. Each line in the report would contain the results of the next test in chronological order showing the relevant fields for that test.

    Let’s say I have three types of tests: A, B, and C. Test A has 3 results or “readings”. Test B has just 1 result. Test C has 2 results. Since each test has completely different results, I created a separate table for the results for each test. Let’s call them tblA, tblB, & tblC. Table A, for example, has 5 fields: primary key, datetime, result1, result2, result3.

    I would then have a Tests table (tblTests) with one record for each type of test. It would provide information about that test (name of the test, number of result fields, etc.).

    When I got this far, I noticed that I have no relationships between the tables. Is that a problem?

    Suppose the user requests a report for all 3 tests from 1/1/14 to 3/31/14. My plan is to run a query on all three tables (tblA, B, & C) selecting the records in that date range. Can I then merge the resulting records to create the report? The formatting of each row would have to be different.

    The alternative design I came up with is to have a “master chron” table (tblChron). It would contain one record for each test in any table and would include the datetime field for that record. That would make it easy to filter and sort, but I don’t feel good about moving the datetime field our of the data record into the master table record.

    I would appreciate any help, suggestions, comments, …

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Jennifer Murphy View Post
    I need to write a database application that involves entering the results in real time for a variety of tests and then periodically generating a report on the results in chronological order over some time period with some summary calculations at the end.
    If the system needs to perform heavy computations on Date/Time values and if you know how to use it, a Calendar Table can be handy. You replace the calls to Date/Time functions (DateAdd(), DateDiff(), etc.), both in the SELECT and the WHERE (or FROM with Joins) sections of the queries, by subqueries referencing the Calendar table.

    Quote Originally Posted by Jennifer Murphy View Post
    Let’s say I have three types of tests: A, B, and C. Test A has 3 results or "readings". Test B has just 1 result. Test C has 2 results. Since each test has completely different results, I created a separate table for the results for each test. Let’s call them tblA, tblB, & tblC. Table A, for example, has 5 fields: primary key, datetime, result1, result2, result3.
    Not necessarily: You can store all results in a single table with a column indicating to which test a row pertains. If different tests have different number of results, some column wound be left blank (Null) when not in use.

    Quote Originally Posted by Jennifer Murphy View Post
    I would then have a Tests table (tblTests) with one record for each type of test. It would provide information about that test (name of the test, number of result fields, etc.).

    When I got this far, I noticed that I have no relationships between the tables. Is that a problem?
    Creating a key you don't use is a problem because a key is an index. Indexing requires computational time. Not using the key means that some processing time would be used to compute the index (and possibly rearrange the data if it's a primary key), while gaining nothing in return. Keys and relationships make your database more strong (because it complies to the relational model and parts or all of the normal forms), but also because it's faster (= less computing time) to retrieve the data when the tables are properly indexed.

    Quote Originally Posted by Jennifer Murphy View Post
    Suppose the user requests a report for all 3 tests from 1/1/14 to 3/31/14. My plan is to run a query on all three tables (tblA, B, & C) selecting the records in that date range. Can I then merge the resulting records to create the report? The formatting of each row would have to be different.
    You could merge the resulting set of rows of the 3 queries using a UNION Query. As all subsets in a UNION query must have the same number of columns, you would have to patch the queries based on tables with less than the max. number of columns. In you example with tblA, tblB and tblC, you would have:
    QryA:
    Code:
    SELECT primarykey, datetime, result1, result2, result3 FROM TblA WHERE... ;
    QryB:
    Code:
    SELECT primarykey, datetime, result1, Null As result2, Null AS result3 FROM TblB WHERE... ;
    QryC:
    Code:
    SELECT primarykey, datetime, result1, result2, Null AS result3 FROM TblC WHERE... ;
    The condition on the number of columns beeing fulfilled, you could then use:
    Code:
    SELECT primarykey, datetime, result1, result2, result3 FROM QryA
    UNION
    SELECT primarykey, datetime, result1, result2, result3 FROM QryB
    UNION
    SELECT primarykey, datetime, result1, result2, result3 FROM QryC
    Using a single table for storing all results would be another (preferred) solution (see above).
    Have a nice day!

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    496
    Provided Answers: 24
    Myself, I would have had 1 table...tResults.
    all tests, all results in 1 tbl, therefore 1 query to get 'em out.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •