Hi All,
I need to know the best way to set up a datawarehouse/materialized
view for doing statistics/graphs in Access. My crosstabs and unions
are getting too complicated to crunch in real time.
Should I use a make-table I run after each update?
Thanks,
Jon
Background....
I'm working with a database that had a design flaw (a questionaire
that was built as separate columns/fields) instead of a relational
base.
So, I'm having to do lots of unions, just to create my relational
tables for me to do statistics and graphs and pivots on.
And, Access is choking. I'm getting the "too many databases open"
error, etc.
Fortunately, I only update the backend database in manual batches.
So, I'm considering doing some sort of a Make-Table/Materialized View
method that I trigger after I manually add my new questionaire
responses.
So, I just write once on my updates like a data warehouse/Materialized
View senario, and then everyone can just get their reports and graphs
without having to crunch a bunch of ugliness.
I'm considering using make-table queries, and just running the make-
table query whenever I have an update.
But, I'm worried about concurrent user issues where I update while
they're viewing. So, my question is - is there a better way (a delete
query and an Append queriy?)? (We're talking a small internal app
with maybe only 20 total users, and maybe 3-5 concurrent users at the
worst. I can kick people off when necessary.)
What's the "proper" way to do this?