mjd327 (6/30/2014)
I'm hoping Access is workable in some way as it seems like the easiest solution, but I want to again emphasize that some of the tables are VERY large. For example a table of transactions has millions of entries. Even just querying that table alone by itself takes two minutes to finish selecting all the rows. So when you're now joining that table with other tables or not efficiently filtering your results, you run into major performance problems. And I know that the columns you select, the indexes, what order you filter in, and how you do your joins all can make major major differences in performance.
This is exactly what I figured. If there was a tool out there that could generate efficient queries dynamically there wouldn't be much need for technical people who understand how to write efficient queries. 😉 If your end users need to be able to see all the rows in the table and then filter them there isn't much you can do. This type of application is difficult if not impossible to write correctly.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/