How the #@!! do I do this?

[Guest]

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and subtract
the first month of the quarter from it. In other words, using the sample data
above, a query that subtracts July's amount from September's. And, I need
four of these queries - one for each quarter (unless it can be done in one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[Ken Snell \(MVP\)]

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

 

[Guest]

What Ken Snell said would work for the short term. A better solution would be
just to get the amount for that month instead of the YTD total. That way you
could do a lot more with the data easier. You could easily compute the YTD
total as needed plus group data by month, quarter, or year. For example what
happens to the data when you get to 2007?

 

[Ken Snell \(MVP\)]

This query's statement will work within one calendar year. Do you need to be
able to span years? If yes, then the query would need to be modified.... how
to modify it will depend upon your data for multiple years. Post back if you
are interested in spanning years and include data that show what multiple
years' data would look like for the source data.

--

Ken Snell
<MS ACCESS MVP>

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and subtract
the first month of the quarter from it. In other words, using the sample
data
above, a query that subtracts July's amount from September's. And, I need
four of these queries - one for each quarter (unless it can be done in
one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[Guest]

Thanks for your response ... it kinda works, but not quite. Here are the
actual amounts for 2006 - this might help you understand my problem a bit
better.

Jan thru Feb - $0 each month
May - $2,541,810
June - $6,091,231
July thru October - $0 each month

So for the month of May I need the query to tell me that May's total was
$2,541,810, and for June I need the query to tell me that June's total was
$3,549,421. Does that help?

Also, I do not need the query to span multiple years. In January of each
year, we archive the current database with all its data intact, then using
another copy of the database we delete all the data and start fresh. So in
January of each year I need the query to concentrate only on the previous
year, but in February we will be focusing solely on the current year.

I hope I'm making sense!

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and subtract
the first month of the quarter from it. In other words, using the sample
data
above, a query that subtracts July's amount from September's. And, I need
four of these queries - one for each quarter (unless it can be done in one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[Ken Snell \(MVP\)]

The query's SQL statement that I posted should give you what you seek,
assuming that you have the YTD field in the original table or query.

How about you post the exact query SQL that you tried, and the output
results from that query, so that we can see what you mean by "kinda works"?

--

Ken Snell
<MS ACCESS MVP>

Thanks for your response ... it kinda works, but not quite. Here are the
actual amounts for 2006 - this might help you understand my problem a bit
better.

Jan thru Feb - $0 each month
May - $2,541,810
June - $6,091,231
July thru October - $0 each month

So for the month of May I need the query to tell me that May's total was
$2,541,810, and for June I need the query to tell me that June's total was
$3,549,421. Does that help?

Also, I do not need the query to span multiple years. In January of each
year, we archive the current database with all its data intact, then using
another copy of the database we delete all the data and start fresh. So in
January of each year I need the query to concentrate only on the previous
year, but in February we will be focusing solely on the current year.

I hope I'm making sense!

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and
subtract
the first month of the quarter from it. In other words, using the
sample
data
above, a query that subtracts July's amount from September's. And, I
need
four of these queries - one for each quarter (unless it can be done in
one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[Guest]

Sorry, I don't get the data that way. I get a report from our corporate
office in another state that only has one number - the current YTD total.
That's why I have to track the YTD total for each month and compute the
difference between last month and the previous month.

 

[Guest]

Here's my SQL:

SELECT T.*, (SELECT (TT.amountYTD-T.amountYTD) AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS QtrDiff
FROM leasings AS T
ORDER BY T.Month;

And here's the result I'm getting:

May ($2,541,810.00) - this is correct
June ($6,091,231.00) - this needs to be $3,549,421

Thanks!

The query's SQL statement that I posted should give you what you seek,
assuming that you have the YTD field in the original table or query.

How about you post the exact query SQL that you tried, and the output
results from that query, so that we can see what you mean by "kinda works"?

--

Ken Snell
<MS ACCESS MVP>

Thanks for your response ... it kinda works, but not quite. Here are the
actual amounts for 2006 - this might help you understand my problem a bit
better.

Jan thru Feb - $0 each month
May - $2,541,810
June - $6,091,231
July thru October - $0 each month

So for the month of May I need the query to tell me that May's total was
$2,541,810, and for June I need the query to tell me that June's total was
$3,549,421. Does that help?

Also, I do not need the query to span multiple years. In January of each
year, we archive the current database with all its data intact, then using
another copy of the database we delete all the data and start fresh. So in
January of each year I need the query to concentrate only on the previous
year, but in February we will be focusing solely on the current year.

I hope I'm making sense!

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and
subtract
the first month of the quarter from it. In other words, using the
sample
data
above, a query that subtracts July's amount from September's. And, I
need
four of these queries - one for each quarter (unless it can be done in
one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[John Spencer]

Your expected results seem to involve the prior month (May), not the
beginning of the quarter (April). The beginning of quarter containing June
is April which seems to be Zero from what I can tell from your posting.

SELECT T.*
, TT.AmountYTD -
(SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS QtrDiff
FROM leasings AS T
ORDER BY T.Month;


You might want to set up the query (temporarily) as the following so you can
trouble shoot.
SELECT T.*
, T.AmountYTD - (SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS qtrDiff
, (SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS
QuarterStartValue
FROM leasings AS T
ORDER BY T.Month;

Here's my SQL:

SELECT T.*, (SELECT (TT.amountYTD-T.amountYTD) AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS QtrDiff
FROM leasings AS T
ORDER BY T.Month;

And here's the result I'm getting:

May ($2,541,810.00) - this is correct
June ($6,091,231.00) - this needs to be $3,549,421

Thanks!

The query's SQL statement that I posted should give you what you seek,
assuming that you have the YTD field in the original table or query.

How about you post the exact query SQL that you tried, and the output
results from that query, so that we can see what you mean by "kinda
works"?

--

Ken Snell
<MS ACCESS MVP>

Thanks for your response ... it kinda works, but not quite. Here are
the
actual amounts for 2006 - this might help you understand my problem a
bit
better.

Jan thru Feb - $0 each month
May - $2,541,810
June - $6,091,231
July thru October - $0 each month

So for the month of May I need the query to tell me that May's total
was
$2,541,810, and for June I need the query to tell me that June's total
was
$3,549,421. Does that help?

Also, I do not need the query to span multiple years. In January of
each
year, we archive the current database with all its data intact, then
using
another copy of the database we delete all the data and start fresh. So
in
January of each year I need the query to concentrate only on the
previous
year, but in February we will be focusing solely on the current year.

I hope I'm making sense!

:

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and
subtract
the first month of the quarter from it. In other words, using the
sample
data
above, a query that subtracts July's amount from September's. And, I
need
four of these queries - one for each quarter (unless it can be done
in
one
query).

Many thanks for any ideas and pseudocode you are willing to share.

 

[Guest]

Boy, do I feel stupid! You're right, the query was working correctly to begin
with. Duh! My apologies.

I've got this rat's nest comprised of 2 databases, about 50 tables each, and
about 500 queries each, that I'm trying to combine, refine, make it conform
to best practices, etc. by creating a new database. The only problem is, I'm
working with so many different queries that I get confused about what I want
the end result to be.

Sorry!

Your expected results seem to involve the prior month (May), not the
beginning of the quarter (April). The beginning of quarter containing June
is April which seems to be Zero from what I can tell from your posting.

SELECT T.*
, TT.AmountYTD -
(SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS QtrDiff
FROM leasings AS T
ORDER BY T.Month;


You might want to set up the query (temporarily) as the following so you can
trouble shoot.
SELECT T.*
, T.AmountYTD - (SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS qtrDiff
, (SELECT TT.amountYTD AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS
QuarterStartValue
FROM leasings AS T
ORDER BY T.Month;

Here's my SQL:

SELECT T.*, (SELECT (TT.amountYTD-T.amountYTD) AS TQtrDiff
FROM leasings AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1))) AS QtrDiff
FROM leasings AS T
ORDER BY T.Month;

And here's the result I'm getting:

May ($2,541,810.00) - this is correct
June ($6,091,231.00) - this needs to be $3,549,421

Thanks!

The query's SQL statement that I posted should give you what you seek,
assuming that you have the YTD field in the original table or query.

How about you post the exact query SQL that you tried, and the output
results from that query, so that we can see what you mean by "kinda
works"?

--

Ken Snell
<MS ACCESS MVP>


Thanks for your response ... it kinda works, but not quite. Here are
the
actual amounts for 2006 - this might help you understand my problem a
bit
better.

Jan thru Feb - $0 each month
May - $2,541,810
June - $6,091,231
July thru October - $0 each month

So for the month of May I need the query to tell me that May's total
was
$2,541,810, and for June I need the query to tell me that June's total
was
$3,549,421. Does that help?

Also, I do not need the query to span multiple years. In January of
each
year, we archive the current database with all its data intact, then
using
another copy of the database we delete all the data and start fresh. So
in
January of each year I need the query to concentrate only on the
previous
year, but in February we will be focusing solely on the current year.

I hope I'm making sense!

:

SELECT T.*,
(SELECT (TT.YTD-T.YTD) AS TQtrDiff
FROM TableName AS TT
WHERE Month(TT.[Month]) =
Month(T.[Month])+(Choose((Month(T.[Month]) Mod 3)+1,-2,0,-1)))
AS QtrDiff
FROM TableName AS T;

--

Ken Snell
<MS ACCESS MVP>

I have a table that gets a new YTD entry once a month. Sample data:

Officer# OfficerName Month YTD
100 Jane Doe 7/31/06 $6,091,022
100 Jane Doe 8/31/06 $6,091,023
100 Jane Doe 9/30/06 $6,091,024

I need a query that will take the most recent month's amount and
subtract
the first month of the quarter from it. In other words, using the
sample
data
above, a query that subtracts July's amount from September's. And, I
need
four of these queries - one for each quarter (unless it can be done
in
one
query).

Many thanks for any ideas and pseudocode you are willing to share.