 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / May 2005Advance question: Need to "cluster" records
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. > I need to separate items into > sales clusters that are defined by ranking all the items sales and [quoted text clipped - 3 lines] > sum, and identify when that running sum reaches 70% of the total sales > dollars. Never tried anything like this, but I guess I would use VBA code to: * DSum the sales value for all products; * Calculate the 70% and 90% of the total; * Define the SQL for a query that returns sales value by product in descending order; * Open the SQL query as a recordset, and loop through the records aggregating the sales value, to identify the 70% and 90% break points. I can't be any more specific with the information you have provided, I just hope this helps to get you started... unless of course someone comes up with a better suggestion! HTH, Nikos 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. 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? > > I need to separate items into > > sales clusters that are defined by ranking all the items sales and [quoted text clipped - 18 lines] > HTH, > Nikos > 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. 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. > > 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 [quoted text clipped - 16 lines] > 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. 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 > 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 [quoted text clipped - 9 lines] > 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.