Create Histogram Query from complex query

[madpiet]

1. Extract the Highs and Lows of the day and the times they occur.



Can't remember the structure... is it like (stockTicker, Date, Time, Price)? If so, you can do something like

SELECT stockTicker, Date, MAX(Price) AS MaxPrice 
FROM table 
GROUP BY stockTicker, Date

And then you can join that back to the original table on (stockTicker, MaxPrice) and get the / a related time. (If the stock were at the MaxPrice more than once, more than one value would be returned, so you'd have to choose which to keep.

[EricRex]

1. Extract the Highs and Lows of the day and the times they occur.

Can't remember the structure... is it like (stockTicker, Date, Time, Price)? If so, you can do something like

SELECT stockTicker, Date, MAX(Price) AS MaxPrice 
FROM table 
GROUP BY stockTicker, Date

And then you can join that back to the original table on (stockTicker, MaxPrice) and get the / a related time. (If the stock were at the MaxPrice more than once, more than one value would be returned, so you'd have to choose which to keep.

Unfortunately, I have each stock's quotes in its own table, without the symbol field. That's because a single table with years worth of data including multiple timeframes (daily, weekly, monthly, 3-minute bars) for multiple stocks would become much too big and unwieldy to do queries that would perform tolerably well. Thus, my comment on needing to be able to change the table name in the base query.

[madpiet]

Ick. Do not pass Go, do not collect $200. Go directly to SQL Server. If you want to be able to change table names inside your SQL statement only once, you might be able to do something like have a dropdown of table names, and then a "standard" query that works against one of the tables. Then you do something like assign the query string in the QueryDef to a string variable, then use REPLACE on it to return a new string with the new table name, and then you'd do something with that.

One option *might* be the free version of SQL Server, but be warned, there's a lot to SQL Server. I started in Access, and it was still a lot to learn.

I wonder if a data warehouse design would be a good way to go...

[jojowhite]

If StartTime And EndTime on Query1 And Query2 are the same,
you can just Join them on the StartTime, since values on this column are unique (and so does EndTime).

With regards to changing table name in the query, you can
using Querydefs and VBA, you can Replaced the table name in the SQL property
of the querydef object.

[madpiet]

No bueno!!!
What if you need to query two of the tables at once? UNION ALL is gonna be painful. (and not editable). About the only way I can see doing what you want is to create a sort of "dummy" query, where everything is right except for the table name. Then the query would be something like this (nonsense example):

SELECT x.Col1, x.Col2, x.Col3
FROM MyTable AS x
WHERE x.Company = 'Company A';

in your code, you'd just do something like (1) grab the .SQL property of the dummy query (the SQL statement). (2) Then use REPLACE(qdf.SQL, "MyTable", "TheRealTableName"), and then you'd (if I remember right) save the query and run it. (basically overwriting the dummy). Then you'd have to change it back later in code.

On the other hand, if you dumped all of this into the free version of SQL Server, you could just have lots of rows, and index them properly, and then you'd just have a simple parameterized query/stored procedure, and you could get whatever Stock you wanted, like this:

CREATE PROCEDURE GetSingleCompanyData
@CompanyID INT
AS
SELECT <column list>
FROM <MyTable>
WHERE CompanyID = @CompanyID;

Then you just call it like this:

EXEC MyStoredProcedure @Ticker='Company1';

and if you could just create a passthrough query to SQL Server that would run that - and the results would be returned to your query window or fed to your report or whatever.

but if SQL Server isn't an option, that could be a problem. I just don't see any other sane way of doing this. the free version of SQL Server has a max database size of like 10GB, I think. I honestly don't remember anymore, since I don't really live in Access anymore.

Hey, Albert Kallal, wouldn't this be a much easier way to do this than to have a zillion different tables in Access? To me that sounds like a huge headache. Sure, you may have to index carefully, but after that, the rest is easy.

[jojowhite]

this is but a demo of changing the SQL, there are two queries, 1 is a dummy (prefix with zz) and the other
without prefix. the Recordsource of the subform is without prefix.

Open form1 and see the code on the Load event of the form and the AfterUpdate event of the combobox.

[June7]

How do you know one table would be too big and unwieldy? I have read comments in forums about Access database with millions of records in a single table that perform fine. However, depending on the query, even a much smaller table can have performance issues.

Where are you running this database? Single user? On a laptop or server?

I installed SQLServer Express on my laptop and connect to it with Access. The process was a bit of a challenge. So was learning how to program app for Android phone. My point - set your mind to it and will probably get it done. Having said that, I tried to install MySQL and gave up.

[madpiet]

How many records are in each of the tables holding the data you're querying? How many tables are there? (So how many total records?) If you have the table definition, we can just fake some data (or a LOT) and see how much the various indexing options speed it up.

If the date and time are separate columns and indexed, does that help things out? Would be an interesting exercise to test out various options. One thing I would *not* do is UNION ALL the tables together and expect any kind of performance. It'll be terrible. That's why I was wondering about indexing the Date/Time column and then the date-only column and the time-only column. (That's another thing I really like about SQL Server - being able to see/read the query execution plan. It tells you how much time each part is taking to execute, so you have some idea where to start fixing the performance problems.

[ArviLaanemets]

Unfortunately, I have each stock's quotes in its own table, without the symbol field. That's because a single table with years worth of data including multiple timeframes (daily, weekly, monthly, 3-minute bars) for multiple stocks would become much too big and unwieldy to do queries that would perform tolerably well. Thus, my comment on needing to be able to change the table name in the base query.

Generally having same info in multiple tables instead of single one doesn't result in better performance of queries (except queries returning info from single of those tables).

Having multiple tables instead of single one will bloat your DB somewhat, so you risk to reach the size limit for database earlier.

In case you need to query info from multiple stocks, you are forced to use union query as source, which:
a) will bloat your querystrings, making them hard to read, and can result in querystring being too long at some moment, and the query failing then;
b) I haven't checked this, but I suspect that differently from single table with proper indexing, the UNION subquery don't have any indexing - and as result to get any summary info from it may be a lot slower;
c) In case you get at some new stock registered somewhat later, you have to redesign all your queries whenever this will happen.

And as it was adviced earlier - in case you have performance problems, consider switching your back-end to SQL server. 10 GB SQL backend size versus 2 GB Access backend size in case Express version, and much more in case Professional version, is a strong argument. And you will have a lot of less limitations for your queries in SQL Server (e.g. how many of different joins, and in which order, you can use), and a lot of syntax options which you don't have in Access at all (like WITH clause), which will be a great help making you queries faster.

[madpiet]

Unfortunately, I have each stock's quotes in its own table, without the symbol field...

One way to move this to SQL Server may be like this:
1. Create a table in SQL Server to hold the prices information (Date, Time, Ticker, Price...) ... basically so it looks like your individual stock tables in Access. Then you create a linked table in Access that basically points to that table in SQL Server.
2. Say you have a clustered index on (Ticker, Date, time) on that table. (You'd have to create it... SQL Server, for better or worse, gives you control of everything... great if you know what you're doing, not so much if you don't.)
3. then you can loop over the tabledefs collection in the Access database to retrieve the table names programmatically, and grab the ticker name from there. (tabledef.name). You could just assign the tickerName to a variable.
4. inside the loop, insert the contents of that table into the SQL Server table. (basically by appending to the linked table).

The other hard part is going to be getting your head around the different types of objects that Access just lumps together as queries. In SQL Server, select queries without order by clauses are views. Anything you add a parameter to so that you can create a dynamic filter is usually a stored procedure. (you can create table-valued functions, but that's a bit more advanced.)

While this is definitely doable, the transition from Access to SQL Server is absolutely not trivial. At some point, if you decide to make the jump, there are other websites where you can get help with SQL Server... SQLServerCentral.com is pretty good, but it feels like it's dying. Just nowhere near as active as it was like 8-10 years ago. The nice thing about that website is that there are tons of articles and things you can read about a huge range of topics, and there are some really insanely smart people on there.

[NoellaG]

If you're going to upsize to SQL server, a few first things:
- when you create the database, don't set the automatic file growth to a %, but a fixes size in MB
- for initial size I would take the Access size * 2, autogrowth: 100 MB would be enough at first
- Important!!!! Set the recovery model to simple, by default it is set to full, but if you don't take regular log backups, this will quickly flood your disk
- security: create your own login accounts and DB users and set the rights correctly; disable the build in SA account

Web sides I like : https://www.mssqltips.com/
and of course : Brent Ozar Brent Ozar Unlimited - SQL Server Consulting and Training