Find Percentage difference among fields in a record

[Nathan H]

I am trying to write a query that will result in showing records that have a large percentage difference among fields in a table.

For instance:

Table
field1.Item
field2.account1 (percentage)
field3.account2 (percentage)
field4.account3 (percentage)
etc...

An example of what I would like the result to be is this: show me the records where one account has a 20% advantage over the next highest account.

I do not know if I am approaching the problem in the right manner having the percentages all in one table.

Thanks for any help

[Nathan H]

Anybody have any ideas? Kind of an urgent matter

[NeoPa]

I'm sorry Nathan, but I can't follow what you mean with your data so I don't get the question :(

[Nathan H]

NeoPa,

Okay, I will try to clarify. I have a table that lists products by item number, and then the accounts that the particular items were sold in. The value that is showing in the account fields are a percentage of total sales for all accounts. So for instance I have Product 1, and it sold 20% into account 1, 30% into account 2, 25% into account 3, and 25% into account 4.

Table
field1.Item
field2.account1 (percentage)
field3.account2 (percentage)
field4.account3 (percentage)
etc...

This table has around 10,000 records.

What I want to do is see where there is a large discrepancy in percentage between accounts

I am trying to write a query that will result in showing records that have a large percentage difference among fields in a table, as in "show records where an account's market share is 20% higher than it's nearest competitor" by product.

[NeoPa]

I think I'm getting you now Nathan.
You mean the .Name of field 1 is "Item" right? And you store the percentage values (for each item) for all your accounts in a single record?

Firstly, if this is the case I would recommend that you take a look at Mary's Normalisation and Table structures article. Doing it the way you seem to be is HIGHLY not recommended.

However, if you feel you have reasons that convince you that you want to persist with this type of design, let me know and I will help to put some SQL together to highlight the records you need. All assuming I'm reading you right in the first place, of course.

PS. Congratulations on reaching 100 posts. I remember when I did and it felt like a real achievement.

[Nathan H]

NeoPa,

I come by the figures in a select query (to get the percentages). I would much rather work off of that. I made a table because I was experimenting with a bunch of different functions trying to find something that worked. I will not use the "make a table" function and go back to the query.

In the query I have a "TOTALS" field, which combines all the unit sales for each account, and then each account is then divided by the total to get the percentage. Very simple math I know.

So working of this, do you have some suggestions?

I hope I am not making this more confusing.

[NeoPa]

...
I hope I am not making this more confusing.

I'm afraid you are to a degree. I appreciate your further explanation but regret the lack of direct answers to my questions.

You introduce the idea of a query that produces these results, without mentioning that it's a cross-tab query. I'm guessing that, but the fact that you don't mention it leads me to be unsure. Who would leave out something so important from an explanation if it were the case - yet I can't see any other way to understand what you're saying. It's hard to proceed without understanding the problem you see.

To find a way to approach this problem, I must understand the details of the problem. If I explain where I'm coming from you may understand why I ask the questions I do, though really why you would need to understand that before answering them is a moot point.

I need to know what the set of the accounts is. If it's a finite set then we can look at putting in explicit checks in the SQL. If it's an undefined set (there may be many or only a few) then we need to look at taking a step backwards and dealing with the data at an earlier level. In other words process the records that deal with an Item / Account - before it's grouped together to form data for the Item as a whole. You say that you produce the data from a query that joins the data into a recordset. With access to the underlying data we would want to look at comparing the Min() and Max() Accounts for an Item and seeing if the criteria you're looking for are met.

[Nathan H]

I have a Master Product Table that lists products I sell (with an item number).

I get a sales report from 10 accounts each week. Each distinct report lists the item number and quantity sold of each product.

I combine the Master Product Table and the 10 account reports in a select query so that I have a record that lists the item number, and then a separate field for each account showing quantity.

Then I convert the quantity fields to show percentages by dividing by the total sales of each product.

So I am left with query results that show in this order:

Item Number
% of Sales to Account 1
% of Sales to Account 2
% of Sales to Account 3
etc...

Again, what I want to be able to do is show only the records where there is a certain percentage difference between the highest account percentage and the SECOND highest.

[NeoPa]

In that case (having ten accounts) I would look at taking a step back and producing a (sub)query that shows the item and just one of the account's percentages. That way you would have ten records for each item.

subItemAcnt
Item; String
AccountID; Numeric or String
Percentage; Numeric

From this you would produce a query comparing the Max() & the Min() Percentage across the range.

SELECT Item
FROM [subItemAcnt]
WHERE ((Max([Percentage])-Min([Percentage]))>=25)