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

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.

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?

Pass-through queries need to be written in the SQL syntax of the back end database. So you'll need to change the SQL to whatever SQL Server (or whatever RDBMS you are using) needs.

I need to select specific GL codes Include the criteria in each select statement to reduce the amount of data that needs to be unioned.

You have another thread going on this topic. Please don't post the same question multiple times.
http://www.access-programmers.co.uk/forums/search.php?searchid=353931