Re: Make-table (?Materialized views?) of complicated cross tabs, calculated field
- From: jonceramic <jonceramic@xxxxxxxxx>
- Date: Tue, 26 Aug 2008 09:29:50 -0700 (PDT)
On Aug 26, 11:00 am, Rich P <rpng...@xxxxxxx> 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
.
- Follow-Ups:
- References:
- Prev by Date: Re: Make-table (?Materialized views?) of complicated cross tabs, calculated field
- Next by Date: Query returns incorrect results
- Previous by thread: Re: Make-table (?Materialized views?) of complicated cross tabs, calculated field
- Next by thread: Re: Make-table (?Materialized views?) of complicated cross tabs, calculated field
- Index(es):
Relevant Pages
|
