Boolean or Table

[zmbd]

I have two tables that I will be taking the cartesian product to get all of my possible combinations (right now that will return some 40 results); however, I will have an occation where I need to restrict the over all results to just a combination of a select group of records from each table (a subgroup of eight).

My first thought was a boolean field in each table and return only the cartesian product where both fields are true; however, my second thought is to use two more tables joined to restrict.

Thoughts?

[Rabbit]

What would these 2 other tables contain and how is the subgroup of 8 chosen?

If you can avoid the joins, that would be best. But if the extra joins mean you eliminate the manual process of checking which records you want to join, then that could be better. With such small records counts, I think the 2 extra joins won't be too big a resource hog if it eliminates a manual process.

[zmbd]

tbl_one - a series of desciptions. Being a lab, four of these are "standards", actually - five
Control, Desc1, Desc2, Desc3, Desc4... this is the current in the table; however, this could expand to have 12 to 100 entries in total

tbl_two - a scale, Here I've a PK, Scale1995, Scale2010
This one is tricky: the Scale used in 1995 is different than 2010; however related. The Scale in 2010 is 1 thru 10 inclusive. Here I need just the values 2 and 8 for the subgroup

1-10:Control have no meaining, there can only be a 0:Control
0:DescN has no meaning as the numeric is a scalar reference to the difference from the control for that property.

{2:Desc1, 8:Desc1; 2:Desc2, 2:Desc2;... 2:DescN, 8:DescN} is the subgroup that would be used as the "scale/calibration" standards.

The analitical reasults however may return a 0:Control or a 1-10:DescN.

I'm not anticipating either the scalar nor the descriptions used for the calibration to change; however, the scale has changed at least once, hence the 1995 field for cross reference with legacy data, and at one point we only used the control and Desc1 as our calibrator; thus, I'm tring to make it future proofed.

[Rabbit]

I think I need to see some sample data. I'm having trouble grasping some of the concepts.

[zmbd]

Here's the basic idea:

(mind you right now, this is all in an excel workbook (@.@)
Try pulling a 30/60/90 day rolling average.... arrrrghghgh

We calibrate the method using the control, we need a zero point, and then control+adulterants at the scalar 2 and scalar 8 degree of difference from control, of the dozens of adulterants out there, we use only 4: desc1...desc4.

So to calibrate:
tbl_callogbook
[pk]...[calid]............[scalar]...[property]...[pass]
[1]....[201401_001_001]...[0]........[Control]....[true]
[2]....[201401_001_002]...[2]........[Desc1]......[true]
[3]....[201401_001_003]...[8]........[Desc1]......[true]
[4]....[201401_001_004]...[2]........[Desc2]......[true]
[5]....[201401_001_005]...[8]........[Desc2]......[true]
[6]....[201401_001_006]...[2]........[Desc3]......[true]
[7]....[201401_001_007]...[8]........[Desc3]......[true]
[8]....[201401_001_008]...[2]........[Desc4]......[true]
[9]....[201401_001_009]...[8]........[Desc4]......[false]
[10]...[201401_001_010]...[8]........[Desc4]......[true]
 

The test has a series of unknowns that we randomly insert in to the workflow either a control or a control+adulterants (single blind test) and we normally restrict these blind-knows to those we use to calibrate with; however, a random intervals there will be scalar that is not a 2 or an 8 but the property will always be one of the Desc1-4

So the unknowns can have a result ranging from a scalar of 0 thru a scalar of 10 and then a descriptive as to what the diffence is:

So a typical sample run might be <*> are blind knowns:
tbl_lablogbook
[pk]...[sampleid].....[scalar]...[property]
[1]....[201401_001]...[0]........[Control]<*>
[2]....[201401_002]...[0]........[Control]
[3]....[201401_003]...[1]........[Desc1]
[4]....[201401_004]...[0]........[Control]
[5]....[201401_005]...[8]........[Desc2]<*>
[6]....[201402_001]...[10].......[Desc3]
[7]....[201402_002]...[0]........[Control]<*>
[8]....[201402_003]...[8]........[Desc2]<*>
[9]....[201402_004]...[2]........[Desc(n-2)]
[10]...[201402_005]...[0]........[Control]
[11]...[201402_005]...[5]........[Control]<<INVALID
[12]...[201402_005]...[0]........[Desc(n-2)]<<INVALID
 

Records 1, 5, and 8 would be blind knowns presented as a regular sample
Records 11 and 12 wouldn't be possible you cannot have a 5 scalar difference from the control nor can you have a 0 scalar difference and yet have a description of something different from the control... in the form there would be logic to prevent this.

[Rabbit]

So if I understand correctly, and I probably got something wrong here.

You have two tables, a scale and a property.

Scale table

PK Scale1995 Scale2010
1  0         0
2  2         1
3  3         2
.......................
11 11        10

Property table

Property
Control
Desc1
Desc2
Desc3
Desc4

And with these two tables, you're trying to derive the table named tbl_callogbook? Or is it tbl_lablogbook you're after?

[zmbd]

And with these two tables, you're trying to derive the table named tbl_callogbook? Or is it tbl_lablogbook you're after?

ahh...
the two tables play two different roles for the logbook tables and the forms. The "tbl_*logbook" are more historical/audit and I need to populate controls, validate results etc...

For example:
tbl_lablogbook
[lablogbook_pk]autonumber
[lablogbook_sampleid]text(50)
[lablogbook_fk_scalar]numeric(long)
[lablogbook_fk_property]numeric(long)

For the controls on the form, for the lablogbook
This is the general scalar(x)property

SELECT tbl_scalar.scalar_pk
   , tbl_scalar.scalar_scale2006
   , tbl_property.property_pk
   , tbl_property.property_name
FROM tbl_property, tbl_scalar
WHERE (((tbl_scalar.scalar_pk)=1) 
    AND ((tbl_property.property_name)="Control"))
Union
SELECT tbl_scalar.scalar_pk
   , tbl_scalar.scalar_scale2006
   , tbl_property.property_pk
   , tbl_property.property_name
FROM tbl_property, tbl_scalar
WHERE ((tbl_scalar.scalar_scale2006)>0);

This would be used for two things, validation and "quick select" (instead of two combo boxes, I have one) for the result in tbl_lablogbook...

However when I go to calibrate what I'm using now is something like the following so that if we needed to add levels or properties:

Once again, this is used only for validation and for the "quick selct" on the form.

SELECT tbl_scalar.scalar_pk
   , tbl_scalar.scalar_scale2006
   , tbl_property.property_pk
   , tbl_property.property_name
FROM tbl_property, tbl_scalar
WHERE (((tbl_scalar.scalar_pk)=1) 
    AND ((tbl_property.property_name)="Control"))
Union
SELECT tbl_scalar.scalar_pk
   , tbl_scalar.scalar_scale2006
   , tbl_property.property_pk
   , tbl_property.property_name
FROM tbl_property, tbl_scalar
WHERE (((tbl_scalar.scalar_scale2006)>0) 
    AND ((tbl_property.property_usethisforspike)=True) 
    AND ((tbl_scalar.scalar_usethisforspike)=True));

So why combine and keep seperate.
So the LabMonkey (that's me (^_^) ) runs the test:
Blind-Control: I need to track, did LabMonkey return Control if not then fail, else pass
Blind-Spike: Did LabMonkey detect difference? Was it the correct property, if no, then fail, if yes then did the LabMonkey return the correct scalar within error (usually +/-1) then pass else fail.

Yes, I'm using an unbound CBO and the afterupdate event to do the quick entries (^_^), the user types 0, or 1 and the first letter of the property, enter, and the entries are made into the table (or LabMonkey can use the table controls and do the double selection thing). The table is bound and the table controls are bound the form while open has the record sets above open at the module level (closed with onclose event) the record/form before update event looks at the current record fields and searches against the above recordset for a match of not found then cancel and warn the user.

[Rabbit]

So the _usethisforspike fields are chosen by the user whenever they want to run a test?

And the question is whether to continue to use that boolean or whether or not you should use 2 tables to store which values they want to run at the moment. I think both options have their merits.

The 2 table route is, imho, a cleaner design. But it is more resource intensive and ever so slightly more complicated to implement. Whereas the boolean route is not as "elegant" but would run more quickly with large data sets.

[Rabbit]

A side note on your scale table. It sounds like scale is a slowly changing dimension. In that the values may change over time but you want to preserve the historical values. What we often do with that type of data is to use a start and end date.

tblSlowlyChangingDimension

PK   EntityID   Desc   StartDate   EndDate
1    1234       Bob    1/1/1900    6/30/2007
2    1234       Bobby  7/1/2007    12/31/9999
3    5678       Jen    1/1/1900    12/31/9999