sql coding problem 3

[Ollie71]

I thought this would be simple but I cant seem to find a satisfactory way of doing it.

Basically I have a form that has a drop down name selector. Once you select a name, a list box is populated with test results. You then select a test result.

A query is run populating a subform giving the matching test results(matching the unique test code), which are basically test results of 47 different elements tested.

So far so good, but now I need to parse through the results getting out information and this is where ive hit a snag.

The best example is that there are 24 toxic elements and I need to get all the ones that test greater than one. I need to save the element names in a string to txt control of those >1 and ideally be able to identify those elements later in different combinations.

The trouble im having is that the query builder can easily 'AND' things together but only a few will be >1 so as soon as one fails the query fails. Its seems that you can only 'OR' maxium of 6 things together, which would mean 4 different queries just to parse the 24 toxic elements.

I had thought that looping through the query in code and storing results in a txt array might be the way to go. But I dont have the knowledge to do that to a query. I have found some references to doing it by using currentDb and record sources loaded with the query but am not sure how to code it.

The only similiar thing ive done that might achieve the end result is create with tags an array of controls to loop through, test with if or case and store results in array. I did this with tick boxes once so you could select many different items and an array of your choices for that record was stored. It seems a very adhoc way of ahcieving it though. But I guess it could be done with the sub form query results. I was hoping there was a better more direct way to loop through the query results directly and pull out what I need, instead of populating controls and manipulating the data afterwards, which seems fraught with danger for data security and accurracy.

Can anybody help me with the code? or point me in the right direction with it or tell me how I might achieve it with queries or have any other ways of doing it.
The form and query I use so far has this structure:

-drop down text box **select name**
-txt box binds cust code which is used to populate list box with test results
-select list box test result
-Subform is populated with matching testcode and results
[testcode]1234, [Elemnt1]0.8, [Elemnt2]1.2, [Elemnt3]0.6, [Elemnt4]1.3.......[Elemnt24]0.12 etc

Results needed:
string of element names that test >1
[unbound_control]="[Elemnt2],[Elemnt4].....etc"

? perhaps txt array containing element names so can parse through for different combinations for later analysis.

 

[TheAceMan1]

How are ya Ollie71 . . .

Have a look at the [blue]IN[/blue] clause. Example:

[blue]WHERE [Element] [blue]IN[/blue] ('Element1', 'Element2', ... , 'Element24')[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Woops! ... hit submit too soon.

If you have a seperate table of the toxic elements the WHERE clause becomes:

[blue]WHERE [Element] IN (SELECT tblToxicElement.Element FROM tblToxicElement)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[MajP]

Sorry but you come up with some overly complicated ideas and a lot of extraneous information.

The problem is that your data is not normalized, but that is simply fixed with a Union query.

Assume

ID testCode element1 element2  element3  element24
1  1234      0.8     1.2       0.6       1.8
2  4567      0.2     1.4       1.2       0.2

qryNormalize

select testCode, Element1 As ElementVal,"Element1" As ElementName, 1 as SortOrd from tblTestCode
UNION 
Select  testCode, Element2 As ElementVal,"Element2" As ElementName, 2 as SortOrd from tblTestCode
UNION
Select  testCode, Element3 As ElemenValt,"Element3" As ElementName, 3 as SortOrd from tblTestCode
UNION 
Select  testCode, Element24 As ElementVal,"Element24" As ElementName, 24 as SortOrd from tblTestCode;

results

testCode ElementVal ElementName
1234	0.6	    Element3
1234	0.8	    Element1
1234	1.2	    Element2
1234	1.8	    Element24
4567	0.2	    Element1
4567	0.2	    Element24
4567	1.2	    Element3
4567	1.4	    Element2

SELECT qryNormalize.testCode, qryNormalize.ElementVal, qryNormalize.ElementName, qryNormalize.SortOrd
FROM qryNormalize
WHERE (((qryNormalize.ElementVal)>1))
ORDER BY qryNormalize.testCode, qryNormalize.SortOrd;

results

testCode Element	ElementName
1234    1.2     Element2
1234    1.8     Element24
4567    1.4     Element2
4567    1.2     Element3

 

[MajP]

My final query was called "qryGTN1"

Public Function getElementString(testCode As Long, qryName As String) As String
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("Select ElementName from " & qryName & " where testCode = " & testCode)
  Do While Not rs.EOF
    getElementString = getElementString & rs.Fields("ElementName") & ", "
    rs.MoveNext
  Loop
  If Len(getElementString) > 0 Then
    getElementString = Left(getElementString, Len(getElementString) - 2)
  End If
End Function

you can use this in any calculated control or query

example
=getElementString(1234,"qryGTN1")
returns a string: Element2, Element24

=GetElementString(forms![frmName].txtTestCode,"qryGTN1")
where .txtTestCode is a text box that holds a test code value

 

[Ollie71]

Im well Aceman, how are you?

Yes I do tend to overshare, saves reposts of more info requests, at least thats the theory.

I see what you mean in being not normalized, I made this structure as it saves on numbers of records (as there are thousands of tests) and keeps the test information together. But this obviously makes it harder when wanting to do certain tasks.

Thanks for your help both of you, I will look up how to use the IN clause and look at normilizing with union queries, which ive never had to use till now. What you get for being self taught I guess.

cheers

 

[MajP]

Does each test have exactly 24 elements? If not you are wasting a lot of space not saving space. You could have millions of test and it would be far more efficient and use less space to do the following:

tblTestCode
testCodeID
fields about a specific test

tblElements
elementID
elementName
fields specific to an element

joinTbl_TestCode_Elements (many elements related to many tests)
testCodeID
elementID
elementValue
fields unique to an element within a given test

 

[Ollie71]

No they dont have exactly 24 in them. They all have 47 elements which are split later into 3 subclasses via queries. The other problem is that each test is a unique testing of the lvls of these elements in the test sample.

In normilizing the tables like above you would have a table where each test would require 47 record entries for each element and its test result data, repeating the testcode each time as well as another table linked probably on testcode, linking the other data thats specific to that test i.e the persons name, dates of test, practitioner, sample date, description, subject etc. Not to mention that although the testcode is unique there are in fact 2. The majority are of one type, but there is a MiscCode (miscellenous code) type as well. They are one or the other.

In my structure I have one record for every test which has a unique transactionID, is either a TestCode or MiscCode, customer id (which links it to the customer database table) the above dates etc, 47 elements (which are the table fields) storing that test data as well as field for ph and weight (+/-).

There are currently over 4500 tests in the database which under my structure gives me the same number of records, albeit with a complex field structure. Under a more normalized split table system I calculate there would be over 211,500 records to store the same information.

 

[MajP]

If you are trying to state that you did not know how to set this up properly and make a user interface to support a proper data structure, well OK. We could have helped.

If you are suggesting that this is a good idea you are wasting your time.

Your structure is 47fields X 4500, and mine would be 3 fields by 211,500. My design is better, more efficient, smaller in size (yes likely smaller), easier to manage, and more flexible

It is not linear. Deeper is almost always better than wider when it comes to db. The overhead for a field is far greater than the overhead for a record. The efficiency in queries returning a few fields with lots of records, is far greater than returning a few records with lots of fields.

If I had a table like this that contains a testcode id, an element ID, and element value. This would contain 211K records. Both the testCodeID_FK, and the elementID_FK would be indexed. To query this table and return all Elements with Value > 1 for test code 1234, would be nearly instantaneous, and also very simple to write.

jncTbl_TestCode_Element
testCodeID_FK
elementID_FK
elementValue

The work arounds proposed by AceMan and myself would be difficult to write as demonstrated, and extremely slow. In jet SQL Union and Subquerys are often not fast.

 

[PHV]

Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Ollie71]

I think you misunderstood me. I was simply stating some of the reasons why it is like that, not that it was a particulary good idea. It has made some of the importing and other things easier to code and changing it is going to require a lot of re-coding, but I do see your point about the performance, especially upon reading the article above that PHV pointed to, which was very good by the way.

Its interesting that I have used those normalization rules with lots of the other tables in my database without even realizing it. Upon reading that very informative article (which is the easiest to understand that ive read about it so far), Im far more clued in about normalization and how it relates to good database design.

I will have to look at my database and see if I can normalize those sections that ive made overly complicated, its obvious that it will be worth the effort of reworking the tables and queries for a better structure.

Thanks for all your replies, help, patience and code hints, they have been extremely helpful.
You guys are so responsive and helpful, and as a self trained coding amateur, I really do appreciate your efforts.