Transposing from normalized database to "denormalized" version

[Tim]

(posted as reply to an earlier message... don't think
anyone will see it there...)

My project manager's goal is take a normalized table and
break it down, to one row per study subject and egads of
variables across, to fit in with a 1980's version of the
data for long-term analyses.

My problem is that the variable "ActivityId" has 20
entries. I have transposed the 12 or so that I want
across as columns, but for some reason i still have 20
rows and the variables (iif True False statement) fill in
a nice diagonal pattern down the datasheet. When I
aggregate (the obvious answer), I receive an MSJet error
message about a SQL Transform statement, but that's where
my Access knowledge stops... any suggestions?

Visually:

:what I have:
ID var1 var2 var3 etc.
12 1 0 0
12 0 1 0
12 0 0 1

:what I want:
ID var1 var2 var3 etc.
12 1 1 1
13 0 1 0

please reply to me via email if you have anything useful
(and to the boards, as expected)!

Thanks,

Tim

 

[Tim]

Here it is, as req. by Jon. I took out the other 10
variables for they would only muddy it up.

Error Message:
"You tried to execute a query that does not include the
specified expression 'IIf(Not IIf([ActivityHistory].
[activityid]=1,True,0)=0,[PERIODSTARTYEAR],Null)' as part
of an aggregate function."

I don't know where I would include that... I've made
several attempts on the design grid but I'm probably just
missing something about it.

My SQL code:

SELECT Sticker.StickerId, IIf([ActivityHistory].
[ActivityId]=1,True,False) AS AUTO, IIf([AUTO]=True,
[PERIODSTARTYEAR],Null) AS AUTOFR1, IIf([AUTO]=True,
[PERIODENDYEAR],Null) AS AUTOTO1, IIf([AUTO]=True,
[FRACTIONOFYEAR],Null) AS AUTOEXP1, IIf([activityhistory].
[activityid]=2,True,False) AS BOIL, IIf([BOIL]=True,
[PERIODSTARTYEAR],Null) AS BOILFR1, IIf([BOIL]=True,
[PERIODENDYEAR],Null) AS BOILTO1, IIf([BOIL]=True,
[FRACTIONOFYEAR],Null) AS BOILEXP1
FROM ((Activity INNER JOIN ActivityHistory ON
Activity.ActivityId = ActivityHistory.ActivityId) INNER
JOIN (Participant INNER JOIN Sticker ON
Participant.ParticipantId = Sticker.ParticipantId) ON
ActivityHistory.StickerId = Sticker.StickerId) INNER JOIN
ExposureType AS ExposureType_1 ON
ActivityHistory.ExposureTypeId =
ExposureType_1.ExposureTypeId
GROUP BY Sticker.StickerId, Activity.ActivityId, IIf
([ActivityHistory].[ActivityId]=1,True,False), IIf([AUTO]
=True,[PERIODSTARTYEAR],Null), IIf([AUTO]=True,
[PERIODENDYEAR],Null), IIf([AUTO]=True,
[FRACTIONOFYEAR],Null), IIf([activityhistory].[activityid]
=2,True,False), IIf([BOIL]=True,[PERIODSTARTYEAR],Null),
IIf([BOIL]=True,[PERIODENDYEAR],Null), IIf([BOIL]=True,
[FRACTIONOFYEAR],Null);

 

[Andrew Smith]

Try a crosstab query.

 

[John Vinson]

Error Message:
"You tried to execute a query that does not include the
specified expression 'IIf(Not IIf([ActivityHistory].
[activityid]=1,True,0)=0,[PERIODSTARTYEAR],Null)' as part
of an aggregate function."

Ummm... it seems I'm missing something. Your query does not contain
any aggreagate functions such as Count(), Sum(), Avg()... why are you
using a totals query AT ALL? That's why you're getting an error - you
have this expression in the SELECT portion of the query but not in its
GROUP BY.

 

[Tim]

John -

This is my point of confusion! I'm trying to not have 20
rows per "StickerId". Using Null, False, and 0 do not
work for the blank match, so aggregating seemed to be the
best fit. I've tried other ideas, such as a "pre-query"
to spit out just 1 row with StickerId and ActivityId, but
it became too bulky and ate too many non-displayed
variable slots, filling my 255 allotment.

At least i now know aggregating is not the answer... other
ideas?

Thanks -

Tim

-----Original Message-----

Error Message:
"You tried to execute a query that does not include the
specified expression 'IIf(Not IIf([ActivityHistory].
[activityid]=1,True,0)=0,[PERIODSTARTYEAR],Null)' as part
of an aggregate function."

Ummm... it seems I'm missing something. Your query does not contain
any aggreagate functions such as Count(), Sum(), Avg()... why are you
using a totals query AT ALL? That's why you're getting an error - you
have this expression in the SELECT portion of the query but not in its
GROUP BY.


.

 

[John Vinson]

At least i now know aggregating is not the answer... other
ideas?

As Andrew and I both suggested - try a Crosstab query. Use the Query
Wizard.