How to create this in Access Query ?

  • Thread starter Thread starter hkappleorange
  • Start date Start date
H

hkappleorange

Data as follows

Region/Status/Amount
==================
US/Budget/100
US/Actual/150

There are 10000 record like this.
I want to generate another set of data
to contain transactions like this
US/Variance/50

How to do this ? Action query ?
 
1. To do this efficiently, I suggest you create another table with 3 fields:
Country Text
EntryType Text
Amount Currency
Save as (say) tblData.

2. Create a query into the original table.
In the Field row, enter:
Country: Split("US/Budget/100", "/")(0)
In the next column in the Field row, enter:
EntryType: Split("US/Budget/100", "/")(1)
In the third column in the Field row, enter:
Amount: CCur(Nz(Split("US/Budget/100", "/")(2),0))

3. Change this query into an Append query (Append on Query menu), and answer
that you want to append to the table you created in step 1. Run the query to
populate the table. No need to save it.

4. Create a query into tblData.
Drag Country and EntryType into the grid.
In the Criteria row under EntryType, enter:
"Budget"
Type this into a fresh column in the Field row:
Variance: (SELECT Actual FROM tblData AS Dupe
WHERE Dupe.Country = tblData.Country
AND Dupe.EntryType = 'Actual') - [Budget]

Notes
====
If you are using Access 2000, you could run into this problem at step 2:
http://support.microsoft.com/?id=225956

Step 4 involves a subquery. If subqueries are new, see:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Hi,



Assuming you have already 3 fields, to get the variance, by region, use a
total query:


SELECT region, "Variance", VAR(amount)
FROM myTable
GROUP BY region


If you really need to "merge" it with your initial data, you can use:


SELECT region, status, amount FROM myTable
UNION ALL
SELECT region, "Variance", VAR(amount) FROM myTable GROUP BY region



Hoping it may help,
Vanderghast, Access MVP
 
Thanks both of you.
My definition of variance is Actual - Budget instead of mathematical
variance. Thanks.
 
Hi,


ah.


SELECT region, "Variance", SUM( iif(status='actual', 1, -1) * amount )
FROM myTable
GROUP BY region


should do (for the first query).

The iif change the sign of Amount, ( + for actual, - for otherwise, ie, for
budget ) before summing the whole list of amounts (per region), leading to
the accounting "variance".



Hoping it may help,
Vanderghast, Access MVP
 
Thanks !!!

Michel Walsh said:
Hi,


ah.


SELECT region, "Variance", SUM( iif(status='actual', 1, -1) * amount )
FROM myTable
GROUP BY region


should do (for the first query).

The iif change the sign of Amount, ( + for actual, - for otherwise, ie, for
budget ) before summing the whole list of amounts (per region), leading to
the accounting "variance".



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
 
I just tested... it generates
US/Variance/250

instead of desired result
US/Variance/50
 
Hi,



With


RegionsAmounts
Region Status Amount
US Budget 100
US Actual 150




I got from

SELECT RegionsAmounts.Region, "Variance" AS Expr1,
Sum(IIf(status='actual',1,-1)*amount) AS Expr2
FROM RegionsAmounts
GROUP BY RegionsAmounts.Region;


the expected result:


Query61
Region Expr1 Expr2
US Variance 50



Can you double check to see if the ( ) are all at the right place, inside
the SUM ? Or are amount all positive (initially) ?



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top