Single record from multiple records on same table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Looks like a crosstab query will do what you want. You will need to modify
the column names for your purpose.
 
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
 
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...
 
Back
Top