[AccessD] Table Structure questions

DWUTKA at marlow.com DWUTKA at marlow.com
Fri Feb 20 17:49:35 CST 2004


May I make a little suggestion, if you worried about the number of rows?

Instead of have a record per question, why not store all of the answers in 1
record.  The easiest method would be to just create a string, of the
answers. (12012111002211000).  Question 1 would be mid([MyField],1,1)
Question 2 would be Mid([MyField],2,1), and so on.

That does 2 things for you.  One record a call, will knock quite a bit of
storage space, and by using a string, instead of Long values, you are only
using 1 byte per question + 1 extra byte per call, instead of 4 bytes for
each question.

Now, you could get even 'skimpier' then that, if you decided to get into a
little bit masking:

With 3 values, you have to use at least 2 bits.  But that still means you
can record 4 answers with 1 byte. You could do this with a class.  For ease
of math, you could use the first bit as 1, the second as 2 and no bits for
0. Or, you could think in advance, and leave yourself a possibility of
0,1,2, and 3.

Drew

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark A Matte
Sent: Friday, February 20, 2004 8:10 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Table Structure questions


Hello All,

I have been asked to set up a call monitoring db for several different 
groups.  A few pieces of data will be captured for each call and then 25 
questions will be answered for each call.  Each group may or may not have 
the same 25 questions...which they can add or remove throughout the month.  
Initially I wanted 2 main tables for data collection(1 for call info and 1 
for Answers)...The Answers table would have 1 row per question(instead of a 
column for each)...so 25 records per call monitored.  I thought this would 
be best so as new questions were added and discontinued...there would be no 
need to change the forms,tables, or reports...but with this method I will be

over 1 million records in 6 months.  I've informed them they will need some 
BE other than access for this scenario.

The question I have...is there a more efficient way to store this type of 
data?(Also, the answers are all 1,2,or 0...so its very little info...just a 
lot of rows)

Thanks,

Mark

_________________________________________________________________
Stay informed on Election 2004 and the race to Super Tuesday. 
http://special.msn.com/msn/election2004.armx

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list