Calculate Difference between 2 tables Plus/Minus 10%

[Hatman50]

I was never very good at math. Can someone please offer some advice.
My high school math teacher would be very dissapointed in me.

I am trying to compare two tables that essentially have the same info.
One table is Yesterdays Data and the other is Todays. They each have
the same account numbers in one field and a Dollar value in the other.
I want to compare the two and report if there is a diference of plus
or minus 10 % for each account.

At some point down the road I will want to have the ability to change
the "plus or minus10%" dynamically, maybe via a form or input box, but
that is not the requirement at this point.

Thanks for an help.

 

[Reiko]

I was never very good at math. Can someone please offer some advice.
My high school math teacher would be very dissapointed in me.

I am trying to compare two tables that essentially have the same info.
One table is Yesterdays Data and the other is Todays. They each have
the same account numbers in one field and a Dollar value in the other.
I want to compare the two and report if there is a diference of plus
or minus 10 % for each account.

At some point down the road I will want to have the ability to change
the "plus or minus10%" dynamically, maybe via a form or input box, but
that is not the requirement at this point.

Thanks for an help.

Hello, you could use a function like this:

=IF(ABS(B1-A1)>.1*A1,"10% or more difference","Less than 10%
difference")

Where B1 should be replaced by the cell reference to the "today"
value, and A1 should be replaced by the cell reference to the
"yesterday" value. The "10% or more difference" and "Less than 10%
difference" can be replaced by whatever response you want (in quotes
still) for when the difference is more than 10% or not, respectively.
The formula calculates the absolute value of the difference and
compares it to 10% of yesterday's value. If you want to differentiate
whether the difference is 10% greater or 10% less, you'd need a nested
if statement like this one:

=IF((B1-A1)>.1*A1,"10% or more increase",IF((A1-B1)>.1*A1,"10% or more
decrease","Less than 10% difference"))

If you want to change the threshold value from 10% to some other
value, you'd just need to change the .1 value in the formula to a
different decimal representing the percentage you want. You could even
have that value refer to a different cell that holds the value so you
could change it dynamically without worrying about the formula. In
that case, particularly if you're copying the formula to multiple
cells, you'd want an absolute reference for that cell, like this:

=IF(ABS(B1-A1)>$C$1*A1,$C$1*100 & "% or more difference","Less than "
& $C$1*100 & "% difference")

where $C$1 is the absolute reference to cell C1, and the strings have
been modified to include the value of C1 in percent form dynamically.

Hope this helps,

~Reiko

 

[Jeff Boyce]

I may be reading too much into your post -- if so, my apologies for the
following (unnecessary) rant ...

If your database actually has two table, essentially identical in structure,
one with "yesterday's" data and one with "today's", you have a spreadsheet,
not a relational database! So what, you ask?

So if you want to get good use out of Access, you need to feed it the kind
of data it expects, not 'sheet data.

And if you want to NOT have to be continually updating the contents of those
tables, ...

You posted in the newsgroup supporting queries -- good choice! If you had a
single table with your data, and a date/time stamp field, you could create
two queries, one that (dynamically) grabs "yesterday's" data and one that
(dynamically) grabs "today's". Then you could use a third query to compare
those two...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Bob Barrows]

I was never very good at math. Can someone please offer some advice.
My high school math teacher would be very dissapointed in me.

I am trying to compare two tables that essentially have the same info.
One table is Yesterdays Data and the other is Todays. They each have
the same account numbers in one field and a Dollar value in the other.
I want to compare the two and report if there is a diference of plus
or minus 10 % for each account.

At some point down the road I will want to have the ability to change
the "plus or minus10%" dynamically, maybe via a form or input box, but
that is not the requirement at this point.

More information please. Table and field names and data types at the least.
The version of Access you are using also is a good thing to provide.
It is always helpful to provide a few rows of sample data from each table
(in tabular format) along with the results you would wish to obtain from
that sample data (again in tabular format).
That would help answer my main question: are there multiple entries that
need to be aggregated for each account in each table?

PS. I concur with Jeff that you have too many tables in your design. A
single table is surely called for here. Why force yourself to go to the
added (and completely unnecessary) work of creating new tables every day?

 

[Hatman50]

I may be reading too much into your post -- if so, my apologies for the
following (unnecessary) rant ...

If your database actually has two table, essentially identical in structure,
one with "yesterday's" data and one with "today's", you have a spreadsheet,
not a relational database!  So what, you ask?

So if you want to get good use out of Access, you need to feed it the kind
of data it expects, not 'sheet data.

And if you want to NOT have to be continually updating the contents of those
tables, ...

You posted in the newsgroup supporting queries -- good choice!  If you had a
single table with your data, and a date/time stamp field, you could create
two queries, one that (dynamically) grabs "yesterday's" data and one that
(dynamically) grabs "today's".  Then you could use a third query to compare
those two...

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.









- Show quoted text -

Good points Jeff, and I appreciate them: Let me give you a brief
background...
I am working in an environment that is using Access not so much as a
“Database†but as a vehicle to use VBA which allows us to create user
forms and add all kinds of VBA code to accomplish their desires. I’m
likely more than sure that there are probably better ways to achieve
what needs to be accomplished; but because of their/our familiarity
with VBA & Access and it’s ability to develop “End User†applications;
it seems to be a good medium.

With regards to this particular situation, I am importing from txt
files that are emailed to us, “Yesterdays AND Today’s†data, and then
i want to performing the comparison. I plan to accomplish this
importing via VBA, which is assigned to a button on a Form. So
“Database structure†really isn’t coming into play,at least not “AT
THIS POINT ANYWAYâ€, as I’ve explained to management.

However, if I understand what you are suggesting, it is that I should
be putting ALL the data into ONE table?! That confuses me… (howeverit
is after the Friday Lunch hourïŠ).

Any advice or questions re accomplishing this task are welcome.
I'm rather confident that it can be done; it's just a matter of HOW
and Best practices.
Thanks very much.

 

[Jeff Boyce]

I'm with Bob on the point of why you are deleting data. I still don't
understand how doing so is helping with the task at hand.

Do those text files include a field holding a date (or date/time) value? If
so, you are 3/4 of the way there!

If not, your import process will need to add a date (or date/time) value
into the table into which you are importing the data (i.e., when did this
come in?).

As for "how", you can think of "yesterday" as "whatever today is, minus one
day". Or, in Access, Date() - 1.

And you can think of "today" as Date() (i.e., the date today).

So you absolutely do NOT need to categorize entries as "yesterday's" or
"today's", then delete them and start over. You can use a date (date/time)
field in your table and return (via query) only those with a value in that
field = Date() (or = Date() -1).

As for one table, since you don't need to keep separate piles of yesterday's
and today's, a single table (with a date field) is all it takes.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.


Good points Jeff, and I appreciate them: Let me give you a brief
background...
I am working in an environment that is using Access not so much as a
"Database" but as a vehicle to use VBA which allows us to create user
forms and add all kinds of VBA code to accomplish their desires. I'm
likely more than sure that there are probably better ways to achieve
what needs to be accomplished; but because of their/our familiarity
with VBA & Access and it's ability to develop "End User" applications;
it seems to be a good medium.

With regards to this particular situation, I am importing from txt
files that are emailed to us, "Yesterdays AND Today's" data, and then
i want to performing the comparison. I plan to accomplish this
importing via VBA, which is assigned to a button on a Form. So
"Database structure" really isn't coming into play, at least not "AT
THIS POINT ANYWAY", as I've explained to management.

However, if I understand what you are suggesting, it is that I should
be putting ALL the data into ONE table?! That confuses me. (however it
is after the Friday Lunch hour?).

Any advice or questions re accomplishing this task are welcome.
I'm rather confident that it can be done; it's just a matter of HOW
and Best practices.
Thanks very much.

 

[Hatman50]

I am using Access 2003
The two text files are emailed to us Daily. (sample data below)
I’ve come to understand Jeff’s point that I only need one table.
In fact as I’ve explained to the requester of this process, I only
need to import TODAYS data as I will already have imported Yesterdays
data.
Makes total sense now.

To answer your question Bob, about aggregation;
Not in this example, BUT in another process, YES there will be
multiple records for the same account.
I’m guessing that I’ll use a Totals query with a GroupBy for that
process…

Sample Data Follows...

TalCash_Wed.txt
"H","TALACCASHD","TALCASH.WED","2010-06-09"," 41:57"," "
"A401611","CDN",95477.66,20100609
"A401835","CDN",28804.85,20100609
"A402353","CDN",27336.47,20100609
"0103200","CDN",105.47,20100609
"0103218","CDN",428.71,20100609
"0103234","USA",979.99,20100609
"0112292","CDN",5051.39,20100609

TalCash_Thu.txt
"H","TALACCASHD","TALCASH.THU","2010-06-10"," 1:07:55"," "
"A401611","CDN",95477.66,20100610
"A401835","CDN",28804.85,20100610
"A402353","CDN",27336.47,20100610
"0103200","CDN",105.47,20100610
"0103218","CDN",428.71,20100610
"0103234","USA",1080.79,20100610
"0112292","CDN",5051.39,20100610

So now I have ONE table with a DATE field. I’ve used an Import
Specification.
I understand how to find the records for Today and Yesterday using
Date() and Date()-1
I’m still confused as to how to find out if the difference between
Today and Yesterday is greater than 10% Plus or Minus.
And how convoluted is the code/logic going to be to compare Monday to
Friday, Never mind Holidays
I may have gotten in over my head on this one. :{

 

[Jeff Boyce]

If you have two queries against that table, one for "today's" and one for
"yesterday's", you can create a third query that uses the first two as their
"tables", do the subtraction, and test in the query for a difference greater
than 10%.

There would be multiple ways to do this test, so here's (only) one:

In the Field, calculate the ratio between Query1-Value and Query2-Value. In
the Selection Criterion beneath this new "field", put something like
(untested):

1.1

If you want to cover a CHANGE of 10% or more, put:

1.1 or < .9

Does that get you closer?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

I am using Access 2003
The two text files are emailed to us Daily. (sample data below)
I’ve come to understand Jeff’s point that I only need one table.
In fact as I’ve explained to the requester of this process, I only
need to import TODAYS data as I will already have imported Yesterdays
data.
Makes total sense now.

To answer your question Bob, about aggregation;
Not in this example, BUT in another process, YES there will be
multiple records for the same account.
I’m guessing that I’ll use a Totals query with a GroupBy for that
process…

Sample Data Follows...

TalCash_Wed.txt
"H","TALACCASHD","TALCASH.WED","2010-06-09"," 41:57"," "
"A401611","CDN",95477.66,20100609
"A401835","CDN",28804.85,20100609
"A402353","CDN",27336.47,20100609
"0103200","CDN",105.47,20100609
"0103218","CDN",428.71,20100609
"0103234","USA",979.99,20100609
"0112292","CDN",5051.39,20100609

TalCash_Thu.txt
"H","TALACCASHD","TALCASH.THU","2010-06-10"," 1:07:55"," "
"A401611","CDN",95477.66,20100610
"A401835","CDN",28804.85,20100610
"A402353","CDN",27336.47,20100610
"0103200","CDN",105.47,20100610
"0103218","CDN",428.71,20100610
"0103234","USA",1080.79,20100610
"0112292","CDN",5051.39,20100610

So now I have ONE table with a DATE field. I’ve used an Import
Specification.
I understand how to find the records for Today and Yesterday using
Date() and Date()-1
I’m still confused as to how to find out if the difference between
Today and Yesterday is greater than 10% Plus or Minus.
And how convoluted is the code/logic going to be to compare Monday to
Friday, Never mind Holidays
I may have gotten in over my head on this one. :{

 

[John Spencer]

Not sure this will work, but you can try the following untested stacked queries.

Query One: Saved as qPriorDate
SELECT A.Account, Max(A.TextDate) as PriorDate
FROM [YourTable] as A
WHERE A.TextDate < "20100610"

Query Two: Uses the first query and two instances of your table.

SELECT A.Account
, A.TextDate
, qPriorDate.PriorDate
, 1-(A.Amount/B.Amount) as PercentChange
FROM ([YourTable] as A INNER JOIN QPriorDate
ON A.Account = qPriorDate.Account
AND A.TextDate = qPriorDate.PriorDate)
INNER JOIN [YourTable] as B
ON A.Account = B.Account
WHERE A.TextDate = "20100610"
AND Abs(1-(A.Amount/B.Amount))>.1

I am using Access 2003
The two text files are emailed to us Daily. (sample data below)
I’ve come to understand Jeff’s point that I only need one table.
In fact as I’ve explained to the requester of this process, I only
need to import TODAYS data as I will already have imported Yesterdays
data.
Makes total sense now.

To answer your question Bob, about aggregation;
Not in this example, BUT in another process, YES there will be
multiple records for the same account.
I’m guessing that I’ll use a Totals query with a GroupBy for that
process…

Sample Data Follows...

TalCash_Wed.txt
"H","TALACCASHD","TALCASH.WED","2010-06-09"," 41:57"," "
"A401611","CDN",95477.66,20100609
"A401835","CDN",28804.85,20100609
"A402353","CDN",27336.47,20100609
"0103200","CDN",105.47,20100609
"0103218","CDN",428.71,20100609
"0103234","USA",979.99,20100609
"0112292","CDN",5051.39,20100609

TalCash_Thu.txt
"H","TALACCASHD","TALCASH.THU","2010-06-10"," 1:07:55"," "
"A401611","CDN",95477.66,20100610
"A401835","CDN",28804.85,20100610
"A402353","CDN",27336.47,20100610
"0103200","CDN",105.47,20100610
"0103218","CDN",428.71,20100610
"0103234","USA",1080.79,20100610
"0112292","CDN",5051.39,20100610

So now I have ONE table with a DATE field. I’ve used an Import
Specification.
I understand how to find the records for Today and Yesterday using
Date() and Date()-1
I’m still confused as to how to find out if the difference between
Today and Yesterday is greater than 10% Plus or Minus.
And how convoluted is the code/logic going to be to compare Monday to
Friday, Never mind Holidays
I may have gotten in over my head on this one. :{

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County