UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Sub Query Issues, Access 2016    
 
   
SimonPowell
post Jan 2 2018, 10:01 AM
Post#1



Posts: 4
Joined: 2-January 18



I need to write a sub query to calculate the variance between forecast and actual values. I have it working fine building a query based on an existing query, but I need it to run dynamically from SQL built in VBA. The first Query (Variance00) is as follows:

SELECT Product.Description, Sum([0100].Actual) AS Actual, Sum([0100].Forecast) AS Forecast
FROM 0100 INNER JOIN Product ON [0100].Product = Product.ID
GROUP BY Product.Description;


The second query (Variance01) is as follows:
SELECT Variance00.Description, ([Variance00]![Forecast]-[Variance00]![Actual])/[Variance00]![Forecast] AS Variance
FROM Variance00;


Note that the actual formula I use is more complex as it has to handle zero values, but if I can get the an sub query working, I am not too worried about error values as I can expand the subquery myself once it is working to handle the zero values appropriately. I have tried lots of things and even done a several online courses but seem to be having a mental block getting it to work in this situation!

I have attached a simplified database with the two queries outlined above. If anyone could take a look and provide the SQL for the Variance using a single query with a sub query embedded, it would really start the new year off well for me.

Appreciate any support and all the best for the new year.

Kind Regards,

Simon






Attached File(s)
Attached File  SubQuery.zip ( 46.82K )Number of downloads: 6
 
Go to the top of the page
 
ranman256
post Jan 2 2018, 10:27 AM
Post#2



Posts: 875
Joined: 25-April 14



you can also make custom functions with YOUR calculation.
paste the code into a module then call it from a query

CODE
function CalcVarianc(pvFore, pvActual)
CalcVarianc =(pvFore-pvActual)/pvFore
end function


usage:

SELECT Variance00.Description, CalcVarianc([Forecast],[Actual]) AS Variance
FROM Variance00;

Go to the top of the page
 
RJD
post Jan 2 2018, 11:17 AM
Post#3


UtterAccess VIP
Posts: 8,799
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

You can certainly combine the two queries in a query/subquery arrangement like this ...

SELECT
A.Description,
IIf(A.SumOfForecast=0,Null,(A.SumOfForecast-A.SumOfActual)/A.SumOfForecast) AS Variance,
IIf(A.SumOfForecast=0,"Forecast is Zero","") AS Comment
FROM
(SELECT Product.Description, Sum([0100].Actual) AS SumOfActual, Sum([0100].Forecast) AS SumOfForecast FROM 0100 INNER JOIN Product ON [0100].Product = Product.ID GROUP BY Product.Description) AS A;

Note that I dealt with the zero denominator issue and added a comment pertaining to that.

But if you "need it to run dynamically from SQL built in VBA", then you have a limitation. If you are simply executing this SQL from a module, then SELECT queries won't work - only Action queries. How did you plan to use this? I didn't see a module in your posted db to deal with this part.

HTH
Joe

Attached File(s)
Attached File  SubQuery_Rev1.zip ( 40.21K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
SimonPowell
post Jan 2 2018, 11:20 AM
Post#4



Posts: 4
Joined: 2-January 18



Hi Ranman, Thanks for the response. The reason I need it as a subquery is that the SQL is used to build the recordset for the subfrm to the right hand side of the chart each time the data is updated. This is done on the front end of a split database that has no queries in it, everything is done with VBA with event triggers.
Cheer,
Simon

Attached File(s)
Attached File  Capture.JPG ( 78.19K )Number of downloads: 2
 
Go to the top of the page
 
SimonPowell
post Jan 2 2018, 11:31 AM
Post#5



Posts: 4
Joined: 2-January 18



Hi Joe,
I'm using the SQL as the recordset for a subform (see the form to the right of the main chart which temporarily has Actuals entered, but I need to have variance sorted in descending order here when I get the query to work). It is the front end of a split database that has no queries in it, just event triggers that generate the recordsets for each subsequent subform.
I'm just taking a look at the query you added, it may do the job if I just change it to the more detailed formula shown in Variance99 but will need to unpick it for a little while, for some reason it won't open in design view...
Cheers,
Simon
Attached File(s)
Attached File  Capture.JPG ( 78.19K )Number of downloads: 0
 
Go to the top of the page
 
RJD
post Jan 2 2018, 11:40 AM
Post#6


UtterAccess VIP
Posts: 8,799
Joined: 25-October 10
From: Gulf South USA


Hi Simon: Understand about the query usage. And the sort should be a very simple mod to the query.

Let us know how this works for you and if we can be of further assistance.

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
SimonPowell
post Jan 2 2018, 12:07 PM
Post#7



Posts: 4
Joined: 2-January 18



Hi Joe,

I worked through the new query you provided and with a little manipulation came up with the following SQL. The VBA will take care of the sort and changing the positive/negative values (which will represent favourable/unfavourable variances) as well as debits and credits depending on the account group selected and zero balances but I take care of all that with the event trigger. The basic query will be something like this and is what I needed to get the dynamic SQL build in VBA:

SELECT A.Description, IIf([A]![Forecast]=0,IIf([A]![Actual]=0,0,9.99),IIf([A]![Forecast]>0,IIf([Actual]=0,IIf([Forecast]>0,-9.99,9.99),([A].[Actual]-[A].[Forecast])/[A].[Forecast]))) AS Variance
FROM (SELECT Product.Description, Sum([0100].Actual) AS Actual, Sum([0100].Forecast) AS Forecast FROM 0100 INNER JOIN Product ON [0100].Product = Product.ID GROUP BY Product.Description) AS A
ORDER BY IIf([A]![Forecast]=0,IIf([A]![Actual]=0,0,9.99),IIf([A]![Forecast]>0,IIf([Actual]=0,IIf([Forecast]>0,-9.99,9.99),([A].[Actual]-[A].[Forecast])/[A].[Forecast])));


It's a lot to get my head round as I'm not brilliant at SQL and still get lost in it a little but this really helped me sort it out. Thanks so much for pointing me in the right direction.

Kind Regards,

Simon
Go to the top of the page
 
RJD
post Jan 2 2018, 12:28 PM
Post#8


UtterAccess VIP
Posts: 8,799
Joined: 25-October 10
From: Gulf South USA


You are very welcome, Simon. Glad to help. But I am having lots of trouble understanding your logic in your posted query ...

IIf([A]![Forecast]=0,IIf([A].[Actual]=0,0,9.99),IIf([A].[Forecast]>0,IIf(A.[Actual]=0,IIf(A.[Forecast]>0,-9.99,9.99),([A].[Actual]-[A].[Forecast])/[A].[Forecast]))) AS Variance

If you are interested, there should be a much simpler way to express this if you could just give us the logic in plain English instead of in the IIF statement. Just a thought ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd September 2018 - 10:00 PM