Greater than formula

  • Thread starter Thread starter naja
  • Start date Start date
N

naja

Hi! I need to generate an Access report that shows me information from two
table.
One table has LM (last month)information from a field called [processed
amount] and the other table which TM (this month) information has the same
field name [processed amount]. I need to show whether there was an increased
or decreased on the charges this month vs. last month. Both table have in
common the account #. Can you let me know how to go about and do that. Thanks
 
You need to join the 2 tables to create a result set containing both columns.
SELECT LM.Account, LM.Amount, TM.Amount
FROM LM INNER JOIN TM
ON LM.Account = TM.Account
This assumes each account will have amounts in both months.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
Hi! I need to generate an Access report that shows me information from two
table.
One table has LM (last month)information from a field called [processed
amount] and the other table which TM (this month) information has the same
field name [processed amount]. I need to show whether there was an increased
or decreased on the charges this month vs. last month. Both table have in
common the account #. Can you let me know how to go about and do that. Thanks
Click to expand...

Create a Query by adding the two tables to the query grid. Join them by the
account number. You can then create a calculated field in the query, or set
the control source of a textbox on the report to an expression subtracting the
two fields' values.

If you have a different table each month, your database design is *WRONG* and
will require vastly more work than if you had one table with a date/time field
indicating the date of the processing.
 
Hi John! Thanks for your reply.

But I need to put some sort of statement that when I get my answer on
whether was more than states that. for example if last month was $100 greater
then the end result should be >$100.00 and if it was less then <$100.00. or
just something like $100 greater than LM, etc, etc. Thanks for your help.




John W. Vinson said:
Hi! I need to generate an Access report that shows me information from two
table.
One table has LM (last month)information from a field called [processed
amount] and the other table which TM (this month) information has the same
field name [processed amount]. I need to show whether there was an increased
or decreased on the charges this month vs. last month. Both table have in
common the account #. Can you let me know how to go about and do that. Thanks
Click to expand...

Create a Query by adding the two tables to the query grid. Join them by the
account number. You can then create a calculated field in the query, or set
the control source of a textbox on the report to an expression subtracting the
two fields' values.

If you have a different table each month, your database design is *WRONG* and
will require vastly more work than if you had one table with a date/time field
indicating the date of the processing.
Click to expand...
 
Hi John! Thanks for your reply.

But I need to put some sort of statement that when I get my answer on
whether was more than states that. for example if last month was $100 greater
then the end result should be >$100.00 and if it was less then <$100.00. or
just something like $100 greater than LM, etc, etc. Thanks for your help.
Click to expand...

You'll have to rephrase that request. I simply don't understand what you're
asking. "when I get my answer on whether"...

You can do the subtraction of the value in LM from the value in CM. You'll
have a number. You can report it however you like. Could you post a message
with sample values and what you would like to see?
 
John,

I apologized for not explaining myself clear: let see if now is more clear.

What I want is being able to calculate the difference between TM and LM and
that my formula result say something like this:

TM$ - LM$ = $25 increased

or

TM$ - LM$ = $25 decreased

Which ever is the situation of my final result, I need that my end result
shows the $ amount with either one of those two statments, increased if the
charges went up or decreased if the charges went down for TM.

By the way these two table have account # as the common name. The field
name is [processed amount]. thanks
 
John,

I apologized for not explaining myself clear: let see if now is more clear.

What I want is being able to calculate the difference between TM and LM and
that my formula result say something like this:

TM$ - LM$ = $25 increased

or

TM$ - LM$ = $25 decreased

Which ever is the situation of my final result, I need that my end result
shows the $ amount with either one of those two statments, increased if the
charges went up or decreased if the charges went down for TM.

By the way these two table have account # as the common name. The field
name is [processed amount]. thanks
Click to expand...

Try adding the two tables to the query grid. In a vacant Field cell type

Change: Abs([TM].[processed amount] - [LM].[processed amoung]) &
Switch([TM].[Processed amount]) > [LM].[Processed amount], " increased",
[TM].[Processed amount]) < [LM].[Processed amount], " decreased",
[TM].[Processed amount]) = [LM].[Processed amount], " no change",
True, " ERROR IN THE DATA!")
 
Hi John,

Thanks for your help, however, i'm getting this error!
"The expression you entered contains invalid syntax or you need to enclose
your text data in quotes" I checked the formula and everything looks good.
let me know. Thx

John W. Vinson said:
John,

I apologized for not explaining myself clear: let see if now is more clear.

What I want is being able to calculate the difference between TM and LM and
that my formula result say something like this:

TM$ - LM$ = $25 increased

or

TM$ - LM$ = $25 decreased

Which ever is the situation of my final result, I need that my end result
shows the $ amount with either one of those two statments, increased if the
charges went up or decreased if the charges went down for TM.

By the way these two table have account # as the common name. The field
name is [processed amount]. thanks
Click to expand...

Try adding the two tables to the query grid. In a vacant Field cell type

Change: Abs([TM].[processed amount] - [LM].[processed amoung]) &
Switch([TM].[Processed amount]) > [LM].[Processed amount], " increased",
[TM].[Processed amount]) < [LM].[Processed amount], " decreased",
[TM].[Processed amount]) = [LM].[Processed amount], " no change",
True, " ERROR IN THE DATA!")
Click to expand...
 
Hi John,

Thanks for your help, however, i'm getting this error!
"The expression you entered contains invalid syntax or you need to enclose
your text data in quotes" I checked the formula and everything looks good.
let me know. Thx
Click to expand...

Please post the actual SQL view of the query.
 
Back
Top