473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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?
Aug 26 '08 #1
3 3127
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 ***
Aug 26 '08 #2
On Aug 26, 11:00*am, Rich P <rpng...@aol.comwrote:
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
Aug 26 '08 #3
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 ***
Aug 26 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Robert Blaha | last post by:
Hi, I'm runnig Linux/Mandrake9.0-Dolphin and try to update Apache 2.0.46 and PHP 4.3.2. Apache is OK bu with PHP I've problem. I unpacked distribution, run ../configure > configure.vysl, make...
0
by: pptran | last post by:
Hi, I am pretty new to building and installing Perl. Can someone help explain the severity of the following Perl 5.8.4 build error message? ==================================================...
2
by: Glenn | last post by:
Hi, I'm using cygwin and am trying to install DBI-mSQL and am getting errors on make .. any idea how to fix this (TIA): cpan> install DBD::mSQL Running install for module DBD::mSQL Running...
9
by: Joel Rodrigues | last post by:
Hi, I get the following error when I run make on Mac OS X v 10.1.5 Any ideas ? ---------------------------------------------------------- ar: illegal option -- s usage: ar -d archive file ......
5
by: CSN | last post by:
I looked through the docs and contrib, but didn't see anything related to storing and using latitude and longitude values. I have data in the form of 12° 34' N, 12° 34' W. Would any of the...
8
by: Seeker | last post by:
Hello, In using Solaris Pro Compiler to compile Pro*C code. I am getting this error: make: Fatal error in reader: parser_proc_online.mk, line 26: Badly formed macro assignment Based on other...
28
by: Steven Bethard | last post by:
Ok, I finally have a PEP number. Here's the most updated version of the "make" statement PEP. I'll be posting it shortly to python-dev. Thanks again for the previous discussion and suggestions!...
10
by: Johs | last post by:
I have a source file called project.c. In the same folder I have a Makefile containing: CC=gcc CFLAGS=-g project: project.c $(CC) $(CFLAGS) project.c -o project
4
by: jalqadir | last post by:
Now that I am trying to install Debian from a CD, I found that the network card was not supported, I read that some dude had found a driver for the NIC in a MSI-M662 laptot, I myself don't know...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.