newbie needs help, what is this called

[hollyh]

i am not sure how to describe this so bare with me

basically i have a VISIT TABLE
with

VisitID
PatientID
VisitType (1st Trimester, 2ndTrimester, etc)
and then columns pertaining to the visit

We are trying to do a longitudinal study where we compare each visit type data to itself to be exported to excel so it would look like this all in one column based on patientID

PATIENTID | 1st Trimester | data | 2nd Trimester | data |

is there a way to query this so the visit types and data are all in the same row based on patient id

 

[Guus2005]

Re: newbie needs help, what is thsi called

select patientid, iif(visittype = "1st trimester",[data],"") as "1st trimester", iif(visittype = "2nd trimester",[data],"") as "2nd trimester" from table1

or something similar.
hope i am getting my point accross.

HTH

 

[hollyh]

Re: newbie needs help, what is thsi called

wow, thanks!

question tho, there's like 10 different fields so itll be more like


PATIENTID | 1st Trimester | data1 | data2... data10
| 2nd Trimester | data1 | data2...data10

so for each data. i guess i should say field, i'd have to use a separate iif statement, right?

 

[Taruz]

Re: newbie needs help, what is thsi called

Hi..

Cross query might be useful maybe..:

transform min(data) as dt
select PatientID
from table_name
group by PatientID
pivot VisitType

 

[Guus2005]

i would say that it can be done the way you are suggesting however there must be an easier and more dynamic way to accomplish this. i was thinking on a crosstab query. but to know for sure i need a sample of your data.

what does the data in your table look like?

is it something like this:
visitid, patientid, visittype, data1, data2,...
1, 1, 1, x, y
2, 1, 1, x2, y2
3, 2, 1, x3, y3

 

[hollyh]

alright I need to look up what a crosstab query is. i did not design this database myself and i cannot get access to it at the moment so bare with me


most of the data is MedicationType (1 for yes and 2 for no 77 null)

so the visitTypes don't repeat for each Patientid, if that makes sense..

 

[hollyh]

actually let me redo this

visitid, patientid, visittype, data1, data2,...


1, 1, 1, 1, 2
2, 1, 2, 77, 1
3, 1, 3(rdTrimester), 1, 1
4, 2, 1(stTrimester), 1, 2

 

[hollyh]

okay so I think im understanding crosstab a little

i will post when i get the actual db in front of me but the GROUPBY function makes me nervous. should i just select MIN? does this matter? is it going to alter the output? i can't seem to do it with more than one dataset in the query grid... does this mean i need to use sql?thanks!

 

[Guus2005]

your table with the fields: visitid, patientid, visittype, data1, data2,...
is it true that the combination patientid and visittype is unique?
is it true that visitid is unique?

it must be, because otherwise your question doesn't make any sense.

this is the way you want to display the result?
PATIENTID | 1st Trimester | data1 | data2... data10 | 2nd Trimester | data1 | data2...data10

looking at your table you almost have it. it would be easier if you had a sample database...

here goes:

select patientid, "1st trimester" as first, iif(visittype = "1st trimester",[data1],"") as "Data1",
              iif(visittype = "1st trimester",[data2],"") as "Data2",
              iif(visittype = "1st trimester",[data3],"") as "Data3",
              iif(visittype = "1st trimester",[data4],"") as "Data4",
              iif(visittype = "1st trimester",[data5],"") as "Data5","2nd trimester" as second
              iif(visittype = "2nd trimester",[data1],"") as "Data1",
              iif(visittype = "2nd trimester",[data2],"") as "Data2",
              iif(visittype = "2nd trimester",[data3],"") as "Data3",
              iif(visittype = "2nd trimester",[data4],"") as "Data4",
              iif(visittype = "2nd trimester",[data5],"") as "Data5"
 from table1

you will get a result which looks like this:

1, 1st trimester, a, b, c, d, e, 2nd trimester,  ,  ,  ,  ,
1, 1st trimester,  ,   ,   ,  ,   , 2nd trimester, a, b, c, d, e
2, 1st trimester, a, b, c, d, e, 2nd trimester,  ,  ,  ,  ,
2, 1st trimester,  ,   ,   ,  ,   , 2nd trimester, a, b, c, d, e

you will have to "group by" the query you just created.
it's best if you store the query: qryPart1, and use this query to "group by".

like this:

select min(patientid), min(first), min(data1), min(data2), ..., min(second), ... from table1

i think you get the picture.

i am using min(). i also could be using max(). that doesnot matter because there is only one record that is grouped by per patientid per trimester.

this will be the endresult:

1, 1st trimester, a, b, c, d, e, 2nd trimester, a, b, c, d, e
2, 1st trimester, a, b, c, d, e, 2nd trimester, a, b, c, d, e

enjoy!

 

[hollyh]

Wow, okay, neat. I have never queried a query before.

I made a dummy version to play around with and this works. Thanks so much, I would've never figured this out on my own

 

[hollyh]

Now I"m getting this error:

the select argument includes a reserved word or an argument that is misspelled/missing or the punctuation is incorrect

So here is my sql, does anything jump out? I've been staring at it for 20 minutes

SELECT test.patientid,
"PrePregMed" AS PrePregMed,
IIf([visit type]="Pre Preg",[Beta Blocker],"") AS "Beta Blocker_0",
IIf([visit type]="Pre Preg",[Diuretic],"") AS "Diuretic_0",
IIf([visit type]="Pre Preg",[Antcoagulation],"") AS "Antcoagulation_0",
IIf([visit type]="Pre Preg",[AceArb],"") AS "Ace/Arb_0",
IIf([visit type]="Pre Preg",[Other],"") AS "Other_0",
IIf([visit type]="Pre Preg",[Antiarrthymic,"") AS "Antiarrthymic_0",
"1stTrimesterMed" AS 1stTrimesterMed,
IIf([visit type]="First Trimester",[Beta Blocker],"") AS "Beta Blocker_1",
IIf([visit type]="First Trimester",[Diuretic],"") AS "Diuretic_1",
IIf([visit type]="First Trimester",[Antcoagulation],"") AS "Antcoagulation_1",
IIf([visit type]="First Trimester",[AceArb],"") AS "Ace/Arb_1",
IIf([visit type]="First Trimester",[Other],"") AS "Other_1",
IIf([visit type]="First Trimester",[Antiarrthymic],"") AS "Antiarrthymic_1",
"2ndTrimesterMed" AS 2ndTrimesterMed,
IIf([visit type]="Second Trimester",[Beta Blocker],"") AS "Beta Blocker_2",
IIf([visit type]="Second Trimester",[Diuretic],"") AS "Diuretic_2",
IIf([visit type]="Second Trimester",[Antcoagulation],"") AS "Antcoagulation_2",
IIf([visit type]="Second Trimester",[AceArb],"") AS "Ace/Arb_2",
IIf([visit type]="Second Trimester",[Other],"") AS "Other_2",
IIf([visit type]="Second Trimester",[Antiarrthymic],"") AS "Antiarrthymic_2",
"3rdTrimesterMed" AS 3rdTrimesterMed,
IIf([visit type]="Third Trimester",[Beta Blocker],"") AS "Beta Blocker_3",
IIf([visit type]="Third Trimester",[Diuretic],"") AS "Diuretic_3",
IIf([visit type]="Third Trimester",[Antcoagulation],"") AS "Antcoagulation_3",
IIf([visit type]="Third Trimester",[AceArb],"") AS "Ace/Arb_3",
IIf([visit type]="Third Trimester",[Other],"") AS "Other_3",
IIf([visit type]="Third Trimester",[Antiarrthymic],"") AS "Antiarrthymic_3",
"PPMed" AS PPMed,
IIf([visit type]="PP",[Beta Blocker],"") AS "Beta Blocker_4",
IIf([visit type]="PP",[Diuretic],"") AS "Diuretic_4",
IIf([visit type]="PP",[Antcoagulation],"") AS "Antcoagulation_4",
IIf([visit type]="PP",[AceArb],"") AS "Ace/Arb_4",
IIf([visit type]="PP",[Other],"") AS "Other_4",
IIf([visit type]="PP",[Antiarrthymic],"") AS "Antiarrthymic_4",
FROM test;

 

[Guus2005]

Now I"m getting this error:

the select argument includes a reserved word or an argument that is misspelled/missing or the punctuation is incorrect

So here is my sql, does anything jump out? I've been staring at it for 20 minutes

SELECT test.patientid,
"PrePregMed" AS PrePregMed,
IIf([visit type]="Pre Preg",[Beta Blocker],"") AS "Beta Blocker_0",
IIf([visit type]="Pre Preg",[Diuretic],"") AS "Diuretic_0",
IIf([visit type]="Pre Preg",[Antcoagulation],"") AS "Antcoagulation_0",
IIf([visit type]="Pre Preg",[AceArb],"") AS "Ace/Arb_0",
IIf([visit type]="Pre Preg",[Other],"") AS "Other_0",
IIf([visit type]="Pre Preg",[Antiarrthymic,"") AS "Antiarrthymic_0",
"1stTrimesterMed" AS 1stTrimesterMed,
IIf([visit type]="First Trimester",[Beta Blocker],"") AS "Beta Blocker_1",
IIf([visit type]="First Trimester",[Diuretic],"") AS "Diuretic_1",
IIf([visit type]="First Trimester",[Antcoagulation],"") AS "Antcoagulation_1",
IIf([visit type]="First Trimester",[AceArb],"") AS "Ace/Arb_1",
IIf([visit type]="First Trimester",[Other],"") AS "Other_1",
IIf([visit type]="First Trimester",[Antiarrthymic],"") AS "Antiarrthymic_1",
"2ndTrimesterMed" AS 2ndTrimesterMed,
IIf([visit type]="Second Trimester",[Beta Blocker],"") AS "Beta Blocker_2",
IIf([visit type]="Second Trimester",[Diuretic],"") AS "Diuretic_2",
IIf([visit type]="Second Trimester",[Antcoagulation],"") AS "Antcoagulation_2",
IIf([visit type]="Second Trimester",[AceArb],"") AS "Ace/Arb_2",
IIf([visit type]="Second Trimester",[Other],"") AS "Other_2",
IIf([visit type]="Second Trimester",[Antiarrthymic],"") AS "Antiarrthymic_2",
"3rdTrimesterMed" AS 3rdTrimesterMed,
IIf([visit type]="Third Trimester",[Beta Blocker],"") AS "Beta Blocker_3",
IIf([visit type]="Third Trimester",[Diuretic],"") AS "Diuretic_3",
IIf([visit type]="Third Trimester",[Antcoagulation],"") AS "Antcoagulation_3",
IIf([visit type]="Third Trimester",[AceArb],"") AS "Ace/Arb_3",
IIf([visit type]="Third Trimester",[Other],"") AS "Other_3",
IIf([visit type]="Third Trimester",[Antiarrthymic],"") AS "Antiarrthymic_3",
"PPMed" AS PPMed,
IIf([visit type]="PP",[Beta Blocker],"") AS "Beta Blocker_4",
IIf([visit type]="PP",[Diuretic],"") AS "Diuretic_4",
IIf([visit type]="PP",[Antcoagulation],"") AS "Antcoagulation_4",
IIf([visit type]="PP",[AceArb],"") AS "Ace/Arb_4",
IIf([visit type]="PP",[Other],"") AS "Other_4",
IIf([visit type]="PP",[Antiarrthymic],"") AS "Antiarrthymic_4"[COLOR="Red"],<<<[/COLOR]

FROM test;

remove the comma before FROM test

looking at your query i am certain it could be done differently.
IIF statements are very slow!

 

[hollyh]

Thanks

Is a crosstab query better?

 

[hollyh]

well after correcting the comma, i'm getting "wrong number of arguments" error

so now I am trying this

allenbrowne dot com ser-67.html

altho I have no idea what I'm doing, keep your fingers crossed

 

[Guus2005]

in your case a crosstab query is not better. a crosstab query is very confusing if you don't know what you are doing.

eventough allenbrowne.com/ser-67.html is a good source to start from, it is better if you fix the syntax error in your query.
apparently it is an error because you didn't build it to accept parameters, did you?

it is a select query and they are the easiest to debug.

take a small part of the query and run it.

SELECT test.patientid,
"PrePregMed" AS PrePregMed,
IIf([visit type]="Pre Preg",[Beta Blocker],"") AS "Beta Blocker_0",
IIf([visit type]="Pre Preg",[Diuretic],"") AS "Diuretic_0",
IIf([visit type]="Pre Preg",[Antcoagulation],"") AS "Antcoagulation_0",
IIf([visit type]="Pre Preg",[AceArb],"") AS "Ace/Arb_0",
IIf([visit type]="Pre Preg",[Other],"") AS "Other_0",
IIf([visit type]="Pre Preg",[Antiarrthymic,"") AS "Antiarrthymic_0"

from test

if that works, add a few lines until the error message presents itself.
when it does, there's your syntax error.

hth:d

 

[hollyh]

oh man ONE COMMA is all it takes the mess the whole thing up

my poor wrist

 

[hollyh]

in the actual db it's showing repeating IDs with this query when it shouldnt be, hrmmmmm

 

[Guus2005]

oh man ONE COMMA is all it takes the mess the whole thing up

my poor wrist

it's a sad thing that computers still do what you tell them to do, not what you want them to do.

 

[hollyh]

so it turns out there are some drugs that need to be lumped together to create a new value

for example

Diuretic: IIf(([Diuretic1]=True Or [Diuretic2]=True),1,0)

I made a bunch of subqueries where the criteria is 1st Trimester, 2nd etc..

and I am trying now to just join all the subqueries in a big query but when I do the values are all wrong. Is this just not possible?

 

[Guus2005]

to save some time typing: this is the same:

Diuretic: IIf(([Diuretic1] Or [Diuretic2]),1,0)

testing if a value equals true is the same as testing the value.

using all the subqueries into the final query is the way to go.
can't tell you why it doesn't work. i need more data to make an assumption.

please post a sample database.