Ok, 



I am building a Database for my market research company.  We collect 

questionairres for a variety of markets.  The potential list of questions 

is infinite.  I need to be able to add to the list as needed.  I build my 

tables as tblCustomer(Person filling out form)  one-to-one on tbl Main 

Questions as there is no need for unique index on customer.  I have filled 

up the question table.  There has to be a better design, but have been 

totally frustrated as to what that might be.  



Please help



-N-

This can be very complicated, I have worked on surveying systems before and

they can be challenging.



Perhaps this will help.  You really need a many-to-many relationship between

CUSTOMER and QUESTION. You should keep a table of customers with data about

each one.  Include a unique ID number.  I understand your application may

not need this but it will make this design work.  Also keep a table of

questions, having question text and a unique QuestionID number, and any

other fields you might need.  This table does not store any customers'

answers, it only lists the possible questions.  Then have a cross-reference

table having customer ID, Question ID, and the answer that customer gave to

that question.  This will allow as many different questions as Access can

handle rows in a table.  I think from your initial post you are limited by

having each question represented as a column?



Of course, this will require significant re-design of any reports or

queries.  Cross-tab queries will be your friend with a design like this.



I hope this helps, it is the best I can do with the limited info available.



David



 -----Original Message-----

From: 	Noel Wilson [mailto:nwilson4@t...] 

Sent:	Wednesday, January 30, 2002 10:14 AM

To:	Access

Subject:	[access] Help With DB Design



Ok, 



I am building a Database for my market research company.  We collect 

questionairres for a variety of markets.  The potential list of questions 

is infinite.  I need to be able to add to the list as needed.  I build my 

tables as tblCustomer(Person filling out form)  one-to-one on tbl Main 

Questions as there is no need for unique index on customer.  I have filled 

up the question table.  There has to be a better design, but have been 

totally frustrated as to what that might be.  



Please help



-N-

> This can be very complicated, I have worked on surveying systems before 

and

> they can be challenging.

> 

> Perhaps this will help.  You really need a many-to-many relationship 

between

> CUSTOMER and QUESTION. You should keep a table of customers with data 

about

> each one.  Include a unique ID number.  I understand your application may

> not need this but it will make this design work.  Also keep a table of

> questions, having question text and a unique QuestionID number, and any

> other fields you might need.  This table does not store any customers'

> answers, it only lists the possible questions.  Then have a cross-

reference

> table having customer ID, Question ID, and the answer that customer gave 

to

> that question.  This will allow as many different questions as Access can

> handle rows in a table.  I think from your initial post you are limited 

by

> having each question represented as a column?

> 

> Of course, this will require significant re-design of any reports or

> queries.  Cross-tab queries will be your friend with a design like this.

> 

> I hope this helps, it is the best I can do with the limited info 

available.

> 

> David

> 

>  -----Original Message-----

> From: 	Noel Wilson [mailto:nwilson4@t...] 

> Sent:	Wednesday, January 30, 2002 10:14 AM

> To:	Access

> Subject:	[access] Help With DB Design

> 

Ok, I really thank you Dave,  But I think you need more info?  I already 

have more questions than Access can handle in a table and need to link 

tables of questions together as any combination of tables could be used on 

any survey....



Yeah, its complicated.....But I'm Lovin it and Learning so much with the 

help of this message board and more experienced programmers than I am ..



> Ok, 

> 

> I am building a Database for my market research company.  We collect 

> questionairres for a variety of markets.  The potential list of 

questions 

> is infinite.  I need to be able to add to the list as needed.  I build 

my 

> tables as tblCustomer(Person filling out form)  one-to-one on tbl Main 

> Questions as there is no need for unique index on customer.  I have 

filled 

> up the question table.  There has to be a better design, but have been 

> totally frustrated as to what that might be.  

> 

> Please help

> 

> -N-

I've seen Access tables with millions of rows.  You have millions of

questions to ask?  Probably not.  Millions of answers? Maybe.  But you are

correct - if you want more help you'll have to give more details.  If you

really are dealing with millions of rows then you should probably be looking

at SQL Server or Oracle etc.  if you must have a column in a table for each

possible question then you will face a number of problems including : limit

on # of columns in a table, and effort to add columns to a table in a live

system (design , coding, & testing).  You might be better off in the long

run to look at a design that will treat questions as ROWS in a table.  If

you have a table like this:



CustomerID..........QuestionID..........Answer

1.........................Q1.......................Green

1.........................Q2.......................Yes

1.........................Q8........................42

2.........................Q1........................Blue

2.........................Q8........................75

2.........................Q11.......................3/3/01

2.........................Q14......................FALSE



you can use a crosstab query to turn that into



CustomerID..........Q1.......Q2.....Q8.....Q11.....Q14

1......................Green....Yes....42.....null......null

2.......................Blue.....null.....75....3/3/01...FALSE



which I am guessing is how your data looks now...



you would have a table called Questions

QID.....Q_Name.....Q_text



A table called Surveys

SurveyID.....Surveyname.....SurveyStartDate.....etc



And a Xref between surveys and Questions

SurveyID.....QuestionID



This is very simplified from a real system I wrote once... called Qualpro,

read about it here  http://www.nationalresearch.com/dynamic.html



Please provide more details... 



David



 -----Original Message-----

From: 	Noel Wilson [mailto:nwilson4@t...] 

Sent:	Wednesday, January 30, 2002 12:16 PM

To:	Access

Subject:	[access] RE: Help With DB Design



> This can be very complicated, I have worked on surveying systems before 

and

> they can be challenging.

> 

> Perhaps this will help.  You really need a many-to-many relationship 

between

> CUSTOMER and QUESTION. You should keep a table of customers with data 

about

> each one.  Include a unique ID number.  I understand your application may

> not need this but it will make this design work.  Also keep a table of

> questions, having question text and a unique QuestionID number, and any

> other fields you might need.  This table does not store any customers'

> answers, it only lists the possible questions.  Then have a cross-

reference

> table having customer ID, Question ID, and the answer that customer gave 

to

> that question.  This will allow as many different questions as Access can

> handle rows in a table.  I think from your initial post you are limited 

by

> having each question represented as a column?

> 

> Of course, this will require significant re-design of any reports or

> queries.  Cross-tab queries will be your friend with a design like this.

> 

> I hope this helps, it is the best I can do with the limited info 

available.

> 

> David

> 

>  -----Original Message-----

> From: 	Noel Wilson [mailto:nwilson4@t...] 

> Sent:	Wednesday, January 30, 2002 10:14 AM

> To:	Access

> Subject:	[access] Help With DB Design

> 

Ok, I really thank you Dave,  But I think you need more info?  I already 

have more questions than Access can handle in a table and need to link 

tables of questions together as any combination of tables could be used on 

any survey....



Yeah, its complicated.....But I'm Lovin it and Learning so much with the 

help of this message board and more experienced programmers than I am ..



> Ok, 

> 

> I am building a Database for my market research company.  We collect 

> questionairres for a variety of markets.  The potential list of 

questions 

> is infinite.  I need to be able to add to the list as needed.  I build 

my 

> tables as tblCustomer(Person filling out form)  one-to-one on tbl Main 

> Questions as there is no need for unique index on customer.  I have 

filled 

> up the question table.  There has to be a better design, but have been 

> totally frustrated as to what that might be.  

> 

> Please help

> 

> -N-

Have a table of questions - infinitely expandable, within the limits of

Access - with a key of the question number

Create a table to hold the 'header' details of each questionnaire - those

details which will not expand (e.g the name of the customer, address,

promotion code, etc) with a key (autonumber probably) to identify the

questionnaire



Have one row in a related table (say tblQuestionnaire_Details) for each

questionnaire / question - key is questionnaire and question number  with

fields for response, etc - the growth is no longer limited by fields in the

questionnaire table because this related table is a sub-form for input and

a sub-report if you want to output the results or analyze



This also simplifies the summing of results - you are always summing the

same field in the tblQuestionnaire_Details table and you can store your

results by question number in an analysis results table



There are allied issues about populating the tblQuestionnaire_Details table

when you add new questions - i.e. populate tblQuestionnaire_Details with

all combinations of questionnaire / question but I think this can solve

your problem



I used the principle for a 'game' at an exhibition. We had a large bank of

questions, selected 10 at random and asked the 'contestant' one question

after the other. This technique gave me the ability to expand the bank of

questions as required. Your requirement does not involve random selection

but needs all questions to be added to the questionnaire.



Hope this helps

Joe







                                                                                         

                    "Noel Wilson"                                                        

                    <nwilson4@t...        To:     "Access" <access@p...>          

                    ane.edu>             cc:                                             

                                         Subject:     [access] Help With DB Design       

                    30/01/2002                                                           

                    16:13                                                                

                    Please                                                               

                    respond to                                                           

                    "Access"                                                             

                                                                                         

                                                                                         









Ok,



I am building a Database for my market research company.  We collect

questionairres for a variety of markets.  The potential list of questions

is infinite.  I need to be able to add to the list as needed.  I build my

tables as tblCustomer(Person filling out form)  one-to-one on tbl Main

Questions as there is no need for unique index on customer.  I have filled

up the question table.  There has to be a better design, but have been

totally frustrated as to what that might be.



Please help



-N-










*************************************************************************



This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.



The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:

Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions

CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs

CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name

Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...



CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.



CIS is a member of the General Insurance Standards Council



CIS & the CIS logo (R) Co-operative Insurance Society Limited



********************************************************************************