Sean Lange (6/30/2014)
mjd327 (6/30/2014)
Ok, I just had a talk with one of my bosses and got some more information on what he had in mind.I didn't realize what he meant at first, but what he was hoping was that rather than having it be created dynamically (as the application is doing now) we would be able to have them access (through Access) a smaller view/table that we create ourselves.
For example, the different departments will only need some specific tables and columns, which is a small subset of the total tables and columns. My boss was hoping we could do all of the joins ourselves, and create sort of views/tables that would be faster to query from. We could also edit the indexes and things like that, so that way that using Access the users can access THOSE tables and get their results much faster than needing to join a table with a million entries and tons of columns, most of which are unnecessary for their use.
That is more manageable. I would build those views on the sql side and let access retrieve that data.
Okay, I think I'll experiment with SQL Server and Access later today. I guess I'd want to make views of joined tables. That should cut down a lot of time, especially if they always are searching for the same columns, which it looks like they are. Since it looks like they need like 30 fields from this massive table and are joining it with like 1-2 fields from like 3 other tables, it should be pretty easy to just join those fields to the table (I haven't played with views before but I'm assuming I can.)
Can I do something like have multiple tables, one of the entries from the past 5 days, then 30 days, then 60 days, and then when they search for a date range I check the correct table? Because that would probably also help with speed.