Hi,

I have a table set up so that there are three columns: StudyID,
DrawDate, and Value.  StudyID and DrawDate are the primary key.  I
want to create a table from this one that has only one row for each
StudyID so that it would go from:

StudyID    DrawDate    Value

to

StudyID    DrawDate1    Value1    DrawDate2    Value2    DrawDate3
Value3  etc.

Is there a way to do this?

Thanks,

Elysia

"Elysia Larson" <elysia.larson@gmail.com> wrote in message 
news:832e952f-174f-489f-ab7a-e2189f660a92@f6g2000vbp.googlegroups.com...
> Hi,
>
> I have a table set up so that there are three columns: StudyID,
> DrawDate, and Value.  StudyID and DrawDate are the primary key.  I
> want to create a table from this one that has only one row for each
> StudyID so that it would go from:
>
> StudyID    DrawDate    Value
>
> to
>
> StudyID    DrawDate1    Value1    DrawDate2    Value2    DrawDate3
> Value3  etc.
>
> Is there a way to do this?
>
> Thanks,
>
> Elysia

How many DrawDate fields will be needed per record? You are denormalizing 
your data, and it will probably cause problems in the long run. If you have 
multiple DrawDates associated with a single StudyID, you need a one-to-many 
relationship between two tables to represent that relationship.
 

Elysia:

The easiest way to present the data in this format would be via a report with
a multi-column subreport within it.  You'll find a demo of this at:

http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=libraryMessages&webtag=ws-msdevapps&tid=24271


The report in the file lists club members horizontally per address.  In your
case the StudyID corresponds to the address, and the DrawDate and Value to my
FP and FirstName fields.  If you have another table with one row per StudyID
(which you should really have to ensure referential integrity)  you can base
the report on that.  If not you can base it on a query on the current table:

SELECT DISTINCT StudyID
FROM YourTable;

The subreport would be based on your current table.

The demo file includes two reports, one using a subreport, the other a single
report whose layout is manipulated in code at runtime.  The former should be
used as the model; the latter was only produced to show that it could be done
that way, in response to an enquiry by a reader of a magazine column written
by a contact of mine.  The subreport approach is far simpler.

Ken Sheridan
Stafford, England

Elysia Larson wrote:
>Hi,
>
>I have a table set up so that there are three columns: StudyID,
>DrawDate, and Value.  StudyID and DrawDate are the primary key.  I
>want to create a table from this one that has only one row for each
>StudyID so that it would go from:
>
>StudyID    DrawDate    Value
>
>to
>
>StudyID    DrawDate1    Value1    DrawDate2    Value2    DrawDate3
>Value3  etc.
>
>Is there a way to do this?
>
>Thanks,
>
>Elysia

-- 
Message posted via http://www.accessmonster.com