Count values across multiple columns

[mkallover]

I'd like to do a query where I count the number of occurrences of error codes that are assigned to a claim. However, on each claim there can be up to 10 error codes that are in columns "err_cde_01, err_cde_02, etc..."



The first thing I would like to do is count the number of occurrences of each error code (a three digit number) across all 10 columns. Is there a good way to do that outside of summing 10 different queries?

[pbaldy]

Is it too late to fix the design? A normalized design would have the error codes in a one-to-many related table.

[mkallover]

Unfortunately no. I'm working with an extract file based on data pulled from our mainframe so I don't think they'd be keen on redoing their mainframe tables for little old me!

For now I'm using a union query to pull in the 10 fields. I just didn't know if there was a better alternative.

[pbaldy]

No, I suspect you're doing it the best way. You'll probably use the UNION for other things anyway, to essentially normalize the data.