S
SAm
Hi
i need to be able to total column c (total cost) in one record and column d
(totalPREVIOUScost) of the previous record. so lets say i am now in january
2005, i need total(column c of January 2005 + Column d of December 2004).
what is the best way to refer to that previous record. should i create a
subquery? or can i somehow call the previous record.
i will state my queries, and i know that they are almost not understood.
Query1
SELECT Trim([OrgLvl1]) AS Facility, Trim([JobCode]) AS Department,
PeriodStartDate, Trim([EarnCode]) AS EarnCode, CurAmt,
IIf(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0))<14,(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0)))/14,1)
AS ThisMonth,
IIf(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0))<14,(14-DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0)))/14) AS nextMonth
FROM Earn INNER JOIN Pay ON Earn.GenNumber = PayGenNumber;
Query2
SELECT Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]) AS [Month], EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate])) AS Month2, Sum([CurAmt]*[ThisMonth])
AS PrevMonth, Sum([CurAmt]*[nextMonth]) AS SecondMonth
FROM FirstTable
GROUP BY Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]), EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate]))
ORDER BY Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]), EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate]));
i need to total of PrevMonth for a given month plus SecondMonth of the
previous month.
thanks,
sam
i need to be able to total column c (total cost) in one record and column d
(totalPREVIOUScost) of the previous record. so lets say i am now in january
2005, i need total(column c of January 2005 + Column d of December 2004).
what is the best way to refer to that previous record. should i create a
subquery? or can i somehow call the previous record.
i will state my queries, and i know that they are almost not understood.
Query1
SELECT Trim([OrgLvl1]) AS Facility, Trim([JobCode]) AS Department,
PeriodStartDate, Trim([EarnCode]) AS EarnCode, CurAmt,
IIf(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0))<14,(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0)))/14,1)
AS ThisMonth,
IIf(DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0))<14,(14-DateDiff("y",[PeriodStartDate],DateSerial(Year([PeriodStartDate]),Month([PeriodStartDate])+1,0)))/14) AS nextMonth
FROM Earn INNER JOIN Pay ON Earn.GenNumber = PayGenNumber;
Query2
SELECT Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]) AS [Month], EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate])) AS Month2, Sum([CurAmt]*[ThisMonth])
AS PrevMonth, Sum([CurAmt]*[nextMonth]) AS SecondMonth
FROM FirstTable
GROUP BY Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]), EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate]))
ORDER BY Facility, Department, MonthName(Month([PeriodStartDate])) & ", " &
Year([PeriodStartDate]), EarnCode,
MonthName(Month(DateAdd("m",1,[PeriodStartDate]))) & ", " &
Year(DateAdd("m",1,[PeriodStartDate]));
i need to total of PrevMonth for a given month plus SecondMonth of the
previous month.
thanks,
sam