Mark Lewis
asked on
Access Query question
I currently have a query that pulls the info I need form other queries. Here's the query:
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"," [MatFeeFre qCode]",II f([MatFeeF reqCode]=" C",[MatFee FreqCode]& [MatBillCy cle],[MatF eeFreqCode ]&[MatBill Month]
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
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;
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","
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Got it. Thanks
ASKER