Rearranging a SQL table's data to a local table

[mdjks]

I have a SQL table that holds financial data. There are 12 columns labled Acct_Per01 through Acct_Per12. I need to select specific GL codes and sum the values in these columns as various groupings, Private, Commercial, Ancillary etc. for each of 14 hospitals.

How can I best create a working table where I have one record for each hospital for each fiscal period.

I want the final table structure be be like the sample below.

Any thoughts on the best way to do this?

Fiscal_Period 200506
Hospital ALX
Private 79,500
Commercial 0
Ancillary 650,457

 

[The_Doc_Man]

This information is totally not normalized from the looks of it. Probably came from a spreadsheet.

12 columns labled Acct_Per01 through Acct_Per12

This is called a "repeating group" and violates normalization rules.

A single record should show

tblAcctData
fldAcctPeriod - a time field related to your accounting period
fldAcctHosp - your hospital code
fldAcctType - your charge-class code (Private, Commercial, Ancillary etc.)
fldAcctValue - the charge for that code for that period.

Then, various queries can do your "group-by" and you can even try a crosstab query for the stuff you want arranged by columns or rows or whatever.

 

[mdjks]

What I've ended up doing is a Union query which will provide data that can be manipulated. I tried running it as a passthru but got errors on the concatenated first field.

SELECT

PATIENT_ACTG_J_ACCOUNT_MSTR.fSCL_YR & "01" AS AcctPeriod,
PATIENT_ACTG_J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG_J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG_J_ACCOUNT_MSTR.ACT_PER_01 as DollarValue


FROM
PATIENT_ACTG_J_ACCOUNT_MSTR

Union Select


PATIENT_ACTG_J_ACCOUNT_MSTR.fSCL_YR & "02" AS AcctPeriod,
PATIENT_ACTG_J_ACCOUNT_MSTR.GL_DPT,
PATIENT_ACTG_J_ACCOUNT_MSTR.SUBACCT,
PATIENT_ACTG_J_ACCOUNT_MSTR.ACT_PER_02 as DollarValue

FROM
PATIENT_ACTG_J_ACCOUNT_MSTR;

This code runs and gives me the values I am looking for. It will need further manipulation since the hospital code is actually part of the GL_DPT code but at least it puts the data in a form I can use without massive IIF statements.

Any suggestions for running as a pass-thru?