Adding Calculated Field to Existing Query

[wildsyp]

Hi,

I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?

The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.

Any help would be appreciated.

 

[pr2-eugin]

There is an easy way to do this. But, if you would be able to explain what is the calculation and maybe show us the query some of us might be able to help.

 

[wildsyp]

Hi, this is the code:

I would like to add Sum(IIf([Tbl_AbsenceMain]![01/10/2014]>0,1,0)) AS InstOct2014 to the end.

SELECT Tbl_CostCodes.[Opex Level 1], Tbl_CostCodes.[Opex Level 2], Tbl_CostCodes.[Opex BGX], Tbl_CombinedEmployeeData.LM_Payroll, Tbl_CombinedEmployeeData.LM_Name, Sum(IIf([Tbl_AbsenceMain]![01/07/2012]>0,1,0)) AS InstJul2012, Sum(IIf([Tbl_AbsenceMain]![01/08/2012]>0,1,0)) AS InstAug2012, Sum(IIf([Tbl_AbsenceMain]![01/09/2012]>0,1,0)) AS InstSep2012, Sum(IIf([Tbl_AbsenceMain]![01/10/2012]>0,1,0)) AS InstOct2012, Sum(IIf([Tbl_AbsenceMain]![01/11/2012]>0,1,0)) AS InstNov2012, Sum(IIf([Tbl_AbsenceMain]![01/12/2012]>0,1,0)) AS InstDec2012, Sum(IIf([Tbl_AbsenceMain]![01/01/2013]>0,1,0)) AS InstJan2013, Sum(IIf([Tbl_AbsenceMain]![01/02/2013]>0,1,0)) AS InstFeb2013, Sum(IIf([Tbl_AbsenceMain]![01/03/2013]>0,1,0)) AS InstMar2013, Sum(IIf([Tbl_AbsenceMain]![01/04/2013]>0,1,0)) AS InstApr2013, Sum(IIf([Tbl_AbsenceMain]![01/05/2013]>0,1,0)) AS InstMay2013, Sum(IIf([Tbl_AbsenceMain]![01/06/2013]>0,1,0)) AS InstJun2013, Sum(IIf([Tbl_AbsenceMain]![01/07/2013]>0,1,0)) AS InstJul2013, Sum(IIf([Tbl_AbsenceMain]![01/08/2013]>0,1,0)) AS InstAug2013, Sum(IIf([Tbl_AbsenceMain]![01/09/2013]>0,1,0)) AS InstSep2013, Sum(IIf([Tbl_AbsenceMain]![01/10/2013]>0,1,0)) AS InstOct2013, Sum(IIf([Tbl_AbsenceMain]![01/11/2013]>0,1,0)) AS InstNov2013, Sum(IIf([Tbl_AbsenceMain]![01/12/2013]>0,1,0)) AS InstDec2013, Sum(IIf([Tbl_AbsenceMain]![01/01/2014]>0,1,0)) AS InstJan2014, Sum(IIf([Tbl_AbsenceMain]![01/02/2014]>0,1,0)) AS InstFeb2014, Sum(IIf([Tbl_AbsenceMain]![01/03/2014]>0,1,0)) AS InstMar2014, Sum(IIf([Tbl_AbsenceMain]![01/04/2014]>0,1,0)) AS InstApr2014, Sum(IIf([Tbl_AbsenceMain]![01/05/2014]>0,1,0)) AS InstMay2014, Sum(IIf([Tbl_AbsenceMain]![01/06/2014]>0,1,0)) AS InstJun2014, Sum(IIf([Tbl_AbsenceMain]![01/07/2014]>0,1,0)) AS InstJul2014, Sum(IIf([Tbl_AbsenceMain]![01/08/2014]>0,1,0)) AS InstAug2014, Sum(IIf([Tbl_AbsenceMain]![01/09/2014]>0,1,0)) AS InstSep2014, Sum(IIf([Tbl_AbsenceMain]![01/10/2014]>0,1,0)) AS InstOct2014
FROM (Tbl_CombinedEmployeeData LEFT JOIN Tbl_AbsenceMain ON Tbl_CombinedEmployeeData.PersNo = Tbl_AbsenceMain.[Employee Number]) LEFT JOIN Tbl_CostCodes ON Tbl_CombinedEmployeeData.[Cost Ctr] = Tbl_CostCodes.[Cost Code]
WHERE (((Tbl_CostCodes.[Opex Level 1]) Is Not Null) AND ((Tbl_AbsenceMain.Type)<>"Cancel" Or (Tbl_AbsenceMain.Type) Is Null))
GROUP BY Tbl_CostCodes.[Opex Level 1], Tbl_CostCodes.[Opex Level 2], Tbl_CostCodes.[Opex BGX], Tbl_CombinedEmployeeData.LM_Payroll, Tbl_CombinedEmployeeData.LM_Name;

 

[pr2-eugin]

Have you not already added that? You query has that column !

 

[wildsyp]

Ha, sorry!

Meant to put: Sum(IIf([Tbl_AbsenceMain]![01/11/2014]>0,1,0)) AS InstNov2014

 

[pr2-eugin]

I am sorry I am a bit confused here. So what seems to be the problem? You just have to add that column that's it? You have managed to add all the others, why is this a problem?

 

[wildsyp]

I have been adding these manually which is fiddly and time consuming. I need some code which will add the new column onto the end without opening the query and making the change myself. I will be handing this over to non-techy people who won't have a clue about amending queries etc.

Thanks
Paul