How to combine field values into one field.

[Guest]

I have a query with the text fields
[Cparty Name] [CCY]
and value fields
[P & L] [CPrgm Fee] [Revenue reversal entry from previous month] [Revenue
current month accruals] [CP interest expense allocated]

I want to take the value fields and make them into one field called amount
and i want to add another field called description which will tell me if the
amount is "P & L", "CPrgm Fee", "Revenue reversal entry from previous month",
"Revenue current month accruals" And "CP interest expense allocated"

Thanks to those who can help in advance.

 

[Guest]

The first part is easy, the second part will require you to define how you
intend to select the value for the Description field.

As to the amount, create a Calculated field in your query:

Amount: [P & L] + [CPrgm Fee] + [Revenue reversal entry from previous month]
+ [Revenue current month accruals] + [CP interest expense allocated]

If Description is based on which of the 5 field contained the actual value
for amount and the other fields are all zero, then you could use something
like the Switch function to do that:

Description: Switch([P & L] >0, "P & L", [CPrgm Fee] >0, "CPrgm Fee",
[Revenue reversal entry from previous month] >0, "Revenue reversal entry from
previous month", [Revenue current month accruals] >0, "Revenue current month
accruals", [CP interest expense allocated] >0, "CP interest expense
allocated")

It that is not what you want for the description, post back with the
business rules for it.

 

[John Spencer]

Sounds to me as if you would have to write a UNION query to normalize the
data and then use that saved query as the source for a totals query.

The query grid cannot handle UNION queries. They must be constructed in the
SQL view.

SELECT [Cparty Name], [CCY], [P&L] as ItemValue, "P&L" as ItemType
FROM [Your Table]
UNION ALL
SELECT [Cparty Name], [CCY], [CPrgm Fee], "CPrgm Fee"
FROM [Your Table]
UNION ALL
....
UNION ALL
SELECT [Cparty Name], [CCY], [CP interest expense allocated], "CP interest
expense allocated"
FROM [Your Table]

With that saved as qNormItems (or whatever name you chose), you can then
build a query like the following.

SELECT [Cparty Name], [CCY], Sum(ItemValue), ItemType
FROM qNormItems
GROUP BY [Cparty Name], [CCY], ItemType

IF you wish to build the Totals query in the grid
-- Open a New query
-- Select the saved query qNormItems
-- Select the fields you want in the query results
-- Select View: Totals from the menu
-- Change GROUP BY to SUM for the field you want to total.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..