Transpose Data

[Ray67]

Hello



I have a report that looks like this

Patientname	ICD 1	ICD 2	ICD 3	ICD 4	ICD 5
John Doe	719.46	729	750
How can i transpose the data. Patient name Order of ICD Code John Doe ICD 1 719.46 John Doe ICD 2 729 John Doe ICD 3 750

[June7]

Can you just rearrange the textboxes vertically?

[Ray67]

June7

I can rearrange it in access???

[June7]

You can rearrange and resize controls on form or report any way you like, as long as they are not 'grouped' and not Datasheet View (on form). Even associated labels and boxes can be reconfigured to your 'transposed' arrangement. I know you have built forms and reports, why does this confuse you?

[Ray67]

The report is coming from another systems not access. Sorry i should have been more clear. I have the data in excel and i would like to transfer it to access.

[June7]

Comment still applies. Bind report to that data source and rearrange the controls.

If you want to have the data actually saved in the vertical (normalized) structure, need a UNION query to rearrange. Then either use the UNION query as source for subsequent queries or report data OR make a table from the UNION.

We already discussed UNION in https://www.accessforums.net/queries...66/index2.html

[Ray67]

yes we did discussed UNION query before. Not sure how you did it. You just gave me the SQL.

[June7]

Last line from post 27 of that thread:

There is no wizard for UNION query, must type in the SQL View of query designer.

[Ray67]

I think i got it

SELECT [Provider Name], [Patient name], "ICD91" AS Category,ICD91 as Data FROM CODE
WHERE [ICD91] Is Not Null
UNION SELECT [Provider Name], [Patient name], "ICD92" AS Category, ICD92
FROM CODE
WHERE [ICD92] Is Not Null;

Does this look right??? It looks like it's pull the right data. Just want to make sure i'm not missing anything.

Thank you

[June7]

Looks good. Be aware that a UNION query will eliminate duplicate records unless the ALL parameter is used. If the selected fields result in more than one record having the same combination of data, the 'duplicates' will be rejected. Including a unique identifier in the selected fields will prevent 'duplicates'.

UNION ALL SELECT ...

[Ray67]

Thank you very much.