Subform Issues (1 Viewer)

I've setup my database to allow data entry for results of microbiological tests. We run 3 different types of tests depending on what is being tested, but all three tests are sent back to us in a similar format. The main difference is that a few test names are different.

In this example, I've used generic names for the tests at my companies request, so "test1" may actually be something like "salt content". The important thing is that the Result1-9 all have different names so it seemed wise to keep them on seperate tables. Results 1-3 will always correspond to test1 and results 4-6 will always correspond with test2 etc. My tables look like this:

tblReport
- Invoice number (PK)
- dateoftest
- timeoftest
- dateofsample
- timeofsample

tbltest1
- test1ID (autonumber PK)
- test1Invoice (FK)
- test1Swablocation
- test1result1
- test1result2
- test1result3

tbltest2
- test2ID (autonumber PK)
- test2Invoice (FK)
- test2Swablocation
- test2result4
- test2result5
- test2result6

tbltest3
- test3ID (autonumber PK)
- test3Invoice (FK)
- test3Swablocation
- test3result7
- test3result8
- test3result9

I've setup forms and subforms for each test so my forms look like this:

frmtest1
subfrmtest1results

frmtest2
subfrmtest2results

frmtest3
subfrmtest3results

What i need to do is make it so that when I open frmtest1, it doesnt show records that were entered under frmtest2 or frmtest3. Currently the records for all 3 forms show up under each form. is there a way to filter them out?

Additionally if I've gone about this a terribly complicated way, I'm open to hearing other suggestions for how to set this up. Thank you for your help

Bruce

Your problem actually begins at the table level. Your structure is incorrect.

tblReport
- Invoice number (PK)
- dateoftest
- timeoftest
- dateofsample
- timeofsample

Click to expand...

Just a note about the above table. It is not necessary to store the Date and Time values in separate fields. A Date/Time field always stores both a Date and a Time value. It's actually stored as floating point number where the integer portion of the number is the Date and the decimal portion is the Time. So August 8th, 2012 3:30 pm is stored as;

41129.6458333333

If you don't enter one of the values (either the Date portion or the Time portion) it gets stored as 0, which for the Date portion represents December 30, 1899 and for the Time portion represents 12:00:00 am. So it's more efficient to store them in a single field. If you want to display only one or the other (Date or Time), that is simple enough with the DateValue and/or TimeValue functions or the other standard Date/Time format functions. Just FYI.

tbltest1
- test1ID (autonumber PK)
- test1Invoice (FK)
- test1Swablocation
- test1result1
- test1result2
- test1result3

tbltest2
- test2ID (autonumber PK)
- test2Invoice (FK)
- test2Swablocation
- test2result4
- test2result5
- test2result6

tbltest3
- test3ID (autonumber PK)
- test3Invoice (FK)
- test3Swablocation
- test3result7
- test3result8
- test3result9

Click to expand...

Here is where your table structure goes wrong. You should have one table for Tests (with a field to indicate if it is Test 1, 2 or 3) and one table for the Test Results. To separate out the various results you use queries. You don't store the results in separate tables. An example structure might look like;

tblReport
- InvoiceNumber (PK)
- TestDate
- SampleDate


tblTests
- TestID (autonumber PK)
- InvoiceNumber (FK)
- SwabLocation
- TestNumber

tblResults
- ResultID (autonumber PK)
- TestID (FK)
- ResultNumber
- ResultNotes

Oh wow, ok. I'm struggling to wrap my head around that concept a little bit, but I appreciate the nudge in the right direction I'll play with that and see where that takes me, Thanks!

Hey,

I've been working on that suggestion you made yesterday about seperating out the reports, results and tests tables but I'm either not quite getting the vision still or maybe my description of the scenario wasn't very clear.

I think that a more accurate description of my tables would probably look like this:

tblReport
- InvoiceNumber (PK)
- Sample Date/time (thanks for the tip)
- Test Date/Time

tblTestSet1
- TestSet1_ID (autonumber PK)
- TestSet1_Invoice (FK)
- TestSet1_Swablocation
- TestSet1_Test1Result
- TestSet1_Test2Result
- TestSet1_Test3Result

tblTestSet2
- TestSet2_ID (autonumber PK)
- TestSet2_Invoice (FK)
- TestSet2_Swablocation
- TestSet2_Test4Result
- TestSet2_Test5Result
- TestSet2_Test6Result

tblTestSet3
- TestSet3_ID (autonumber PK)
- TestSet3_Invoice (FK)
- TestSet3_Swablocation
- TestSet3_Test7Result
- TestSet3_Test8Result
- TestSet3_Test9Result

In other words there are 3 test sets, each with their own unique tests.

Testset1 for example could be "Allergen" testing.
- Test1Result - Dairy
- Test2Result - nuts
- Test3Result - Soy

TestSet2 would be an environmental swab test to check for bacteria
- Test4Result - bacteria1
- Test5Result - bacteria2
- Test6Result - bacteria3

TestSet2 would be an product integrity test
- Test4Result - firmness
- Test5Result - color
- Test6Result - texture

So I created 3 forms and 3 subforms.

frmTestSet1_Results
- InvoiceNumber
- Date/TimeSwab
- Date/TimeTest
SubfrmTestSet1_Results
- TestSet1_Swablocation
- TestSet1_Test1Result
- TestSet1_Test2Result
- TestSet1_Test3Result

frmTestSet2_Results
- InvoiceNumber
- Date/TimeSwab
- Date/TimeTest
SubfrmTestSet2_Results
- TestSet2_Swablocation
- TestSet2_Test4Result
- TestSet2_Test5Result
- TestSet2_Test6Result

frmTestSet3_Results
- InvoiceNumber
- Date/TimeSwab
- Date/TimeTest
SubfrmTestSet3_Results
- TestSet3_Swablocation
- TestSet3_Test7Result
- TestSet3_Test8Result
- TestSet3_Test9Result

So each form has the same form body, but different subforms. (maybe there is an easier way to do this?).

The problem is that If something is entered under frmTestSet3Results it is still seen as a record (with a blank subform) under fromTestSet1Results. I thought it might be possible to flag each record somehow with the form used to enter it and then tell the form to only display its own records.

if I've made this too complicated please let me know. I am happy to change the structure of my database if need be (it is still in development stage). it didn't seem like breaking down the tables as you suggested would do that though. Please correct me if I'm wrong.

Thank you again for all your help

Bruce

I'm attaching a sample file to illustrate the concept.

Note: The sample file has a form (frmReport) that contains two continuous subforms. To make this work you use a hidden text box that returns the PK value from the first continuous subform and is used to control the Master/Child link of the second continuous subform. In this example I have left that text box visible so you can see how it works. The first subform shows the different Tests for each Invoice. As you select different records in the first subform, the related Results for each Test are displayed in the second subform.

Thank you very much! I'll play with it!

Thank you for all your help,

It looks to me that your database would be able to enter new types of tests, but not necessarily new test results, unless I've missed something.
I apologize that I have either not communicated what I'm trying to do effectively or I'm too dense to understand how your example worked.

I've included a sample of what the reports that we get back look like. There are 3 sheets in this document, one for each test set. The database needs to be able to record the test results from each type of report.


Thanks again

Bruce

So does each invoice only include 1 test (as is the case in your sample Excel file), or could you possibly have more than 1 type of test on the same invoice number?

If the former, then it gets even simpler because you only need a table for the Invoice/Test and a table for the results. If the latter, then you still need three tables.

Also, just to clarify;

The results for each different type of test always have ID and Swab Location and in addition to that;

An Allergen test always has Dairy, Nuts and Soy - no more, no less

An Environmental test always has Bacteria1, Bacteria2 and Bacteria3 - no more, no less

An Integrity test always has Firmness, Color and Texture - no more, no less

Is that correct?

Each invoice number would correspond with a single test set.

for example:

You would never have an allergen and an environmental or integrity test connected to the same invoice number. They will always be on different invoices.

However -

You would have a tests for Soy, Nuts and dairy all on the same invoice.

OK. What about my post #9? Is that essentially correct?

- There are 4-6 different bacterias tested depending on the invoice, but they are always a selection of those 6

- The allergens are always 1-3 from the selection of 3

- the Integrity tests are always a selection of about 4-6 of 6 total

A single allergen test result will include
- swab#
- Location of Swab
- the facility it was taken from.

A single Environmental test will include
- swab#
- Location of Swab
- the facility it was taken from

A single Integrity Test will include the
- Name of product tested
- lot it was taken from
- daycode
- product grade.

I've included an updated Sample report to show the added fields.

OK, that's a bit of a different structure than I originally thought. This will require some design change from my previous example. I'll post back later today with a different example.

Here's the updated sample file. It uses a tabbed form with one tab for Allergen and Environmental tests, since they are structurally similar, and a second tab for the Integrity tests. It also has some examples of some crosstab queries that display the results in a format similar to the spreadsheets you import (not sure if that's something you needed or not, I just included it as an example). Click the Summary command buttons to see the crosstab queries.

Maybe you can make something like this work.