is there a transpose query

[Guest]

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

 

[Guest]

As far as I am aware, no. What are you trying to do?

 

[Guest]

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.

 

[Dirk Goldgar]

In

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

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.

 

[Guest]

PERFECT! Thank you!

In

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

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)