Single record from multiple records on same table

A

Ace9x

I have a need to condense multiple records (potentially maximum of 14) from a
table onto a single record for use in a report. I would like to create a
query that can be used in the report.
The fields that need to be condensed are Session and WCode, each may exist
from 0 to 14 times for any given scenario.
The query ideally would contain only the Wcode value as a numbered set from
1 to 14, relevant to the value of Session.

To clear up any ambiguity in my description belwo is a sample of what I am
attempting and failing to achieve.

Original

Name Year Period Session Wcode
---------------------------------------------
XXXX 2005 01 1 A
XXXX 2005 01 4 B
XXXX 2005 01 5 C
XXXX 2005 01 7 D

New query layout

Name Year Period Wcode1 Wcode2 Wcode3 Wcode4 Wcode5 Wcode6 Wcode7
XXXX 2005 01 A B C
D


Any help or guidance or constructive criticism of amy approach greatfully
received.

Cheers,
Steve
 
K

KARL DEWEY

Looks like a crosstab query will do what you want. You will need to modify
the column names for your purpose.
 
A

Ace9x

Have already used the crosstab query approach. Unfortunately to get the
correct results I end up with numbers in the column titles for the columns I
need as shown below :-

TRANSFORM First(qryAdj.WorkCode) AS FirstOfWCode
SELECT qryAdj.Name, qryAdj.InNo, qryWPPAdjustments.FinYear, qryAdj.PrNo,
First(qryAdj.WCode) AS [Total Of WCode]
FROM qryAdj
GROUP BY qryAdj.Name, qryAdj.InmateNo, qryAdj.FinYear, qryAdj.PrdNo
PIVOT qryAdj.Session;

Produces query with Name, InNo,FinYear,PrdNo,1,2,3,4,5 etc

When I then attempt to Select from the query I obviously don't get the
result I need.

How do I rename the numbers columns to get a valid column name I can then
Select in code within the report?

Cheers,
Steve
 
K

KARL DEWEY

Try this --
TRANSFORM First(qryAdj.WorkCode) AS FirstOfWCode
SELECT qryAdj.Name, qryAdj.InNo, qryWPPAdjustments.FinYear, qryAdj.PrNo,
First(qryAdj.WCode) AS [Total Of WCode]
FROM qryAdj
GROUP BY qryAdj.Name, qryAdj.InmateNo, qryAdj.FinYear, qryAdj.PrdNo
PIVOT "Wcode" & [qryAdj].[Session] AS X;


Ace9x said:
Have already used the crosstab query approach. Unfortunately to get the
correct results I end up with numbers in the column titles for the columns I
need as shown below :-

TRANSFORM First(qryAdj.WorkCode) AS FirstOfWCode
SELECT qryAdj.Name, qryAdj.InNo, qryWPPAdjustments.FinYear, qryAdj.PrNo,
First(qryAdj.WCode) AS [Total Of WCode]
FROM qryAdj
GROUP BY qryAdj.Name, qryAdj.InmateNo, qryAdj.FinYear, qryAdj.PrdNo
PIVOT qryAdj.Session;

Produces query with Name, InNo,FinYear,PrdNo,1,2,3,4,5 etc

When I then attempt to Select from the query I obviously don't get the
result I need.

How do I rename the numbers columns to get a valid column name I can then
Select in code within the report?

Cheers,
Steve


KARL DEWEY said:
Looks like a crosstab query will do what you want. You will need to modify
the column names for your purpose.
Click to expand...
Click to expand...
 
Top