Solved

IIF(And statement in Access query or SQL

Posted on 2008-10-14
2
504 Views
Last Modified: 2010-04-21
I have 2 tables one is tbl_Charges & the other tbl_Liability.
The tbl_Charges has a primary key for case# column. The table also has a position column.
The tbl_Liability looks like this.
BeginDate      EndDate      Position      Daily_$
7/1/2005      6/30/2006      ISR      $136.63
7/1/2006      6/30/2007      ISR      $140.73
7/1/2007      6/30/2008      ISR      $144.95
7/1/2008      6/30/2009      ISR      $150.75
7/1/2009      6/30/2010      ISR      $157.53
7/1/2005      6/30/2006      ISW      $116.58
7/1/2006      6/30/2007      ISW      $119.77
7/1/2007      6/30/2008      ISW      $123.36
7/1/2008      6/30/2009      ISW      $128.29
7/1/2009      6/30/2010      ISW      $134.06
7/1/2005      6/30/2006      ISW Lift      $127.91
7/1/2006      6/30/2007      ISW Lift      $131.75
7/1/2007      6/30/2008      ISW Lift      $135.70
7/1/2008      6/30/2009      ISW Lift      $141.12
7/1/2009      6/30/2010      ISW Lift      $147.47
7/1/2005      6/30/2006      ISR N      $143.42
7/1/2006      6/30/2007      ISR N      $147.75
7/1/2007      6/30/2008      ISR N      $152.15
7/1/2008      6/30/2009      ISR N      $158.24
7/1/2009      6/30/2010      ISR N      $165.36
7/1/2005      6/30/2010      Unknown      $0.00

The only unique identifier in this table is the Liability$.
I need a query that will look at the tbl_Charges, columns Position & Date.
If tbl_Charges!Date is between tbl_Liability!BeginDate And tbl_Liability!EndDate and if tbl_Charges!Position = tbl_Liability!Position.
Output should be If the date of charge is 10/1/2008 and the position is an ISR, it would display $150.75
0
Comment
Question by:HA94694
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 45

Accepted Solution

by:
aikimark earned 350 total points
ID: 22715375
No IIF required.

Select [Daily_$]
From tbl_Liability INNER JOIN tbl_Charges ON tbl_Charges.Position = tbl_Liability.Position
Where tbl_Charges.ChargeDate Between tbl_Liability.BeginDate And tbl_Liability.EndDate
0
 

Author Closing Comment

by:HA94694
ID: 31506035
Absolutely perfect. I so did not think of that, obviously. I had tried everything else.
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

730 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