Solved

access query

Posted on 2015-01-31
21
63 Views
Last Modified: 2016-02-11
i have a query that calculates the Bill Amount based on as you will see in the query.
I built a function GetLarBill  in a expression that always is true with the following exception.

If a user selects InsuranceLimit - y (yes/no) box  and then enters the Bil lAmount Limit  

I would like to the query to execute something like this
if Insurance limit is True
GetLarBill([CustomerID],Nz([billtype]),[LIMIT DOLLAR ABOUT],[TotalSavings])

how would I fit this into the query

SELECT tblRevcodeBill.ClaimID, Sum(tblRevcodeBill.OriginalBill) AS SumOfOriginalBill, Sum(tblRevcodeBill.FeeReduction) AS SumOfFeeReduction, Sum(tblRevcodeBill.AdditionalReduction) AS SumOfAdditionalReduction, Sum(tblRevcodeBill.Settlementoffer) AS SumOfSettlementoffer, Sum(Nz([tblrevcodebill.feereduction])+Nz([tblrevcodebill.additionalreduction])+Nz([tblrevcodebill.Settlementoffer])) AS TotalSavings, Customers.CustomerID, GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill, Sum(Nz([tblrevcodebill.originalbill])-Nz([tblrevcodebill.feereduction])-Nz([tblrevcodebill.additionalreduction])-Nz([tblrevcodebill.settlementoffer])) AS RevisedBill, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.[LIMIT DOLLAR ABOUT]
FROM tblSettlem, Customers INNER JOIN (tblRevcodeBill INNER JOIN tblClaim1 ON tblRevcodeBill.ClaimID = tblClaim1.ClaimID) ON Customers.ID = tblClaim1.ID_Cust
GROUP BY tblRevcodeBill.ClaimID, Customers.CustomerID, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.[LIMIT DOLLAR ABOUT]
ORDER BY tblRevcodeBill.ClaimID;
0
Comment
Question by:seamus9909
  • 10
  • 9
  • 2
21 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40581704
There seems to be some join problem. Are you having any success in other columns? Are you getting some errors?
SELECT rb.ClaimID
	, Sum(rb.OriginalBill) AS SumOfOriginalBill
	, Sum(rb.FeeReduction) AS SumOfFeeReduction
	, Sum(rb.AdditionalReduction) AS SumOfAdditionalReduction
	, Sum(rb.Settlementoffer) AS SumOfSettlementoffer
	, Sum(Nz([rb.feereduction])+Nz([rb.additionalreduction])+Nz([rb.Settlementoffer])) AS TotalSavings
	, cst.CustomerID
	, GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill
	, Sum(Nz([rb.originalbill])-Nz([rb.feereduction])-Nz([rb.additionalreduction])-Nz([rb.settlementoffer])) AS RevisedBill
	, c.BILLTYPE, c.INSURANCELIMIT, c.[LIMIT DOLLAR ABOUT]
FROM tblSettlem, Customers cst INNER JOIN (tblRevcodeBill rb INNER JOIN tblClaim1 c 
        ON rb.ClaimID = c.ClaimID) 
        ON cst.ID = c.ID_Cust
GROUP BY rb.ClaimID
	, cst.CustomerID
        , c.BILLTYPE, c.INSURANCELIMIT
	, c.[LIMIT DOLLAR ABOUT]
ORDER BY rb.ClaimID;

Open in new window


For now, this code is just if formatted for clarity and some tables are aliased. It is not clear weather all works except the function issue or there are some other errors beside the function.
0
 

Author Comment

by:seamus9909
ID: 40581903
The Query is working fine.   I want to add the "condition" to check for insurance limit flag and limit dollar amount and run the  GetLarbil module with

GetLarBill([CustomerID],Nz([billtype]),[Limit Dollar Amount],[TotalSavings]) AS LarBill

if InsuranceLimit is no then run the expression.

GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill

Does this make sense?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40582169
Combine:
GetLarBill([CustomerID],Nz([billtype]),[Limit Dollar Amount],[TotalSavings]) AS LarBill
and
GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill

to

GetLarBill([CustomerID],Nz([billtype]),[Limit Dollar Amount],[sumoforiginalbill],[TotalSavings]) AS LarBill

And revise your function GetLarBill(...) in the module to perform as you like.

Wouldn't this work for you?
0
 

Author Comment

by:seamus9909
ID: 40582523
I revised my Function GetLarBill to include LimitdollarAmount as a string.  and updated the Query, however on rows that don't have a DollarAmountLimit, the Lar Bill is coming back "#Error"  Not working but getting closer



Function GetLArBill(strCustomerID As String, BILLTYPE As String, Limitdollaramount As String, varBill As Double, varTotSaving As Double) As Double
    Select Case strCustomerID
        Case "AL":
            Select Case BILLTYPE
                Case "-1": GetLArBill = 7.5: Exit Function
                Case Else: GetLArBill = varTotSaving * 0.25: Exit Function
            End Select
        Case "AD": GetLArBill = varTotSaving * 0.22: Exit Function
        Case "JP": GetLArBill = varTotSaving * 0.18: Exit Function
        Case "MC": GetLArBill = varTotSaving * 0.15: Exit Function
        Case "YK": GetLArBill = varTotSaving * 0.15: Exit Function
        Case "AH"
            Select Case varTotSaving
                 Case Is < 10001: GetLArBill = 1500: Exit Function
                 Case Is < 20001: GetLArBill = 3000: Exit Function
                 Case Is < 30001: GetLArBill = 4500: Exit Function
                 Case Is < 40001: GetLArBill = 6000: Exit Function
                 Case Is < 50001: GetLArBill = 7500: Exit Function
                 Case Is < 60001: GetLArBill = 9000: Exit Function
                 Case Is < 70001: GetLArBill = 10500: Exit Function
                 Case Is < 80001: GetLArBill = 12000: Exit Function
                 Case Is < 90001: GetLArBill = 13375: Exit Function
                 Case Is < 100001: GetLArBill = 13750: Exit Function
                 Case Is < 125001: GetLArBill = 15625: Exit Function
                 Case Is < 150001: GetLArBill = 18750: Exit Function
                 Case Is < 175001: GetLArBill = 19650: Exit Function
                 Case Is < 200001: GetLArBill = 22500: Exit Function
                 Case Is < 250001: GetLArBill = 28125: Exit Function
                 Case Is < 300001: GetLArBill = 33750: Exit Function
                 Case Is < 400001: GetLArBill = 45000: Exit Function
                 Case Is < 500001: GetLArBill = 56250: Exit Function
                 Case Is < 750001: GetLArBill = 84375: Exit Function
                 Case Is > 750000: GetLArBill = 123000: Exit Function
            End Select
            Case "SY"
            Select Case varTotSaving
                 Case Is < 10001: GetLArBill = varTotSaving * 0.25: Exit Function
                 Case Is < 20001: GetLArBill = 2500: Exit Function
                 Case Is < 30001: GetLArBill = 4500: Exit Function
                 Case Is < 40001: GetLArBill = 6000: Exit Function
                 Case Is < 50001: GetLArBill = 7500: Exit Function
                 Case Is < 60001: GetLArBill = 9000: Exit Function
                 Case Is < 70001: GetLArBill = 10500: Exit Function
                 Case Is < 80001: GetLArBill = 12000: Exit Function
                 Case Is < 90001: GetLArBill = 13500: Exit Function
                 Case Is < 100001: GetLArBill = 15000: Exit Function
                 Case Is < 125001: GetLArBill = 18750: Exit Function
                 Case Is < 150001: GetLArBill = 22500: Exit Function
                 Case Is > 175001: GetLArBill = varTotSaving * 0.15: Exit Function
                 
            End Select
        Case Else
                GetLArBill = varTotSaving * 0.25: Exit Function
    End Select
End Function

and the query to:

SELECT tblRevcodeBill.ClaimID, Sum(tblRevcodeBill.OriginalBill) AS SumOfOriginalBill, Sum(tblRevcodeBill.FeeReduction) AS SumOfFeeReduction, Sum(tblRevcodeBill.AdditionalReduction) AS SumOfAdditionalReduction, Sum(tblRevcodeBill.Settlementoffer) AS SumOfSettlementoffer, Sum(Nz([tblrevcodebill.feereduction])+Nz([tblrevcodebill.additionalreduction])+Nz([tblrevcodebill.Settlementoffer])) AS TotalSavings, Customers.CustomerID, GetLarBill([CustomerID],Nz([billtype]),[LimitDollarAmount],[sumoforiginalbill],[TotalSavings]) AS LarBill, Sum(Nz([tblrevcodebill.originalbill])-Nz([tblrevcodebill.feereduction])-Nz([tblrevcodebill.additionalreduction])-Nz([tblrevcodebill.settlementoffer])) AS RevisedBill, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.LIMITDOLLARAMOUNT
FROM tblSettlem, Customers INNER JOIN (tblRevcodeBill INNER JOIN tblClaim1 ON tblRevcodeBill.ClaimID = tblClaim1.ClaimID) ON Customers.ID = tblClaim1.ID_Cust
GROUP BY tblRevcodeBill.ClaimID, Customers.CustomerID, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.LIMITDOLLARAMOUNT
ORDER BY tblRevcodeBill.ClaimID;
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40582615
Save the new combined function because it will be faster to execute. Going back to your two function solution with focus on line:

GetLarBill([CustomerID],Nz([billtype]),[sumoforiginalbill],[TotalSavings]) AS LarBill

change it to:

, IIF( InsuranceLimit = true, GetLarBill([CustomerID],Nz([billtype]), [LIMIT DOLLAR ABOUT], [TotalSavings]), GetLarBill( [CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings])) AS LarBill

Question: you you have  InsuranceLimit column in your query? It was not clear to me if you have or not.

If you do, this ought to work. After this solution works, we will have the clarity we need to shape up the new combined function.

Mike

post is revised...
0
 

Author Comment

by:seamus9909
ID: 40583083
Yes Mike

I have insurancelimit as a Column in the query.

When I place your ,IFF statement in the query , I get  "the function you entered as the wrong number of arguments?

are you saying don't use the module GetLarBill

SELECT tblRevcodeBill.ClaimID, Sum(tblRevcodeBill.OriginalBill) AS SumOfOriginalBill, Sum(tblRevcodeBill.FeeReduction) AS SumOfFeeReduction, Sum(tblRevcodeBill.AdditionalReduction) AS SumOfAdditionalReduction, Sum(tblRevcodeBill.Settlementoffer) AS SumOfSettlementoffer, Sum(Nz([tblrevcodebill.feereduction])+Nz([tblrevcodebill.additionalreduction])+Nz([tblrevcodebill.Settlementoffer])) AS TotalSavings, Customers.CustomerID,IIF( InsuranceLimit = true, GetLarBill([CustomerID],Nz([billtype]), [LIMITDOLLARAMOUNT], [TotalSavings]), GetLarBill( [CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings])) AS LarBill
, Sum(Nz([tblrevcodebill.originalbill])-Nz([tblrevcodebill.feereduction])-Nz([tblrevcodebill.additionalreduction])-Nz([tblrevcodebill.settlementoffer])) AS RevisedBill, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.LIMITDOLLARAMOUNT
FROM tblSettlem, Customers INNER JOIN (tblRevcodeBill INNER JOIN tblClaim1 ON tblRevcodeBill.ClaimID = tblClaim1.ClaimID) ON Customers.ID = tblClaim1.ID_Cust
GROUP BY tblRevcodeBill.ClaimID, Customers.CustomerID, tblClaim1.BILLTYPE, tblClaim1.INSURANCELIMIT, tblClaim1.LIMITDOLLARAMOUNT
ORDER BY tblRevcodeBill.ClaimID;
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40583120
For simplicity let's named them GetLarBill_1(...)  and GetLarBill_2(...)

IIF( InsuranceLimit = true, GetLarBill_1([CustomerID],Nz([billtype]), [LIMITDOLLARAMOUNT], [TotalSavings]), GetLarBill_2( [CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings])) AS LarBill

In VB>NET, you can have a function and another with the same name but different signature. For a moment I forgot that this cannot be done in vba.

Now, if necessary modify your:
GetLarBill_1(...) for when  InsuranceLimit = true and  
GetLarBill_2(...) for when  InsuranceLimit = false
0
 

Author Comment

by:seamus9909
ID: 40583914
So let me understand,   You are suggesting I create two seperate Modules? and then place them in the query?
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40584072
In the same module have two functions.

For InsuranceLimit = true:  GetLarBill_1([CustomerID],Nz([billtype]), [LIMITDOLLARAMOUNT], [TotalSavings])
and
For InsuranceLimit = false: GetLarBill_2( [CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings])l
0
 

Author Comment

by:seamus9909
ID: 40584096
So Sorry Im so thick at sometimes.  You are saying module, which is not the query correct?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40584128
Module mean code module. There are two kind of modules:

Form level module where form related code is in it.
Standard module where its code is visible globally in an access database file.

What we need standard module so that the queries could access these functions (GetLarBill_1 and GetLarBill_2). Standard modules are listed under module tab in database window. It doesn't matter what the you name these modules. They are named sort of help the designer remember what its content is.

post has been revised.
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 40584506
Rather than that extensive SELECT CASE statement in the code, it'd be better to store that in a table so that you can easily change it over time.  All the criteria including InsuranceLimit  can be in that table, then you simply link on those fields and it should provide the calculation for you.
0
 

Author Comment

by:seamus9909
ID: 40584520
LukeCHung


Can you provide an example of what would be in the table?
0
 
LVL 10

Expert Comment

by:LukeChung-FMS
ID: 40584719
First of all, in your GetLArBill function, you don't need to have all those Exit Function commands. After the line is executed, it automatically goes to the line after the End Select statement and you're out of the function.

I didn't try to figure out everything you had, but I would imagine the table would have fields like this:

InsuranceLimit (Yes/No)
CustomerID (text)
SavingsRangeLow (double -- your varToSavings value)
SavingsRangeHigh (double)
Rate (double -- the percentage)
Amount (double -- the amount)

To start, you'd want to create a query that selects the right record from that table with your data (for the range values, you'd be linking where your field value falls between the high and low values. You can then calculate the amount based on your value and rate (if Rate isn't zero) or use the Amount value.

Something like that.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40584729
seamus9909,

For your consideration, your project has lots of refinement opportunities. For now, I will suggest to stay focused on the objective stated in the original question. Make it work the way you intended.

Later on, on a different post, ask for improvements. Otherwise, you will have never ending project.

Mike
0
 

Author Comment

by:seamus9909
ID: 40585412
Mike good advise.

So im still confused.  What would the syntax be in GetLarBill_1? and in GetLarBill_2?
Do I put the IIF statement in module?

here is what the module looks like now.


Function GetLArBill_11(strCustomerID As String, BILLTYPE As String, varBill As Double, varTotSaving As Double) As Double

    Select Case strCustomerID
        Case "AL":
            Select Case BILLTYPE
                Case "-1": GetLArBill = 7.5:
                Case Else: GetLArBill = varTotSaving * 0.25:
            End Select
        Case "AD": GetLArBill = varTotSaving * 0.22:
        Case "JP": GetLArBill = varTotSaving * 0.18:
        Case "MC": GetLArBill = varTotSaving * 0.15:
        Case "YK": GetLArBill = varTotSaving * 0.15:
        ' Case "AH"
           ' Select Case varTotSaving
            '     Case Is < 10001: GetLArBill = 1500:
            '     Case Is < 20001: GetLArBill = 3000:
            '     Case Is < 30001: GetLArBill = 4500:
            '     Case Is < 40001: GetLArBill = 6000:
            '     Case Is < 50001: GetLArBill = 7500:
            '     Case Is < 60001: GetLArBill = 9000:
            '     Case Is < 70001: GetLArBill = 10500:
            '     Case Is < 80001: GetLArBill = 12000:
            '     Case Is < 90001: GetLArBill = 13375:
            '     Case Is < 100001: GetLArBill = 13750:
            '     Case Is < 125001: GetLArBill = 15625:
            '     Case Is < 150001: GetLArBill = 18750:
            '     Case Is < 175001: GetLArBill = 19650:
            '     Case Is < 200001: GetLArBill = 22500:
            '     Case Is < 250001: GetLArBill = 28125:
            '     Case Is < 300001: GetLArBill = 33750:
            '     Case Is < 400001: GetLArBill = 45000:
            '     Case Is < 500001: GetLArBill = 56250:
            '     Case Is < 750001: GetLArBill = 84375:
            '     Case Is > 750000: GetLArBill = 123000:
            'End Select
            'Case "SY"
            'Select Case varTotSaving
             '    Case Is < 10001: GetLArBill = varTotSaving * 0.25:
             '    Case Is < 20001: GetLArBill = 2500:
             '    Case Is < 30001: GetLArBill = 4500:
             '    Case Is < 40001: GetLArBill = 6000:
             '    Case Is < 50001: GetLArBill = 7500:
             '    Case Is < 60001: GetLArBill = 9000:
             '    Case Is < 70001: GetLArBill = 10500:
             '    Case Is < 80001: GetLArBill = 12000:
             '    Case Is < 90001: GetLArBill = 13500:
              '   Case Is < 100001: GetLArBill = 15000:
              '   Case Is < 125001: GetLArBill = 18750:
             '    Case Is < 150001: GetLArBill = 22500:
             '    Case Is > 175001: GetLArBill = varTotSaving * 0.15:
                 
            ' End Select
        Case Else
                GetLArBill = varTotSaving * 0.25:
   End Select
   
End Function
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40585435
In your query, you have a line like:

GetLarBill([CustomerID],Nz([billtype]),[LimitDollarAmount],[sumoforiginalbill],[TotalSavings]) AS LarBill

replace it with:

IIF( InsuranceLimit = true, GetLarBill_1([CustomerID],Nz([billtype]), [LIMITDOLLARAMOUNT], [TotalSavings]), GetLarBill_2( [CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings])) AS LarBill

======================
And in the module where you have functions, have:

Function GetLArBill_1(...) As Double
  'For InsuranceLimit = true    <-- add codes necessary to use parameters and return the desired result.
End Function

Function GetLArBill_2(...) As Double
  'For InsuranceLimit = false  <-- add codes necessary to use parameters and return the desired result.
End Function
0
 

Author Comment

by:seamus9909
ID: 40590006
so here is GetLarBill_1 module I know I am doing something wrong with the IF

Function GetLArBill_1(InsuranceLimit As String, strCustomerID As String, BILLTYPE As String, varBill As Double, varTotSaving As Double) As Double
For InsuranceLimit = false
    Select Case strCustomerID
        Case "AL":
            Select Case BILLTYPE
                Case "-1": GetLArBill = 7.5:
                Case Else: GetLArBill = varTotSaving * 0.25:
            End Select
        Case "AD": GetLArBill = varTotSaving * 0.22:
        Case "JP": GetLArBill = varTotSaving * 0.18:
        Case "MC": GetLArBill = varTotSaving * 0.15:
        Case "YK": GetLArBill = varTotSaving * 0.15:
       
        Case Else
                GetLArBill = varTotSaving * 0.25:
   End Select
   
End Function
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40598256
Hi seamus9909,

I think we need to separate and break down the issues we are having with this question. As you indicated in the subject line of your question, the question was about access query. If we first manage to handle that, next we could focus on why this function is not working. Per the original post, you have a function (presumably functioning okay) and the focus was how to implement it in the query).

My intention is to move the ball faster and get your issue handled.

Ok by this in mind, lets assume our two functions are correct with this simplifications:

Function GetLArBill_1(InsuranceLimit As String, strCustomerID As String, BILLTYPE As String, varBill As Double, varTotSaving As Double) As Double
    GetLArBill_1 = 10    '<-- for now our function GetLArBill_1 will return 10
End Function

and

Function GetLArBill_2([CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings]) As Double
    GetLArBill_2 = 20    '<-- for now our function GetLArBill_2 will return 20
End Function

If you implement this and get acceptable (returning 10 and 20 for Function GetLArBill_1 and Function GetLArBill_2, respectively), then you can post a new question to figure out what is wrong with the function. I hope this is acceptable to you.

With this approach in mind, I will wait to see if your query issue has been resomed and this question is closed.

Thanks,

Mike
0
 

Author Comment

by:seamus9909
ID: 40602273
Ok Mike I agree lets focus on just the modules.

Here is what I have

Function GetLArBill_2([CustomerID], Nz([billtype]), [sumoforiginalbill], [TotalSavings]) As Double
     GetLArBill_2 = 20    '<-- for now our function GetLArBill_2 will return 20
 End Function

When I try to compile is says  "expected Identifier   on CustomerId
0
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 40602289
These are the parameters for
Function GetLArBill_1(InsuranceLimit As String, strCustomerID As String, BILLTYPE As String, varBill As Double, varTotSaving As Double) As Double

Open in new window


I need your help to modify the parameters for Function GetLArBill_2(...) As Double
My best guess is the following. These parameters are those we will need later to calculate the return for GetLArBill_2().

Function GetLArBill_2(strCustomerID As String, BILLTYPE As Strin, sumoforiginalbill As Double, TotalSavings  As Double) As Double

Open in new window


Thanks,

Mike
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now