Switch vs Mapping Table (how to use "greater then or equal to") 1

[alevol]

Hello All

I've been using Switch() in queries, but want to change to the joined Mapping Table method discussed in thread701-1556644

An example of the Switch I was using in the SELECT statement:

Switch(
Schizophrenia_SeveritySum_Twin1 >= 12, 78,
Schizophrenia_SeveritySum_Twin1 = 11, 76,
Schizophrenia_SeveritySum_Twin1 = 10, 72,
Schizophrenia_SeveritySum_Twin1 = 9, 70,
Schizophrenia_SeveritySum_Twin1 = 8, 66,
Schizophrenia_SeveritySum_Twin1 = 7, 62,
Schizophrenia_SeveritySum_Twin1 = 6, 60,
Schizophrenia_SeveritySum_Twin1 = 5, 56,
Schizophrenia_SeveritySum_Twin1 = 4, 52,
Schizophrenia_SeveritySum_Twin1 <= 3, 50)
AS Schizophrenia_TScore_Twin1

As you can see, I must be able to use, in this example, 78 for any value >=12. It wouldn't be practical to add many values (13,14,15,16...) to the mapping table, because there are more than just two columns in it, like this:

Tscore CatA CatB CatC CatD Schizo...
78 >=21 >=21 >=39 >=12
76 20 20 38 >=19 11
76 37
74 19 36 18
72 19 35 17 10
72 34
70 18 18 33 16 9
68 17 17 32 15
68 31
66 16 16 30 8
64 15 15 29 14

Does anyone know how to indicate a ">=" or a range of values in the joined mapping table? Maybe it's just not possible, but then I wouldn't know what to do when I have Switch's that have more than 15 conditions in them (since Access won't take them).

Anybody have any ideas?

Thanks a lot!

Alex

 

[MajP]

Can you better explain in words what the input is and what the desired output is? The design should be more like this.

tblTScores
categoryID
MinValue
MaxValue
TScore

Examples
CategoryID MinValue MaxValue TScore
CatA 21 78
CatB 21 78
CatC 39 78
....
CatA 20 20 76

It may be hard to actually do a straight join, but a simple dlookup should return the correct value or a simple function using the above table.

 

[alevol]

The database is for collecting questionnaire scores related to symptoms. I have a query which adds up the raw scores. Then another query matches each raw score with a statistical T-score, according to a distribution specific to each diagnosis.

So, the example above is a little snippet from the second query, which matches a raw score to a T-Score:

Switch(
Schizophrenia_SeveritySum_Twin1 >= 12, 78,
Schizophrenia_SeveritySum_Twin1 = 11, 76,
Schizophrenia_SeveritySum_Twin1 = 10, 72,
Schizophrenia_SeveritySum_Twin1 = 9, 70,
Schizophrenia_SeveritySum_Twin1 = 8, 66,
Schizophrenia_SeveritySum_Twin1 = 7, 62,
Schizophrenia_SeveritySum_Twin1 = 6, 60,
Schizophrenia_SeveritySum_Twin1 = 5, 56,
Schizophrenia_SeveritySum_Twin1 = 4, 52,
Schizophrenia_SeveritySum_Twin1 <= 3, 50)
AS Schizophrenia_TScore_Twin1

As you can see, the matching is a bit erratic (for example, there is no 54, 58, 64, etc, T-Score for Schizophrenia).

I thought I would put all these raw-score to T-score matches in one table, instead of using all the Switch's I was using (one for each diagnosis) and then simplify the second query to something like this:

SELECT FamilyID,
a.TScore AS ADHD_Inattentive_TScore_Twin1,
b.TScore AS ADHD_HyperImpul_TScore_Twin1,
c.TScore AS ADHD_Combined_TScore_Twin1,
d.TScore AS ODD_TScore_Twin1,
e.TScore AS Schizophrenia_TScore_Twin1,
f.TScore AS Dysthymia_TScore_Twin1,
g.TScore AS Bipolar_TScore_Twin1,
h.TScore AS Inconsistency_TScore_Twin1,
i.TScore AS Conduct_TScore_Twin1,
j.TScore AS GeneralizedAnxiety_TScore_Twin1,
k.TScore AS SeparationAnxiety_TScore_Twin1,
l.TScore AS MDD_TScore_Twin1,
m.TScore AS EatingProblems_TScore_Twin1

FROM Scoring_Inventory_SymptomSeveritySums, TScoreTableInventory a, TScoreTableInventory b,
TScoreTableInventory c, TScoreTableInventory d, TScoreTableInventory e, TScoreTableInventory f,
TScoreTableInventory g, TScoreTableInventory h, TScoreTableInventory i, TScoreTableInventory j,
TScoreTableInventory k, TScoreTableInventory l, TScoreTableInventory m

WHERE ADHD_Inattentive_SeveritySum_Twin1 = a.ADHDInatt AND
ADHD_HyperImpul_SeveritySum_Twin1 = b.ADHDHyper AND
ADHD_Combined_SeveritySum_Twin1 = c.ADHDCom AND
ODD_SeveritySum_Twin1 = d.ODD AND
Schizophrenia_SeveritySum_Twin1 = e.Schizo AND
Dysthymia_SeveritySum_Twin1 = f.Dysthymia AND
Bipolar_SeveritySum_Twin1 = g.Bipolar AND
Inconsistency_SeveritySum_Twin1 = h.Inconistent AND
Conduct_SeveritySum_Twin1 = i.ConductM AND
GeneralizedAnxiety_SeveritySum_Twin1 = j.GenAnxM AND
SeparationAnxiety_SeveritySum_Twin1 = k.SepAnxM AND
MDD_SeveritySum_Twin1 = l.MDDM AND
EatingProblems_SeveritySum_Twin1 = m.EatingM

AND FamilyID IN (SELECT FamilyID FROM Demographics WHERE TwinGender = 'M');

But this wouldn't allow me to have the >= I had in the Switch().

I don't know if this is any clearer, hope it is. (The table that I included in my first post is only part of the whole thing, and each column represents a different diagnosis, but this is a table only for looking up the T-Score that is paired to each raw-score).

 

[MajP]

The table I describe is the correct approach.
How you use the table would depend on the organization of your other tables. Are the other diagnosis categories fields or records? Of course they should be records and not fields, but my guess is that your data is not properly normalized.

without seeing your structure the easy way would be to wrap the table results in a function.

tblTScores
category           MinValue  MaxValue  Tscore
Schizophrenia      12        99999      78
Schizophrenia      11        12         76
Schizophrenia      10        11         72
Schizophrenia      9         10         70
...
other categories

use the table in a function

public function getTScore(categoryID as variant, inputScore as variant) as variant
  dim strWhere as string
  if not isnull(categoryID) and not isnull(inputScore) then
     strWhere = "Category = '" & categoryID & "' AND MinValue <= " & inputScore & " AND MaxValue > " & inputScore
     getTScore = nz(dlookup("TScore","TblTScores",strWhere))
  end if
end function

to use in a sql

select getTScore("Schizophrenia","Schizophrenia_SeveritySum_Twin1") as SchizophreniaTScore,...

The same table can probably be used in a pure sql solution, but without knowing how the diagnosis categories are organized it is hard to tell.

 

[alevol]

Hi MajP

Thanks a lot, this makes lots of sense now. There is one table for each one of the questionnaires we use. The one here is a questionnaire that asks teenagers about their behavior. According to scoring rules for the questionnaire, the answers of certain questions are added and this makes the raw score for a certain Symtom Category. I am doing this through a query. This is an example:

SELECT FamilyID,

CInt(Inventory_Twin1_Item1) +
CInt(Inventory_Twin1_Item2) +
CInt(Inventory_Twin1_Item3) +
CInt(Inventory_Twin1_Item4) +
CInt(Inventory_Twin1_Item5) +
CInt(Inventory_Twin1_Item6) +
CInt(Inventory_Twin1_Item7) +
CInt(Inventory_Twin1_Item8) +
CInt(Inventory_Twin1_Item9)
AS ADHD_Inattentive_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item10) +
CInt(Inventory_Twin1_Item11) +
CInt(Inventory_Twin1_Item12) +
CInt(Inventory_Twin1_Item13) +
CInt(Inventory_Twin1_Item14) +
CInt(Inventory_Twin1_Item15) +
CInt(Inventory_Twin1_Item16) +
CInt(Inventory_Twin1_Item17) +
CInt(Inventory_Twin1_Item18)
AS ADHD_HyperImpul_SeveritySum_Twin1,

...

Just out of curiosity, how could you use the Tscore lookup table directly from SQL (that is, without the function)?

Many thanks again,

Alex

 

[dhookom]

but my guess is that your data is not properly normalized

It looks like MajP is correct. A normalized table structure would have one "score" per record rather than multiple scores in multiple fields.

You can find a normalized demo at At Your Survey

This solution allows you to more easily sum across records rather than across fields.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Yes, you really should look at At Your Survey. This is a properly designed survey database. Building a proper survey database is hard to do, and takes a good understanding of database normalization. It is easy to do what you did, except it is of little use for returning data. That is why you end up with these overly complicated work arounds such as switch functions. You are paying big time on the back end for poor design on the front end.

If it was properly normalized you could do a very simple aggregate query to do the complex summation you show. You then would end up with something like this

FamilyID Diagnosis SumOfSeverity
1 Schizophrenia... 12
1 ADHD.. 21
...
2 Schizophrenia... 19

Now a simple query to the tblTScores, linked by diagnosis category where
"Category = Diagnosis AND MinValue <= SumOfSeverity AND MaxValue > SumOfSeverity"

There are many cases where the amount of work to fix a database is just not worth it. However, in your case I suggest you consider biting the bullet and redesign the whole database. You would be able to do these long complex sql queries with very short simple queries. If you are planning on doing a lot of data analysis, then fixing it will save you a lot of time and frustration in the long run. If you plan to ever use this again, you are wasting your time if you do not fix it.

 

[alevol]

Thanks a lot for the advice MajP and dhookom. I will definitely check out At Your Survey for future projects. But for this one this has been a lot of help. I am a psychology researcher and not a programer, so my solution was probably much simpler conceptually than it should have been. Using one record for each answer/item sounds intriguing.

 

[alevol]

Hello again

I wonder if you can help me figure out a quite bizarre result I'm getting. I've gone ahead with the overly complicated query, since my database already has data in it and I can't really redesign it right now. The good news is that once these scoring queries are finished I won't have to do any more work on it, since all the analysis gets done in a separate statistical program.

The query to add up the items looks like this:

SELECT FamilyID,

CInt(Inventory_Twin1_Item1) +
CInt(Inventory_Twin1_Item2) +
CInt(Inventory_Twin1_Item3) +
CInt(Inventory_Twin1_Item4) +
CInt(Inventory_Twin1_Item5) +
CInt(Inventory_Twin1_Item6) +
CInt(Inventory_Twin1_Item7) +
CInt(Inventory_Twin1_Item8) +
CInt(Inventory_Twin1_Item9) 
AS ADHD_Inattentive_SeveritySum_Twin1,
	
CInt(Inventory_Twin1_Item10) +
CInt(Inventory_Twin1_Item11) +
CInt(Inventory_Twin1_Item12) +
CInt(Inventory_Twin1_Item13) +
CInt(Inventory_Twin1_Item14) +
CInt(Inventory_Twin1_Item15) +
CInt(Inventory_Twin1_Item16) +
CInt(Inventory_Twin1_Item17) +
CInt(Inventory_Twin1_Item18) 
AS ADHD_HyperImpul_SeveritySum_Twin1,
	
CInt(Inventory_Twin1_Item1) +
CInt(Inventory_Twin1_Item2) +
CInt(Inventory_Twin1_Item3) +
CInt(Inventory_Twin1_Item4) +
CInt(Inventory_Twin1_Item5) +
CInt(Inventory_Twin1_Item6) +
CInt(Inventory_Twin1_Item7) +
CInt(Inventory_Twin1_Item8) +
CInt(Inventory_Twin1_Item9) +
CInt(Inventory_Twin1_Item10) +
CInt(Inventory_Twin1_Item11) +
CInt(Inventory_Twin1_Item12) +
CInt(Inventory_Twin1_Item13) +
CInt(Inventory_Twin1_Item14) +
CInt(Inventory_Twin1_Item15) +
CInt(Inventory_Twin1_Item16) +
CInt(Inventory_Twin1_Item17) +
CInt(Inventory_Twin1_Item18) 
AS ADHD_Combined_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item19) +
CInt(Inventory_Twin1_Item20) +
CInt(Inventory_Twin1_Item21) +
CInt(Inventory_Twin1_Item22) +
CInt(Inventory_Twin1_Item23) +
CInt(Inventory_Twin1_Item24) +
CInt(Inventory_Twin1_Item25) +
CInt(Inventory_Twin1_Item26) +
CInt(Inventory_Twin1_Item27) +
CInt(Inventory_Twin1_Item28) +
CInt(Inventory_Twin1_Item29) +
CInt(Inventory_Twin1_Item30) +
CInt(Inventory_Twin1_Item31) +
CInt(Inventory_Twin1_Item32) +
CInt(Inventory_Twin1_Item33) 
AS Conduct_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item39) +
CInt(Inventory_Twin1_Item40) +
CInt(Inventory_Twin1_Item41) +
CInt(Inventory_Twin1_Item42) +
CInt(Inventory_Twin1_Item43) +
CInt(Inventory_Twin1_Item44) +
CInt(Inventory_Twin1_Item45) +
CInt(Inventory_Twin1_Item46) 
AS ODD_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item2) +
CInt(Inventory_Twin1_Item47) +
CInt(Inventory_Twin1_Item48) +
CInt(Inventory_Twin1_Item49) +
CInt(Inventory_Twin1_Item50) +
CInt(Inventory_Twin1_Item51) +
CInt(Inventory_Twin1_Item52) +
CInt(Inventory_Twin1_Item88) 
AS GeneralizedAnxiety_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item65) +
CInt(Inventory_Twin1_Item66) +
CInt(Inventory_Twin1_Item68) +
CInt(Inventory_Twin1_Item69) +
CInt(Inventory_Twin1_Item71) 
AS SeparationAnxiety_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item76) +
CInt(Inventory_Twin1_Item77) +
CInt(Inventory_Twin1_Item78) +
CInt(Inventory_Twin1_Item79) +
CInt(Inventory_Twin1_Item80) +
CInt(Inventory_Twin1_Item81) 
AS Schizophrenia_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item50) +
CInt(Inventory_Twin1_Item52) +
CInt(Inventory_Twin1_Item84) +
CInt(Inventory_Twin1_Item85) +
CInt(Inventory_Twin1_Item86) +
CInt(Inventory_Twin1_Item87) +
CInt(Inventory_Twin1_Item88) +
CInt(Inventory_Twin1_Item91) +
CInt(Inventory_Twin1_Item92) +
CInt(Inventory_Twin1_Item103) +
CInt(Inventory_Twin1_Item108) 
AS MDD_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item50) +
CInt(Inventory_Twin1_Item52) +
CInt(Inventory_Twin1_Item84) +
CInt(Inventory_Twin1_Item88) +
CInt(Inventory_Twin1_Item89) +
CInt(Inventory_Twin1_Item90) +
CInt(Inventory_Twin1_Item91) +
CInt(Inventory_Twin1_Item92) +
CInt(Inventory_Twin1_Item103) +
CInt(Inventory_Twin1_Item108) 
AS Dysthymia_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item50) +
CInt(Inventory_Twin1_Item98) +
CInt(Inventory_Twin1_Item100) +
CInt(Inventory_Twin1_Item101) +
CInt(Inventory_Twin1_Item102) +
CInt(Inventory_Twin1_Item103) +
CInt(Inventory_Twin1_Item104) +
CInt(Inventory_Twin1_Item105) +
CInt(Inventory_Twin1_Item106) 
AS Bipolar_SeveritySum_Twin1,

CInt(Inventory_Twin1_Item108) +
CInt(Inventory_Twin1_Item109) +
CInt(Inventory_Twin1_Item110) +
CInt(Inventory_Twin1_Item111) +
CInt(Inventory_Twin1_Item112) +
CInt(Inventory_Twin1_Item113) +
CInt(Inventory_Twin1_Item114) 
AS EatingProblems_SeveritySum_Twin1,

Abs((CInt(Inventory_Twin1_Item121) - CInt(Inventory_Twin1_Item40))) +
Abs((CInt(Inventory_Twin1_Item122) - CInt(Inventory_Twin1_Item51))) +
Abs((CInt(Inventory_Twin1_Item123) - CInt(Inventory_Twin1_Item44))) +
Abs((CInt(Inventory_Twin1_Item124) - CInt(Inventory_Twin1_Item64))) +
Abs((CInt(Inventory_Twin1_Item125) - CInt(Inventory_Twin1_Item88))) +
Abs((CInt(Inventory_Twin1_Item126) - CInt(Inventory_Twin1_Item19))) +
Abs((CInt(Inventory_Twin1_Item127) - CInt(Inventory_Twin1_Item103))) +
Abs((CInt(Inventory_Twin1_Item128) - CInt(Inventory_Twin1_Item108))) 
AS Inconsistency_SeveritySum_Twin1

FROM Inventory_Twin1;

And the one that matches the T-score looks like this:

SELECT FamilyID,
a.TScore AS ADHD_Inattentive_TScore_Twin1,
b.TScore AS ADHD_HyperImpul_TScore_Twin1,
c.TScore AS ADHD_Combined_TScore_Twin1,
d.TScore AS ODD_TScore_Twin1,
e.TScore AS Schizophrenia_TScore_Twin1,
f.TScore AS Dysthymia_TScore_Twin1,
g.TScore AS Bipolar_TScore_Twin1,
h.TScore AS Inconsistency_TScore_Twin1,
i.TScore AS Conduct_TScore_Twin1,
j.TScore AS GeneralizedAnxiety_TScore_Twin1,
k.TScore AS SeparationAnxiety_TScore_Twin1,
l.TScore AS MDD_TScore_Twin1,
m.TScore AS EatingProblems_TScore_Twin1
	
FROM Scoring_Inventory_SymptomSeveritySums, ScoringTScoresInventory a, ScoringTScoresInventory b, ScoringTScoresInventory c, ScoringTScoresInventory d, ScoringTScoresInventory e, ScoringTScoresInventory f, ScoringTScoresInventory g, ScoringTScoresInventory h, ScoringTScoresInventory i, ScoringTScoresInventory j, ScoringTScoresInventory k, ScoringTScoresInventory l, ScoringTScoresInventory m

WHERE 
a.Category = 'ADHDInatt' AND a.MinValue <= ADHD_Inattentive_SeveritySum_Twin1 AND a.MaxValue > ADHD_Inattentive_SeveritySum_Twin1 AND
b.Category = 'ADHDHyper' AND b.MinValue <= ADHD_HyperImpul_SeveritySum_Twin1 AND b.MaxValue > ADHD_HyperImpul_SeveritySum_Twin1 AND
c.Category = 'ADHDCom' AND c.MinValue <= ADHD_Combined_SeveritySum_Twin1 AND c.MaxValue > ADHD_Combined_SeveritySum_Twin1 AND
d.Category = 'ODD' AND d.MinValue <= ODD_SeveritySum_Twin1 AND d.MaxValue > ODD_SeveritySum_Twin1 AND
e.Category = 'Schizo' AND e.MinValue <= Schizophrenia_SeveritySum_Twin1 AND e.MaxValue > Schizophrenia_SeveritySum_Twin1 AND
f.Category = 'Dysthymia' AND f.MinValue <= Dysthymia_SeveritySum_Twin1 AND f.MaxValue > Dysthymia_SeveritySum_Twin1 AND
g.Category = 'Bipolar' AND g.MinValue <= Bipolar_SeveritySum_Twin1 AND g.MaxValue > Bipolar_SeveritySum_Twin1 AND
h.Category = 'Inconistent' AND h.MinValue <= Inconsistency_SeveritySum_Twin1 AND h.MaxValue > Inconsistency_SeveritySum_Twin1 AND
i.Category = 'ConductM' AND i.MinValue <= Conduct_SeveritySum_Twin1 AND i.MaxValue > Conduct_SeveritySum_Twin1 AND
j.Category = 'GenAnxM' AND j.MinValue <= GeneralizedAnxiety_SeveritySum_Twin1 AND j.MaxValue > GeneralizedAnxiety_SeveritySum_Twin1 AND
k.Category = 'SepAnxM' AND k.MinValue <= SeparationAnxiety_SeveritySum_Twin1 AND k.MaxValue > SeparationAnxiety_SeveritySum_Twin1 AND
l.Category = 'MDDM' AND l.MinValue <= MDD_SeveritySum_Twin1 AND l.MaxValue > MDD_SeveritySum_Twin1 AND
m.Category = 'EatingM' AND m.MinValue <= EatingProblems_SeveritySum_Twin1 AND m.MaxValue > EatingProblems_SeveritySum_Twin1;

As per MajP's suggestion, the first three categories of the ScoringTscoresInventory table look like this (it's too long to put it all up):

Category	TScore	MinValue	MaxValue
ADHDInatt	78	21	100
ADHDInatt	76	20	21
ADHDInatt	74	19	20
ADHDInatt	70	18	19
ADHDInatt	68	17	18
ADHDInatt	66	16	17
ADHDInatt	64	15	16
ADHDInatt	62	14	15
ADHDInatt	58	13	14
ADHDInatt	56	12	13
ADHDInatt	54	11	12
ADHDInatt	52	10	11
ADHDInatt	50	0	10
ADHDHyper	78	21	100
ADHDHyper	76	20	21
ADHDHyper	72	19	20
ADHDHyper	70	18	19
ADHDHyper	68	17	18
ADHDHyper	66	16	17
ADHDHyper	64	15	16
ADHDHyper	62	14	15
ADHDHyper	60	13	14
ADHDHyper	58	12	13
ADHDHyper	56	11	12
ADHDHyper	54	10	11
ADHDHyper	50	0	10
ADHDCom		78	39	100
ADHDCom		76	37	39
ADHDCom		74	36	37
ADHDCom		72	34	36
ADHDCom		70	33	34
ADHDCom		68	31	33
ADHDCom		66	30	31
ADHDCom		64	28	30
ADHDCom		62	27	28
ADHDCom		60	25	27
ADHDCom		58	24	25
ADHDCom		56	22	24
ADHDCom		54	21	22
ADHDCom		52	19	21
ADHDCom		50	0	19
...

And yet I get a really bizarre result, where only 4 families come up (there's 150 in the database), and they each come up four times (I've changed the FamilyIDs for privacy reasons, they aren't sequential in reality, and I've truncated the names of the columns):

FamilyID  ADHD_Inat  ADHD_Hyper  ADHD_Comb  ODD  Schizo  Dysth  Bipolar  Incon  Conduct  General  Separ  MDD  Eating
00001	66	54	60	56	50	52	50	54	50	56	62	58	50
00001	66	54	60	50	50	52	50	54	50	56	62	58	50
00001	66	54	60	56	50	52	50	54	50	50	62	58	50
00001	66	54	60	50	50	52	50	54	50	50	62	58	50
00002	58	66	64	60	60	52	60	50	50	56	68	54	62
00002	58	66	64	50	60	52	60	50	50	56	68	54	62
00002	58	66	64	60	60	52	60	50	50	50	68	54	62
00002	58	66	64	50	60	52	60	50	50	50	68	54	62
00003	58	56	58	68	52	64	58	54	50	66	50	60	50
00003	58	56	58	50	52	64	58	54	50	66	50	60	50
00003	58	56	58	68	52	64	58	54	50	50	50	60	50
00003	58	56	58	50	52	64	58	54	50	50	50	60	50
00004	68	58	64	68	60	56	58	50	54	52	50	58	78
00004	68	58	64	68	60	56	58	50	54	52	50	58	50
00004	68	58	64	50	60	56	58	50	54	52	50	58	78
00004	68	58	64	50	60	56	58	50	54	52	50	58	50

The query, when I use the Switch() statements works fine, though.

Any thoughts?

Alex

 

[MajP]

As far as I can tell that query is not going to work. Not to be a parrot, but since your data is not normalized you are stuck trying this over the top complicated query.

That is why I recommend the function

Your query would simply be
Select
FamilyID,
getTScore("ADHDInatt", ADHD_Inattentive_SeveritySum_Twin1) as ADHD_Inattentive_TScore_Twin,
.....
getTScore("EatingM", EatingProblems_TScore_Twin1) as ADHD_Inattentive_TScore_Twin1

 

[alevol]

Thanks a lot MajP

I had tried using the VB code before, but it hadn't worked, and since I don't know VB wasn't able to figure it out.

I can see from what you posted tonight that there were extra quotation marks in the second parameter of the getTscore function in your original post, and removing those makes it work perfectly.

Thanks again!

Alex

 

[MajP]

If you want to do away with the function, you would need to get your data into a query like this

FamilyID Category sumValue
example
fam1 ADHT 72

You would do this using a union query

SELECT FamilyID,
CInt(Inventory_Twin1_Item1) +
CInt(Inventory_Twin1_Item2) +
CInt(Inventory_Twin1_Item3) +
CInt(Inventory_Twin1_Item4) +
CInt(Inventory_Twin1_Item5) +
CInt(Inventory_Twin1_Item6) +
CInt(Inventory_Twin1_Item7) +
CInt(Inventory_Twin1_Item8) +
CInt(Inventory_Twin1_Item9)
AS SumValue,
"ADHTINatt" as Category
from inventory_twin1
UNION
CInt(Inventory_Twin1_Item10) +
CInt(Inventory_Twin1_Item11) +
CInt(Inventory_Twin1_Item12) +
CInt(Inventory_Twin1_Item13) +
CInt(Inventory_Twin1_Item14) +
CInt(Inventory_Twin1_Item15) +
CInt(Inventory_Twin1_Item16) +
CInt(Inventory_Twin1_Item17) +
CInt(Inventory_Twin1_Item18)
AS ADHD_SumValue,
"HyperInAtt" as Category,
from inventory_twin1
.......

Once you build that query you can join it to the Tscore table
where category = tblTScores.category and SumValue > tblTscore.minVlaue and sumValue < tblTscore.maxvalue

the answer would look like
FamilyID Category SumValue Tscore

 

[MajP]

BTW, that union query is often called a normalizing query because the result is a more normalized structure. The result is more how the data should have been originally organized.