Group By problem

[Red via AccessMonster.com]

Hi everyone,
I am trying to create a query that will pull in multiple training dates.
What I need to do is group by SSAN the training dates for an individual. For
example, instead of having the query show all training dates for lifesaving,
then CPR, then life skills by SSAN, I would like it to show by SSAN the
training dates. I need the query to report the results in record form vs
colum form if possible.
CPR lifesaving
Life Skills
000-00-0001 15 May 2006 15 may 2006 15 May
2006
000-00-0002 10 May 2006 10 May 2006 10 May
2006
000-00-0003 12 May 2006 12 May 2006 12 May
2006
000-00-0004 13 May 2006 13 May 2006 13 May
2006

ETC......

I hope this makes sense. I will include my SQL for your reference. Any help
to show me how to group and align would be greatly appreciated.

VR,

Red

SELECT FormalTngDates.SSAN, FormalTngDates.TrngDate, FormalTraining.FormalTng
FROM FormalTraining LEFT JOIN FormalTngDates ON FormalTraining.FormalTngID =
FormalTngDates.FormalTngID
GROUP BY FormalTngDates.SSAN, FormalTngDates.TrngDate, FormalTraining.
FormalTng, FormalTngDates.FormalTngID;

 

[John Spencer]

Try a crosstab query. In the query grid.

SSAN Group by Row Heading
Formal Training GroupBY Column Heading
TrngDate FIRST Value

I think the SQL would look like:
TRANSFORM First(TrngDate) as TrainDate
SELECT FormalTngDates.SSAN
FROM FormalTraining LEFT JOIN FormalTngDates ON FormalTraining.FormalTngID =
FormalTngDates.FormalTngID
GROUP BY FormalTngDates.SSAN
PIVOT FormalTraining

 

[Guest]

You need a crosstab query.

 

[Red via AccessMonster.com]

Thanks John, Worked like a charm. Just saved me many hours of cutting and
pasting :>) it is appreciated.

VR,

Red

Try a crosstab query. In the query grid.

SSAN Group by Row Heading
Formal Training GroupBY Column Heading
TrngDate FIRST Value

I think the SQL would look like:
TRANSFORM First(TrngDate) as TrainDate
SELECT FormalTngDates.SSAN
FROM FormalTraining LEFT JOIN FormalTngDates ON FormalTraining.FormalTngID =
FormalTngDates.FormalTngID
GROUP BY FormalTngDates.SSAN
PIVOT FormalTraining

Hi everyone,
I am trying to create a query that will pull in multiple training dates.

[quoted text clipped - 33 lines]

GROUP BY FormalTngDates.SSAN, FormalTngDates.TrngDate, FormalTraining.
FormalTng, FormalTngDates.FormalTngID;

 

[Red via AccessMonster.com]

Thanks Karl, Worked like a charm. Just saved me many hours of cutting and
pasting :>) it is appreciated.

VR,

Red

You need a crosstab query.

Hi everyone,
I am trying to create a query that will pull in multiple training dates.

[quoted text clipped - 28 lines]

GROUP BY FormalTngDates.SSAN, FormalTngDates.TrngDate, FormalTraining.
FormalTng, FormalTngDates.FormalTngID;