Solved

Access Query question

Posted on 2011-03-02
4
317 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
  • 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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Citrix XenApp, Internet Explorer 11 set to Enterprise Mode and using central hosted sites.xml file.
#Citrix #Internet Explorer #Enterprise Mode #IE 11 #IE 8
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.

757 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

20 Experts available now in Live!

Get 1:1 Help Now