Access Query question

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
Mark LewisAsked:
Who is Participating?
 
BardobraveConnect With a Mentor Commented:
Try this:

IIf([MatFeeFreqCode]="M",[MatFeeFreqCode],IIf([MatFeeFreqCode]="C",[MatFeeFreqCode]&[MatBillCycle],[MatFeeFreqCode]&[MatBillMonth]))
0
 
Mark LewisAuthor Commented:
Would this go at the end of the select statement?
0
 
Mark LewisAuthor Commented:
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
 
Mark LewisAuthor Commented:
Got it. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.