Need to create a query, choose between percentage rates based on Date range. 1

[debq]

I have inherited an old DB at work and need to make changes to our Audit Pass.Fail rate - FROM 40% to 30%....

I have a select query called, "Q09d2_percent", that calculates a percent and then flags a field called "Follow-up" either "Yes" or "No" based on the "percent" field calculation.The current query

applies a "YES" flag for percents "IIf([Percent]>0.395" or greated than 40%. The query also evaluates other various criteria.

I now need to apply the current level of 40% (0.395) rate to audits with dates less than or equal to =< 08/31/2012 and a 30% (0.295) for dates greater than or equal =>

09/01/2012. I can't just go into the query and update the percent to 0.295 because we need to maintain the exisiting percent for past audits.

In the future the rate will drop to 20% (0.195). So I need to be flexible in my new method.

I want to create a VBA Module to create this query 'Q09d2_percent" and based on the date criteria choose the appropriate rate.

The current table query SQL is:

SELECT Q09d1_amount_summary.[Audit Review Number], IIf([Q09d1_amount_summary]![SumOfBilled_Amount]=0,0,[Q09d1_amount_summary]![SumOfVariance]/[Q09d1_amount_summary]![SumOfBilled_Amount]) AS [Percent], IIf([Percent]>0.395 Or [Percent]<-0.395,"Yes","No") AS [Follow-up], Q09b_Total_Cases.[Total Cases], Q09c_Cases_Reviewed.[Cases Reviewed], [Total Cases]-[Cases Reviewed] AS Difference, Q09d1_amount_summary.Ready, Q09d1_amount_summary.Audit_Type, IIf([Q09d1_amount_summary]![SumOfOld_Billed_amount]=0,0,[Q09d1_amount_summary]![SumOfOld_Varience]/[Q09d1_amount_summary]![SumOfOld_Billed_amount]) AS Old_Percent
FROM (Q09d1_amount_summary INNER JOIN Q09b_Total_Cases ON Q09d1_amount_summary.[Audit Review Number] = Q09b_Total_Cases.[Audit Review Number]) INNER JOIN Q09c_Cases_Reviewed ON Q09d1_amount_summary.[Audit Review Number] = Q09c_Cases_Reviewed.[Audit Review Number]
WHERE (((Q09d1_amount_summary.Ready)="Yes"));

I am new to VBA and would appreciate any help and guidance provided. I am having difficulty breaking the SQL out into "If Else Then" Statements. Do I run the SQL without the percent and then create the IF ELSE THEN statement for just that part or do I need to break the SQL apart into various IF ELSE THEN statements????


This is what I have so far:

Option Compare Database
Option Explicit

Private Sub cmdRun_MakePercentQuery()

End Sub
'declare variables to provide rates

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim Rate40 As Double
Dim Rate30 As Integer
Dim Rate20 As Integer

Rate40 = 0.395
Rate30 = 0.295
Rate20 = 0.195

Set db = CurrentDb()

'The next line creates and automatically appends the QueryDef.
    Set qdf = db.CreateQueryDef("Q09d2_percent")
    
    'Set the SQL property to a string representing a SQL statement.

    qdf.SQL = SELECT Q09d1_amount_summary.[Audit Review Number], IIf([Q09d1_amount_summary]![SumOfBilled_Amount]=0,0,[Q09d1_amount_summary]![SumOfVariance]/[Q09d1_amount_summary]![SumOfBilled_Amount]) AS [Percent], IIf([Percent]>0.395 Or [Percent]<-0.395,"Yes","No") AS [Follow-up], Q09b_Total_Cases.[Total Cases], Q09c_Cases_Reviewed.[Cases Reviewed], [Total Cases]-[Cases Reviewed] AS Difference, Q09d1_amount_summary.Ready, Q09d1_amount_summary.Audit_Type, IIf([Q09d1_amount_summary]![SumOfOld_Billed_amount]=0,0,[Q09d1_amount_summary]![SumOfOld_Varience]/[Q09d1_amount_summary]![SumOfOld_Billed_amount]) AS Old_Percent
FROM (Q09d1_amount_summary INNER JOIN Q09b_Total_Cases ON Q09d1_amount_summary.[Audit Review Number] = Q09b_Total_Cases.[Audit Review Number]) INNER JOIN Q09c_Cases_Reviewed ON Q09d1_amount_summary.[Audit Review Number] = Q09c_Cases_Reviewed.[Audit Review Number]
WHERE (((Q09d1_amount_summary.Ready)="Yes"));

      
        
If [MaxAuditMonthYear] <= #08/31/2012# Then 0.395

Else

If [MaxAuditMonthYear >= #09/01/2012# Then 0.295


End If

 

[Golom]

I really wouldn't mess with changing the query every time you want to run it. With your approach you will need to modify code whenever dates or percentages change.

Set Up a Table Like this called "RateDates"

[b]RateDate          Percentage[/b]
08/31/2012           0.395         [blue] 0.395 up to Aug 31, 2012[/blue]
12/31/2012           0.295         [blue] 0.295 up to Dec 31, 2012[/blue]
12/31/3099           0.195         [blue] 0.195 after that[/blue]

In this I have set "RateDate" as the last day on which the rate applies.
The last one (3099) just ensures that at least one rate will always be found.


Then, write a function like this

Public Function GetPercent (TheDate As Date) As Currency
Dim db  As DAO.Database
Dim SQL As String
Dim rs  As DAO.Recordset

SQL = "SELECT RateDate, Percentage  From RateDates " & _
      "WHERE RateDate >= #" & TheDate & "# " & _
      "ORDER BY RateDate "

Set db = CurrentDB
Set rs = db.OpenRecordset ( SQL )

GetPercent = rs![Percentage]

Set rs = Nothing
Set db = Nothing

End Function

and save it in a module

Then change your query's SQL to

SELECT 
S.[Audit Review Number], 
IIf(S.[SumOfBilled_Amount]=0,0, S.[SumOfVariance]/S.[SumOfBilled_Amount]) AS [Percent], 

IIf(Abs([Percent])>[b]GetPercent(S.MaxAuditMonthYear)[/b],"Yes","No") AS [Follow-up], 

C.[Total Cases], R.[Cases Reviewed], [Total Cases]-[Cases Reviewed] AS Difference, 
S.Ready, S.Audit_Type, 
IIf(S.[SumOfOld_Billed_amount]=0,0,S.[SumOfOld_Varience]/S.[SumOfOld_Billed_amount]) AS Old_Percent

FROM 
(Q09d1_amount_summary As S 
INNER JOIN Q09b_Total_Cases As C ON S.[Audit Review Number] = C.[Audit Review Number]) 
INNER JOIN Q09c_Cases_Reviewed As R ON S.[Audit Review Number] = R.[Audit Review Number]

WHERE (((S.Ready)="Yes"));

I have assumed that the field MaxAuditMonthYear is in the table Q09d1_amount_summary. If it comes from some external source (a textbox on a form for example) then some modifications will be required.

 

[debq]

Golom, Thanks so much for your help.

I have made the changes as you suggested but now I am stuck again. How do I get the Query to appear in the DB under the Queries list and be available for use in another query?

This is the code that I have so far:

Option Compare Database
Option Explicit
Public Function GetPercent(TheDate As Date) As Double

Dim db As DAO.Database
Dim SQL As String
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String

SQL = "SELECT RateDate, Percentage  From RateDates " & _
      "WHERE RateDate >= #" & TheDate & "# " & _
      "ORDER BY RateDate "

Set db = CurrentDb
Set rs = db.OpenRecordset(SQL)

GetPercent = rs![Percentage]

Set qdf = Object.CreateQueryDef("Q09d2_percent_test", SQL)


'create the querydef sql string
strSQL = "SELECT S.[Audit Review Number], IIf(S.[SumOfBilled_Amount]=0,0, S.[SumOfVariance]/S.[SumOfBilled_Amount]) AS [Percent]," & _
"IIf(Abs([Percent])>GetPercent Or (Abs([Percent])<-GetPercent,'Yes','No')AS [Follow-up], C.[Total Cases], R.[Cases Reviewed], " & _
"[Total Cases]-[Cases Reviewed] AS Difference, S.Ready, S.Audit_Type, IIf(S.[SumOfOld_Billed_amount]=0,0,S.[SumOfOld_Varience]/S.[SumOfOld_Billed_amount])" & _
"AS Old_Percent FROM (Q09d1_amount_summary As S INNER JOIN Q09b_Total_Cases As C ON S.[Audit Review Number] = C.[Audit Review Number]) INNER JOIN Q09c_Cases_Reviewed " & _
"As R ON S.[Audit Review Number] = R.[Audit Review Number] WHERE (((S.Ready)='Yes'));"

CurrentDb.QueryDefs("Q09d2_percent_test").SQL = strSQL


Set rs = Nothing
Set db = Nothing

End Function

I think I need a "Print" function to add the query to the DB, but am not sure.

Thanks again

 

[Golom]

Perhaps I wasn't too clear about how this works. You appear to be still hung up on the idea that you need to modify the SQL in your query. With this approach that is not necessary.

Save the GetPercent function in a module just as I gave it to you.

Then go into query designer (SQL view) and paste this

SELECT 
S.[Audit Review Number], 
IIf(S.[SumOfBilled_Amount]=0,0, S.[SumOfVariance]/S.[SumOfBilled_Amount]) AS [Percent], 

IIf(Abs([Percent])>GetPercent(S.MaxAuditMonthYear),"Yes","No") AS [Follow-up], 

C.[Total Cases], R.[Cases Reviewed], [Total Cases]-[Cases Reviewed] AS Difference, 
S.Ready, S.Audit_Type, 
IIf(S.[SumOfOld_Billed_amount]=0,0,S.[SumOfOld_Varience]/S.[SumOfOld_Billed_amount]) AS Old_Percent

FROM 
(Q09d1_amount_summary As S 
INNER JOIN Q09b_Total_Cases As C ON S.[Audit Review Number] = C.[Audit Review Number]) 
INNER JOIN Q09c_Cases_Reviewed As R ON S.[Audit Review Number] = R.[Audit Review Number]

WHERE (((S.Ready)="Yes"));

and save it as Q09d2_percent_test. Note that the query calls the function sending it the S.MaxAuditMonthYear parameter and the function returns the appropriate percentage from the table RateDates. You should also note that I changed the test in your IIF to include the ABS function so that you need to do only the [blue]ABS(Percent) > GetPercent(...)[/blue] test. That handles both positive and negative values for Percent.

The question that is still unresolved is where does MaxAuditMonthYear come from? Is it a field in a table somewhere? Is it a user-supplied parameter?

I have assumed that it is in the table Q09d1_amount_summary but that's not the case then we will need to make some adjustments.

 

[debq]

Golom,

OMG!! This is great! Now that I am clear on the process I have been able to put the process in place exactly as you suggested and it works great.

I did change the Date field used by the query to "MaxOfAudit_Comp_MonthYear" which is a field the q09c_Cases_Reviewed subQuery used.

Thank you so much for your guidance and for your patience. I have learned alot from working with you.