Advance question: Need to "cluster" records

[Guest]

I need to separate items into
sales clusters that are defined by ranking all the items sales and
determining which ones compose the top 70% of total sales, the next 20, and
the last 10. So in pseudo code terms you need to rank the items from highest
selling to lowest in dollars, calculate the total sales, generate a running
sum, and identify when that running sum reaches 70% of the total sales
dollars.

The data setup is pretty straight forward with each item listed and it's
sales to date for the current month.

I have been able to generate a running sum in a query, but I am still at a
loss of a way to figure out where the top 70% falls. I used a Dsum to
generate the running sum and they only other idea I had was to use a criteria
versus this running sum that takes the Dsum of the entire data set and
multiplies by .70. This seems like it will work technically but when I
attempted to run it the query just hanged for a good 10 mins with no movement
on the progress meter.

The other big problem is that this needs to be dynamically linked to excel
to populate spreadsheets so I am limited in using make tables and so forth.

 

[Nikos Yannacopoulos]

I don't think I can use VBA because this needs to be done in queries only so
that I can link it dynamically to Excel.

If that's an absolute must, then I suppose it's making things harder. It
might be, though, that there is a workaround; for instance, if sales are
imported/updated periodically, you could use that same process to
trigger the categorization of items by means of an extra field in the
Items table and a recordset operation similar to the one I described
(just adding the Update part).
If it's an integrated system, in the sense that sales invoices are
created in it rather than sales data imported from another system,
things are more complicated; could another trigger be used? Maybe a
request for the report or something?

Can I define a VBA function that
figures out which cluster to place an item into and place that in a query as
a field expression?

In theory you could, but it would get inefficient very quickly as the
number of different items increases, at least in the way I imagine it
working, which is a variation of the recordset operation already
described, running on each and every query record, and looping until the
current item is found in the recordset... way too much! If I were you I
would just hope someone has a better suggestion to offer.

 

[Guest]

Essentially this is how the system is setup. The sales data is created
overnight in a reporting process for our sales system. An export file is
created and that export file location is linked to Access. I need to then
take that 'raw' sales data and determine what "cluster" each item is in
dynamically and link this data to spreadsheets in Excel.

Trust me, I know it's not exactly how I would design the system from
scratch, but it's the only possibility given our current setup and tools.

There is no "request" or report, the person just needs to be able to open a
spreadsheet and have the data update dynamically through Microsoft Query
ODBC, which is why to the best of my knowledge only queries will do the job
even if it's 10-12 queries all feeding into each other.

 

[Nikos Yannacopoulos]

Well, it is indeed a tough one, but I think there may be a solution!
Assuming the new file is downloaded from the system at 02:00 every
night, you could have a scheduled job run at, say, 03:00, which assigns
products to categories in a table through VBA code, like previously
suggested. That way, when people come in in the morning the Access
database has the new linked sales data (downloaded overnight) and the
new product rankings in the table.

HTH,
Nikos