subquery or previous record question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Have you tried the DSum() function? You would have it total the desired
field with the date value as desired in the DSum()'s WHERE clause.

Example:
DSum("[FieldName]", "[TableOrQueryName]", "Format([DateField],
""mm/yyyy"")=Format(DateSerial(Year(Date()), Month(Date())-1, Day(Date())),
""mm/yyyy"")")

This will give you the previous month, subtract 2 for 2 months earlier.
 
Thanks wayne,

I tried using the dsum but i think i will need to work on it some more.

However, i had a solution yesterday, but i am stuck at one point. i have a
query (call it query3) which selects from query2 and query2_1 and then i join
these two queries. in order to join them i need to make 4 joins, the first
three are: facility, department, and earncode. these are all inner joins.
then i have a fourth join which is month1 to month2, but this one needs to be
an outer joint. so i don't know how do it ( a message comes up that i need to
create a subquery, but i have no idea how to accomplish this).

following is the SQL:
SELECT Step2.Facility, Step2.Department, Step2.Month1, Step2.EarnCode,
[Step2]![PrevMonth]+[Step2_1]![SecondMonth] AS Total
FROM Step2 INNER JOIN Step2 AS Step2_1 ON (Step2.EarnCode = Step2_1.Month1)
AND (Step2.Month1 = Step2_1.Month2) AND (Step2.Department =
Step2_1.Department) AND (Step2.Facility = Step2_1.Facility);

please help,

thanks,

sam

Wayne Morgan said:
Have you tried the DSum() function? You would have it total the desired
field with the date value as desired in the DSum()'s WHERE clause.

Example:
DSum("[FieldName]", "[TableOrQueryName]", "Format([DateField],
""mm/yyyy"")=Format(DateSerial(Year(Date()), Month(Date())-1, Day(Date())),
""mm/yyyy"")")

This will give you the previous month, subtract 2 for 2 months earlier.

--
Wayne Morgan
MS Access MVP


SAm said:
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
Click to expand...
Click to expand...
 
A subquery would normally be in the WHERE clause. You would pull the Outer
Join out of the joins and instead use those two fields in the WHERE clause.

Example:
WHERE TableName.FieldName IN (SELECT OtherTableName.OtherFieldName FROM
OtherTableName)

add a WHERE clause to the subquery as needed. These can be very slow to run.

--
Wayne Morgan
MS Access MVP


SAm said:
Thanks wayne,

I tried using the dsum but i think i will need to work on it some more.

However, i had a solution yesterday, but i am stuck at one point. i have a
query (call it query3) which selects from query2 and query2_1 and then i
join
these two queries. in order to join them i need to make 4 joins, the first
three are: facility, department, and earncode. these are all inner joins.
then i have a fourth join which is month1 to month2, but this one needs to
be
an outer joint. so i don't know how do it ( a message comes up that i need
to
create a subquery, but i have no idea how to accomplish this).

following is the SQL:
SELECT Step2.Facility, Step2.Department, Step2.Month1, Step2.EarnCode,
[Step2]![PrevMonth]+[Step2_1]![SecondMonth] AS Total
FROM Step2 INNER JOIN Step2 AS Step2_1 ON (Step2.EarnCode =
Step2_1.Month1)
AND (Step2.Month1 = Step2_1.Month2) AND (Step2.Department =
Step2_1.Department) AND (Step2.Facility = Step2_1.Facility);

please help,

thanks,

sam

Wayne Morgan said:
Have you tried the DSum() function? You would have it total the desired
field with the date value as desired in the DSum()'s WHERE clause.

Example:
DSum("[FieldName]", "[TableOrQueryName]", "Format([DateField],
""mm/yyyy"")=Format(DateSerial(Year(Date()), Month(Date())-1,
Day(Date())),
""mm/yyyy"")")

This will give you the previous month, subtract 2 for 2 months earlier.

--
Wayne Morgan
MS Access MVP


SAm said:
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
Click to expand...
Click to expand...
Click to expand...
 
hi Wayne,

thanks for your quick response. i don't know what i was thinking. but i now
made all four joins as outer joins and it works wonderfully. thanks again,
and i am glad i had this resolved.

I like your answers, and i constantly learn something from them. if i didn't
get to apply this over here, i will be applying somewhere else. i am
constantly writing these queries. i need to constantly try to find new ideas.
( i am more of an consultant than a database manager.)

thanks again,

sam

Wayne Morgan said:
A subquery would normally be in the WHERE clause. You would pull the Outer
Join out of the joins and instead use those two fields in the WHERE clause.

Example:
WHERE TableName.FieldName IN (SELECT OtherTableName.OtherFieldName FROM
OtherTableName)

add a WHERE clause to the subquery as needed. These can be very slow to run.

--
Wayne Morgan
MS Access MVP


SAm said:
Thanks wayne,

I tried using the dsum but i think i will need to work on it some more.

However, i had a solution yesterday, but i am stuck at one point. i have a
query (call it query3) which selects from query2 and query2_1 and then i
join
these two queries. in order to join them i need to make 4 joins, the first
three are: facility, department, and earncode. these are all inner joins.
then i have a fourth join which is month1 to month2, but this one needs to
be
an outer joint. so i don't know how do it ( a message comes up that i need
to
create a subquery, but i have no idea how to accomplish this).

following is the SQL:
SELECT Step2.Facility, Step2.Department, Step2.Month1, Step2.EarnCode,
[Step2]![PrevMonth]+[Step2_1]![SecondMonth] AS Total
FROM Step2 INNER JOIN Step2 AS Step2_1 ON (Step2.EarnCode =
Step2_1.Month1)
AND (Step2.Month1 = Step2_1.Month2) AND (Step2.Department =
Step2_1.Department) AND (Step2.Facility = Step2_1.Facility);

please help,

thanks,

sam

Wayne Morgan said:
Have you tried the DSum() function? You would have it total the desired
field with the date value as desired in the DSum()'s WHERE clause.

Example:
DSum("[FieldName]", "[TableOrQueryName]", "Format([DateField],
""mm/yyyy"")=Format(DateSerial(Year(Date()), Month(Date())-1,
Day(Date())),
""mm/yyyy"")")

This will give you the previous month, subtract 2 for 2 months earlier.

--
Wayne Morgan
MS Access MVP


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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top