Solved

Access Query question

Posted on 2011-03-02
4
320 Views
Last Modified: 2012-05-11
I currently have a query that pulls the info I need form other queries. Here's the query:

 
SELECT Employee.EmpName, Matter.MatFeeFreqCode, Matter.MatExpFreqCode, Format([Client.CliCode],"0000") AS CliCode, Client.CliReportingName, Matter.MatReportingName, Format([Matter.MatCode],"0000") AS MatCode, Matter.MatDateLastBill, Matter.MatDateLastPaymt, Matter.MatDateOpened, Matter.MatBillAgreeCode, Sum(GWB_WIP_AR_FEE_EXP_UNION.SumOfUTAmount) AS UnbilledTime, Sum(GWB_WIP_AR_FEE_EXP_UNION.SumOfUEAmount) AS UnbilledExpenses, Sum(GWB_WIP_AR_FEE_EXP_UNION.ARFee) AS FeesAR, Sum(GWB_WIP_AR_FEE_EXP_UNION.ARExp) AS ExpAR, Employee.Group, Matter.MatBillMonth, Matter.MatBillCycle
FROM GWB_WIP_AR_FEE_EXP_UNION INNER JOIN (Employee INNER JOIN ((Client INNER JOIN Matter ON Client.CliSysNbr = Matter.MatCliNbr) INNER JOIN BillTo ON Matter.MatBillTo = BillTo.BillToSysNbr) ON Employee.EmpSysNbr = BillTo.BillToBillingAtty) ON GWB_WIP_AR_FEE_EXP_UNION.SysNum = Matter.MatSysNbr
GROUP BY Employee.EmpName, Matter.MatFeeFreqCode, Matter.MatExpFreqCode, Format([Client.CliCode],"0000"), Client.CliReportingName, Matter.MatReportingName, Format([Matter.MatCode],"0000"), Matter.MatDateLastBill, Matter.MatDateLastPaymt, Matter.MatDateOpened, Matter.MatBillAgreeCode, Employee.Group, Matter.MatBillMonth, Matter.MatBillCycle
ORDER BY Employee.EmpName;

Open in new window


The MatFeeFreq Code only returns M, Q, or C. The MatBillMonth and MatBillCycle returns numbers. I need to create another field on the fly call Freq that will concatenate MatFreqCode with either the MatBillMonth or the MatBillCycle depending on what the value of the freq code is. These three fields are straight out of the Matter table.

I have tried this but it didnt work:

IIf([MatFeeFreqCode]="M","[MatFeeFreqCode]",IIf([MatFeeFreqCode]="C",[MatFeeFreqCode]&[MatBillCycle],[MatFeeFreqCode]&[MatBillMonth]

What I am trying to accomplish is another field called Freq that has the freq code and the either the cycle nymber or the month number depending on the value of the freq code.

Thanks
0
Comment
Question by:gwbmcse
[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
  • 3
4 Comments
 
LVL 19

Accepted Solution

by:
Bardobrave earned 500 total points
ID: 35018136
Try this:

IIf([MatFeeFreqCode]="M",[MatFeeFreqCode],IIf([MatFeeFreqCode]="C",[MatFeeFreqCode]&[MatBillCycle],[MatFeeFreqCode]&[MatBillMonth]))
0
 

Author Comment

by:gwbmcse
ID: 35018155
Would this go at the end of the select statement?
0
 

Author Comment

by:gwbmcse
ID: 35018177
When I tried to do this via design view, running the query gave me a prompt for the MatFeeReqCode rater than reading it from the current record.
0
 

Author Closing Comment

by:gwbmcse
ID: 35018495
Got it. Thanks
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS SQL Update query with connected table data 3 63
SQL Server syntax 11 41
Citrix Xenapp 7.12 Installation 6 28
Converting Stored Procedure to SQL Statement 5 44
CITRIX XENAPP 6.5 FARM CUSTOM POLICY - CHANGE MANAGEMENT WINDOW REBOOT SCHEDULE
Exchange server is not supported in any cloud-hosted platform (other than Azure with Azure Premium Storage).
How to install and configure Citrix XenApp 6.5 - Part 1. In this video tutorial we have explained step by step installation of Citrix XenApp 6.5 Server on Windows Server 2008 R2 is explained in this video. We have explained the difference between…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

726 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