On Fri, 29 Sep 2006 12:38:02 -0700, John D
wrote:

John - thanks so much - just the direction I needed.

Just spent most of the morning delving into UNION Queries as they apply to
my problem. May I ask two details questions?

1) You said - "A 'Normalizing Union Query' is the trick here." I Googled
that phrase and found a couple of direct string returns - one of which was
another posting by you ( http://www.dbforums.com/archive/inde...t-329313.html). I
think "Normalizing" in this context means the EFFECT of applying a series of
proper UNION Queries in this situation rather than a specific structural TYPE
of UNION Query. Is that your meaning?

exactly. Sorry for not being clearer.

2) Does this procedure make sense to you? First, output the field names to
(say) an Excel spreadsheet. Second, create my system of account numbers using
that spreadsheet. (Ex - "WATSALE_RESIDENTIAL" could have an account number
next to it of, say, 4001. And so on.) Third, change all field names in Access
Table Design View to the new account number system. Fourth, do the UNION and
APPEND Queries you suggest to create a new normalized Table. Fifth, import
the Excel list as a new Table that I'd use in queries to associate the
account name with the account number.

That would be one way; a bit simpler would be to build the table of
fieldnames and their corresponding accountnumbers, first, and use them
in the UNION query:

SELECT OrgID, FY, DLookUp("[AccountNo]", "[Accounts]",
"[AccountFieldname] = 'WATSALE_RESIDENTIAL'" AS AccountNo,
[WATSALE_RESIDENTIAL] AS Value
FROM wideflattable
WHERE [WATSALE_RESIDENTIAL] IS NOT NULL
UNION ALL...


John W. Vinson[MVP]