subquery or previous record question

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
 
W

Wayne Morgan

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.
 
G

Guest

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...
 
W

Wayne Morgan

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem with dates in a DAvg query 8
How can i pass a date variable to this Append SQL 1
Returning zero in a query? 3
MonthName to Integer from Current to Previous Year 3
value displayed repeatedly 9
End of Month: Parameter Query? 3
Calc Date Years Month Days from DOB to DOD 2
Running sum expression 2

Top