Make-table (?Materialized views?) of complicated cross tabs,calculated field

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?

Hi Jon,

Here are a couple of things to think about with Access - it is a file
based relational database system. Right there - that implies not heavy
duty. Datawarehousing is heavy duty - for server RDBMS like sql server.
Proof: the errors you are getting in Access.

If you have 50,000 records or less and are having problems querying
these records - as you have mentioned - time to reconstruct your
application. If you have 50,000 to 100,000 records - you should still
be able to run fairly sophisticated queries in Access. After about
200,000 - 300,000+ records is where I start having problems with Access
and will migrate everything to sql server/.Net.

If you have small data - 50,000 records or less - need to rewrite your
queries. If you have big data - need to migrate to big data
environment.

Rich

*** Sent via Developersdex http://www.developersdex.com ***

On Aug 26, 11:00*am, Rich P wrote:
> Hi Jon,
>
> Here are a couple of things to think about with Access - it is a file
> based relational database system. *Right there - that implies not heavy
> duty. *Datawarehousing is heavy duty - for server RDBMS like sql server..
> Proof: *the errors you are getting in Access.
>
> If you have 50,000 records or less and are having problems querying
> these records - as you have mentioned - time to reconstruct your
> application. *If you have 50,000 to 100,000 records - you should still
> be able to run fairly sophisticated queries in Access. *After about
> 200,000 - 300,000+ records is where I start having problems with Access
> and will migrate everything to sql server/.Net.
>
> If you have small data - 50,000 records or less - need to rewrite your
> queries. *If you have big data - need to migrate to big data
> environment.
>
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Yeah, yeah, I know I know.

We're only talking a few thousand records. And it's a yearly survey
that I just need to "do", not "do right". So, I'm completely kludge-
ing it.

The problem is I'm compiling about 10 fields of "1-5" rank responses
back into a single table by having a separate query for each question,
and then doing a union to give me the list I should have had the
responses stored in in the first place.
THEN, I do the crosstab on that Union.
THEN, I do a GROUP BY query that crunches some numbers on the crosstab
to get percents and year to year comparisons of previous surveys.
THEN, I do pivots and charts on that aggregate data.



So, by the end, I've got about 5 or 6 levels of nested queries and
conditional calculated fields (to get my percents right).

I've tried to rewrite the queries by setting my WHERE Criteria at each
level, and none of them improve. I've tried doing my "counts" so I
can calculate my averages by doing dcount lookups and by doing GROUP
BY tables with count aggregates. They're all dog slow - because they
have to go off that union to get the totals for each question.

If I manually make-table the Union and Crosstab queries, it takes a
few thumb twiddling, but sufferable minutes for the admin (me), and
then the subsequent forms and charts run great for my users.

Again, the time on this project isn't to make it "right". It's to
make it "work". Since I only update survey responses once a week, and
since that update has to be manually done anyway, I consider it an
acceptable solution (i.e. A Complete F'in Kludge!). LOL

It's either some sort of Make Table or alternative method, or I do
what's been done in the past and manually export all of this into
Excel and just link people to that static file.

Again, basic question is this - Should I use make-table, which will
drop and create the table. Or, should I use some sort of delete/
insert combo? Or is there something similar to an Oracle
"materialized view" in Access I've never heard of?

Jon

OK. I have a slightly better view of what you are trying to do and
Access should be the correct tool for this. But still not too clear.
How about this? Post some sample data (fake - whatever - just a few
rows for each table) of the table/tables you are trying to query. And
then add what the results should look like - a few rows of the query
results you are looking for. Like say you have 5- 10 rows with 3 fields
per row in the source data, and the query result will have say 10 fields
per row.

tbl1
fld1 fld2 fld3 ...
...
join

tbl2
fldA fldB fldC ...
...

Query Result

ID date1 date2 date3 date4 date5 date6
.. ...
.. ...

Then you can explain where you are having the problem like how do you
get from pointA to pointB to pointC ...

Rich

*** Sent via Developersdex http://www.developersdex.com ***