How to combine field values into one field.

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
 
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
..
 
Back
Top