?
Solved

Access Query question

Posted on 2011-03-02
4
Medium Priority
?
323 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If your vDisk VHD file gets deleted from the image store accidentally or on purpose, you won't be able to remove the vDisk from the PVS console. There is a known workaround that is solid.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
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.
Suggested Courses

762 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