ACCESS FEED: RE: [MS_AccessPros] Multiple if statements in a query

Senin, 14 Juli 2014

RE: [MS_AccessPros] Multiple if statements in a query

Art,
Data belongs in your tables, not in your code or expressions. What happens when you add a new frequency? Are you going to look for everywhere the table is used and add another couple expressions to the Switch()?

I would add a column [FreqMultiplier] to the tlkpIncomeType table (I think that is the appropriate table). Then update the column with the values 12, 1, 2080, 52, and 26. Then your much simpler expression in the query is:
IncomeAmount * tlkpPayFrequencyType.FreqMultiplier as [Annual Income]

Rather than:
Switch(
[tblHouseholdIncome].[PayFrequencyTypeID]=4,[IncomeAmount]*12,
[tblHouseholdIncome].[PayFrequencyTypeID]=6,[IncomeAmount],
[tblHouseholdIncome].[PayFrequencyTypeID]=7,[IncomeAmount]*2080,
[tblHouseholdIncome].[PayFrequencyTypeID]=2,[IncomeAmount]*52,
[tblHouseholdIncome].[PayFrequencyTypeID]=3,[IncomeAmount]*26) AS [Annual Income]

When you add another PayFrequencyTypeID, you only have to update the FreqMultiplier value. Always attempt to maintain data rather than expressions and code.

Duane Hookom MVP
MS Access

To: MS_Access_Professionals@yahoogroups.com
From: MS_Access_Professionals@yahoogroups.com
Date: Mon, 14 Jul 2014 11:02:39 -0700
Subject: Re: [MS_AccessPros] Multiple if statements in a query

That was SWEEET!!!! I did not even know about the switch command. I was looking at the IIF statement and it did not seem to fit the bill. This is the final query code:

SELECT tblHouseholdIncome.IncomeID, tblHouseholdIncome.HouseholdID, tblHouseholdIncome.IncomeTypeID, tblHouseholdIncome.IncomeAmount, tblHousehold.ApplicationID, tlkpIncomeType.IncomeTypeName, tlkpPayFrequencyType.PayFrequencyTypeName, Switch([tblHouseholdIncome].[PayFrequencyTypeID]=4,[IncomeAmount]*12,[tblHouseholdIncome].[PayFrequencyTypeID]=6,[IncomeAmount],[tblHouseholdIncome].[PayFrequencyTypeID]=7,[IncomeAmount]*2080,[tblHouseholdIncome].[PayFrequencyTypeID]=2,[IncomeAmount]*52,[tblHouseholdIncome].[PayFrequencyTypeID]=3,[IncomeAmount]*26) AS [Annual Income]
FROM ((tblHouseholdIncome INNER JOIN tblHousehold ON tblHouseholdIncome.HouseholdID = tblHousehold.HouseholdID) LEFT JOIN tlkpIncomeType ON tblHouseholdIncome.IncomeTypeID = tlkpIncomeType.IncomeTypeID) LEFT JOIN tlkpPayFrequencyType ON tblHouseholdIncome.PayFrequencyTypeID = tlkpPayFrequencyType.PayFrequencyTypeID;

With Warm Regards,

Arthur D. Lorenzini

IT System Manager

Cheyenne River Housing Authority

Wk.(605)964-4265 Ext. 130

Fax (605)964-1070

alorenzini@crhanetwork.org

"Only those who will risk going too far can possibly find out how far one can go."

On Monday, July 14, 2014 12:55 PM, "John Viescas JohnV@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com> wrote:

Arthur-

Switch([PayFrequencyTypeID] = 4, [IncomeAmount] * 12, [PayFrequencyTypeID] = 6, [IncomeAmount], [PayFrequencyTypeID] = 7, [IncomeAmount] * 2080, [PayFrequencyTypeID] = 2, [IncomeAmount] * 52, [PayFrequencyTypeID] = 3, [IncomeAmount] * 26)

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2014, at 12:39 PM, dbalorenzini@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I have the following VBA code that need to translate into a query expression:

If Me.PayFrequencyTypeID = 4 Then 'Monthly
    Me.txtAnnualAmount = Me.IncomeAmount * 12
    ElseIf Me.PayFrequencyTypeID = 6 Then 'Annual
      Me.txtAnnualAmount = Me.IncomeAmount
      ElseIf Me.PayFrequencyTypeID = 7 Then 'Hourly
         Me.txtAnnualAmount = Me.IncomeAmount * 2080
         ElseIf Me.PayFrequencyTypeID = 2 Then 'Weekly
         Me.txtAnnualAmount = Me.IncomeAmount * 52
             ElseIf Me.PayFrequencyTypeID = 3 Then 'Bi-Weekly
               Me.txtAnnualAmount = Me.IncomeAmount * 26

    End If

This is located in the current event of my subform.

Is there a way to do it? This is the query that I need to update with the expression:

SELECT tblHouseholdIncome.IncomeID, tblHouseholdIncome.HouseholdID, tblHouseholdIncome.IncomeTypeID, tblHouseholdIncome.IncomeAmount, tblHouseholdIncome.PayFrequencyTypeID, tblHousehold.ApplicationID, tlkpIncomeType.IncomeTypeName, tlkpPayFrequencyType.PayFrequencyTypeName, 0 AS [Annual Income]
FROM ((tblHouseholdIncome INNER JOIN tblHousehold ON tblHouseholdIncome.HouseholdID = tblHousehold.HouseholdID) LEFT JOIN tlkpIncomeType ON tblHouseholdIncome.IncomeTypeID = tlkpIncomeType.IncomeTypeID) LEFT JOIN tlkpPayFrequencyType ON tblHouseholdIncome.PayFrequencyTypeID = tlkpPayFrequencyType.PayFrequencyTypeID;

I have 0 as a placeholder in my annual income right now.

Thank you,

Arthur Lorenzini

Sioux Falls, SD

__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>

Reply via web post

•

Reply to sender

•

Reply to group

•