Consolidating records

[Brian]

I have a table of client records showing an individual record for each
purchase a client has made. How can I consolidate these records and
create a query in which I include each purchase as a separate field in
one client record?

 

[John Vinson]

I have a table of client records showing an individual record for each
purchase a client has made. How can I consolidate these records and
create a query in which I include each purchase as a separate field in
one client record?

Why on Earth would you want to? That's a grossly non-normalized table
design. And what will you do with that star customer who has made 837
purchases?

Create a Crosstab query instead, using the purchases as the Column
Header.

 

[Tim]

I have the same sort of a problem: an old data set for a
study I'm on is "grossly non-normalized", and I'm trying
to denormalize a much better version, for some long-term
analyses...

My problem is that the variable ActivityId has 20 entries,
and for each study subject I can't transpose across the 20
rows into columns, without for some reason keeping the 20
rows anyway. 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?

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

 

[John Vinson]

My problem is that the variable ActivityId has 20 entries,
and for each study subject I can't transpose across the 20
rows into columns, without for some reason keeping the 20
rows anyway. 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?

A Crosstab query - or perhaps a MakeTable query based on a Crosstab
query - would seem to be the solution here. If you'ld post the SQL of
your query and the text of the error message someone may be able to
come up with a fix.