Expression for combining fields

[Chuck Frodermann]

Is there a way to combine 2m or more fields into 1 with an expression such
as

[ICD9]&" "&[icd92]&" "&[icd93]

the desired result would be

icd9
icd92
icd93

so you could then sort and find the number of a particular icd9 code.

We have up to icd96 and this is repeated for cpt codes.

Required in this format for state reporting.

their table structure has fields icd9 thru icd96 and the same for cpt codes.

Thanks in Advance
Chuck

 

[KARL DEWEY]

Congratulations, you have a spreadsheet.
To sort as you stated you need one whopping union query.
SELECT Field1, 9 AS ICD, [icd9] AS Data
FROM YourTable
UNION ALL SELECT Field1, 10 AS ICD, [icd10] AS Data
FROM YourTable
UNION ALL SELECT Field1, 11 AS ICD, [icd11] AS Data
FROM YourTable
UNION ALL SELECT Field1, 12 AS ICD, [icd12] AS Data
FROM YourTable
........
UNION ALL SELECT Field1, 93 AS ICD, [icd93] AS Data
FROM YourTable;

Then you can sort.

 

[Chuck]

Karl I need a little further direction. The file is wfall.mdb the table is
visit.
I know a path and file must somehow be entered and then a table.

I know a field is enclosed in [] what about a file and path?

Form your reply I should go to Excel and open a query.
Do I put the full query in 1 cell or in 1a put

select field1, icd9 as ICD,[icd9] as data FROM wfall.mdb visit

and in the cell below enter
UNION ALL SELECT Field1, 10 AS ICD, [icd10] AS Data
FROM visit.

Chuck Thanks for the answer.

Congratulations, you have a spreadsheet.
To sort as you stated you need one whopping union query.
SELECT Field1, 9 AS ICD, [icd9] AS Data
FROM YourTable
UNION ALL SELECT Field1, 10 AS ICD, [icd10] AS Data
FROM YourTable
UNION ALL SELECT Field1, 11 AS ICD, [icd11] AS Data
FROM YourTable
UNION ALL SELECT Field1, 12 AS ICD, [icd12] AS Data
FROM YourTable
........
UNION ALL SELECT Field1, 93 AS ICD, [icd93] AS Data
FROM YourTable;

Then you can sort.
--
KARL DEWEY
Build a little - Test a little

Is there a way to combine 2m or more fields into 1 with an expression such
as

[ICD9]&" "&[icd92]&" "&[icd93]

the desired result would be

icd9
icd92
icd93

so you could then sort and find the number of a particular icd9 code.

We have up to icd96 and this is repeated for cpt codes.

Required in this format for state reporting.

their table structure has fields icd9 thru icd96 and the same for cpt codes.

Thanks in Advance
Chuck