DWUTKA at marlow.com
DWUTKA at marlow.com
Fri Feb 20 18:57:40 CST 2004
Not too difficult to display the data in a query that splits the answers up. I know, it's a trade off. Normalize the data, for easy reporting, but suck up a lot of extra storage space. Or crunch what you store, to save space, and lose speed when you want to display/analyze the data. Now there's a suggestion for an upgrade to Jet. Provide a crunching/speed capability. <grin> Drew -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Charlotte Foust Sent: Friday, February 20, 2004 6:24 PM To: Access Developers discussion and problem solving Subject: RE: [AccessD] Table Structure questions The problem with that approach shows up when you want to analyze the data, Drew. Charlotte Foust -----Original Message----- From: DWUTKA at marlow.com [mailto:DWUTKA at marlow.com] Sent: Friday, February 20, 2004 3:50 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] Table Structure questions 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 _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com