is there a transpose query

B

Bill

Hi, my data looks like this:
iss_id fld_title icf_value
1327 Next Actions Review with team and close ticket
1327 Co-Assignment -1
1327 Classification 8
1327 Target Completion Date 2007-01-21
1327 Follow-Up Date --

I would like to transpose it so that iss_id is the key and each fld_title
becomes its own field header:
iss_id Next Actions Co-Assignment Classification Target Completion
Date Follow-Up Date Next Actions
1327 Review with team and close ticket -1 8 2007-01-21 -- Follow up with
Datatel
 
B

Bill

With the issue number being the key to the table, I could more easily join
the information to other tables that have the issue number as the key without
having to restrict based on the values in the other fields. This is an
Eventum issue tracking database and it has some interesting methods of
setting up custom fields on the standard forms. The custom field data is
then stored in a table by issue number using issue-number, custom field id as
the primary key.
 
D

Dirk Goldgar

In
Bill said:
Hi, my data looks like this:
iss_id fld_title icf_value
1327 Next Actions Review with team and close ticket
1327 Co-Assignment -1
1327 Classification 8
1327 Target Completion Date 2007-01-21
1327 Follow-Up Date --

I would like to transpose it so that iss_id is the key and each
fld_title becomes its own field header:
iss_id Next Actions Co-Assignment Classification Target Completion
Date Follow-Up Date Next Actions
1327 Review with team and close ticket -1 8 2007-01-21 -- Follow up
with Datatel
Click to expand...

Will each fld_title value occur only once per iss_id? If so, you could
use a crosstab query like this:

TRANSFORM First(icf_value) AS FirstOficf_value
SELECT iss_id
FROM [YourTable]
GROUP BY iss_id
PIVOT fld_title;

If you need to force the order of the output columns, you can use the
query's Column Headings property to specify that.
 
B

Bill

PERFECT! Thank you!


Dirk Goldgar said:
In
Bill said:
Hi, my data looks like this:
iss_id fld_title icf_value
1327 Next Actions Review with team and close ticket
1327 Co-Assignment -1
1327 Classification 8
1327 Target Completion Date 2007-01-21
1327 Follow-Up Date --

I would like to transpose it so that iss_id is the key and each
fld_title becomes its own field header:
iss_id Next Actions Co-Assignment Classification Target Completion
Date Follow-Up Date Next Actions
1327 Review with team and close ticket -1 8 2007-01-21 -- Follow up
with Datatel
Click to expand...

Will each fld_title value occur only once per iss_id? If so, you could
use a crosstab query like this:

TRANSFORM First(icf_value) AS FirstOficf_value
SELECT iss_id
FROM [YourTable]
GROUP BY iss_id
PIVOT fld_title;

If you need to force the order of the output columns, you can use the
query's Column Headings property to specify that.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top