mysql connection

If you can set up the MYSQL database as an ODBC connection (do a web search for this), then you can use MS-Query to import the data. No VBA would be required for this part. Develop the SQL you want in some tool other than MS-Query. I'm a big fan of the tool, but admit that the query builder is painful to use and that's a kind assessment.

I'm a bit hazy on what you want to do with the rolling part. You can set up the SQL to read in everything that has come in since the last update and sort by time descending.

Another method would be to import the data after a certain time one hour ago, two hours ago, whatever to assure you get at least 50 rows. Then you can use a pivot table against the imported data with a date sort descending and a filter for the top 50. The pivot table might be useful for some of your analysis. The pivot table should return a fixed range of cells for any formulas you will use.

You can run formulas on pivot tables, but you have to "overlay" them with named dynamic ranges. Sometimes, especially if you are consolidating a lot of data, this is worth doing.

However, I get the impression your requirement isn't like that. If you are going to go through that much trouble, you might as well make named ranges for the first 50 rows of the returned data.

Here is a wiki on named ranges: http://www.utteraccess.com/wiki/Offs...Dynamic_Ranges

Attached is a sample workbook to give you an idea how this might be implemented.

Sheet1 has data returned from the query. In this case 98 rows. We only want to look at the latest 50. The query had a SORT BY clause of Sort By 1, desc so the dates come out newest to oldest.

I defined a range called My_Date to overlay on the date: =OFFSET(Sheet1!$A$2 ,0 ,0, 50 ,1)

This range points to Cell A2, goes zero rows down, zero columns to the right, and returns a range 50 rows deep and 1 column wide. Normally some of these parameters are calculated with CountA or maybe even Match.

Once we define a range we can define other ranges from it. The name Value_2 =OFFSET(My_Date,0,1) which says, give me the same range as My_Date, but shifted by zero columns down and one column to the right.

So My_Date points to A2:A51 and my Value points to B2:B51.

I tried defining a range intersecting the table with rows 1:51, but I could not address it like a table. So it looks like individual named ranges are the only way to go.



If you don't have a lot of columns or if you only need to reference a few of them, this approach may work for you.

If you use the pivot table approach, sort the date newest to oldest and there is a filter you can apply for Top 10 - except that you can specify top 50.

[Edit] I lied. You can only do top 50 in a pivot table on something in the value field not on something in the row field. Forget about the pivot table approach altogether.

Limit the amount of data coming in with the query, (you can pass a parameter with MS-Query) and then fine tune it with the named ranges.

This isn't the greatest wiki I've ever written, but it should get you started with MS-Query.

http://www.utteraccess.com/wiki/MS_Query

I do not have it. good for you if it works.