Calculations across records

[Matt]

Hi,

I'm having trouble with a calculation I would like to make. I want to
calculate the difference between a total for this month and a total for
last month. It is easy for me to select the top two records for a
given company using a Select Query, but after that, I do not know what
to do. The aggregate functions do not include one for differences, and
no matter how hard I try, I cannot seem to reference two records from
the same column, in either a query or a form. Access is used for
accounting often, I would think, so there must be a way to do this.
Does anyone know how?


Matt

 

[Michel Walsh]

Hi,


Bring the table in the designer. Make a total query. In a free column:

MyMonth: DateSerial( Year(tableName.DateTime), Month(tableName.DateTime), 1)

Keep the proposed GroupBy.

In another free column, bring the field Amount, and change the GroupBy to SUM.

Save the query, say, Total1




In a new query, bring Total1 twice. Keep it a standard SELECT type query, not a total query.

Bring Total1.MyMonth in the grid, add the criteria: =[Total1_1].[MyMonth] +1

In a new column, type:

MonthDiff: Total1.SumOfAmount - Total1_1.SumOfAmount




Hoping it may help,
Vanderghast, Access MVP

 

[Matt]

Hi,


Bring the table in the designer. Make a total query. In a free column:

MyMonth: DateSerial( Year(tableName.DateTime),
Month(tableName.DateTime), 1)

Keep the proposed GroupBy.

In another free column, bring the field Amount, and change the
GroupBy to SUM.

Save the query, say, Total1




In a new query, bring Total1 twice. Keep it a standard SELECT type
query, not a total query.

Bring Total1.MyMonth in the grid, add the criteria:
=[Total1_1].[MyMonth] +1

In a new column, type:

MonthDiff: Total1.SumOfAmount - Total1_1.SumOfAmount




Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm having trouble with a calculation I would like to make. I want
to calculate the difference between a total for this month and a
total for last month. It is easy for me to select the top two
records for a given company using a Select Query, but after that, I
do not know what to do. The aggregate functions do not include one
for differences, and no matter how hard I try, I cannot seem to
reference two records from the same column, in either a query or a
form. Access is used for accounting often, I would think, so there
must be a way to do this. Does anyone know how?


Matt

Hello Michael and thanks for the help.

I am having difficulty with basically the first part of your
instructions, and I can't for the life of me figure out why. My query
will not accept the DateSerial equation in the form you have given me,
telling me that there is a "Data type mismatch in criteria expression".
I find this odd because there is NO criteria set for the DateSerial
field at all! Here is my expression:
Expr1: DateSerial(Year([Portfolio Information].[Date]),Month([Portfolio
Information].[Date]),1)
I have tried it with exclamation points between the table name and
field name as well, although I don't think that should make a
difference, and it didn't. The Date field is in Short Date format.

Thanks for your consideration.


Matt

 

[Michel Walsh]

Hi,

The [Date] field should be a date_time data type, not a string.


Vanderghast, Access MVP

Hi,


Bring the table in the designer. Make a total query. In a free column:

MyMonth: DateSerial( Year(tableName.DateTime),
Month(tableName.DateTime), 1)

Keep the proposed GroupBy.

In another free column, bring the field Amount, and change the
GroupBy to SUM.

Save the query, say, Total1




In a new query, bring Total1 twice. Keep it a standard SELECT type
query, not a total query.

Bring Total1.MyMonth in the grid, add the criteria:
=[Total1_1].[MyMonth] +1

In a new column, type:

MonthDiff: Total1.SumOfAmount - Total1_1.SumOfAmount




Hoping it may help,
Vanderghast, Access MVP

Hi,

I'm having trouble with a calculation I would like to make. I want
to calculate the difference between a total for this month and a
total for last month. It is easy for me to select the top two
records for a given company using a Select Query, but after that, I
do not know what to do. The aggregate functions do not include one
for differences, and no matter how hard I try, I cannot seem to
reference two records from the same column, in either a query or a
form. Access is used for accounting often, I would think, so there
must be a way to do this. Does anyone know how?


Matt

Hello Michael and thanks for the help.

I am having difficulty with basically the first part of your
instructions, and I can't for the life of me figure out why. My query
will not accept the DateSerial equation in the form you have given me,
telling me that there is a "Data type mismatch in criteria expression".
I find this odd because there is NO criteria set for the DateSerial
field at all! Here is my expression:
Expr1: DateSerial(Year([Portfolio Information].[Date]),Month([Portfolio
Information].[Date]),1)
I have tried it with exclamation points between the table name and
field name as well, although I don't think that should make a
difference, and it didn't. The Date field is in Short Date format.

Thanks for your consideration.


Matt