A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

huge problems with database deisgn for survey



 
 
Thread Tools Display Modes
  #11  
Old November 14th, 2008, 11:48 PM posted to microsoft.public.access.tablesdbdesign
Desperate in Ontario[_2_]
external usenet poster
 
Posts: 8
Default huge problems with database deisgn for survey

Sorry, I did say that I had done the append query. Obviously wrongly but I
wasnt ignoring your advice.

I realise that its hard work talking to a novice but I am trying to do this
right rather than go for the easier option.



"Duane Hookom" wrote:

To get the results, you need to run an append query as per two previous
replies in this thread. The questions are then displayed in a continuous
subform.

I'm not sure if it would help understand but if you look at the Northwind
Orders and OrderDetails tables. Let's make a couple assumptions:
- an order record is like a case record
- an order detail is like a result
- a product is like a question (let's assume there are only 7 products
available or 7 questions)
- we then have to assume that everyone is going to buy all seven products
(answer all 7 questions)

== northwind ==
You would create an append query based on your product table and the one
Order from your order table. This would create 7 order detail records waiting
for you to enter quantities to purchase.

== survey ==
You would create an append query based on your questions table and the one
Case from your tblCase table. This would create 7 tblResults records waiting
for you to enter an answer value.

You would never create a single field for each product to be ordered. For
instance you wouldn't have an Orders table with fields like: Bread, Milk,
Eggs, Apples, ... As I stated earlier, if your questions are fairly generic,
you need to normalize.

Maybe you should just allow the two local "Experts" create all the fields,
controls, reports, queries, ... required with the multiple field solution ;-)

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Thanks for the replies

Excel won't provide the answers though as it won't porvide reports in the
same way as Access does. I am probably wrong there but my experience of Excel
doesnt give me means of saying that those Cases who live in post code xxx,
who are aged xx, answered, yes, scored 20 etc.

My basic problem with this is that now I have the tables. I haven't been
able to construct a form that has all the questions (i realize that I can get
round this by using text boxes,) and a method of entering the answers so that
the results table is populated.

The two local "Experts" suggest that I use a field per question. Even some
online examples use this method, So I am very confused. I really want to do
this the right way tho, I don't want to build in problems for later,

I have made a query that is ResultsToQuestionText. I thought this might help
with the forms as a subform to case. i still have the problem of how to get a
box for each response and show which question.

I have tried substituting my questions into AYS but that was a mess too!

thank you

"Duane Hookom" wrote:

I agree with John (as usual). One of the nice benefits of creating a
normalized survey application is you don't have to create lots of fields,
lookup tables, controls, etc.

When I began working on the At Your Survey solution, I was taking over an
application started by someone else. I very soon got tired of adding fields,
adding tables, adding controls, adding code, adding logic,...

While the normalized structure requires that your survey conforms with some
basic constraints, it is quite flexible. It could be modified to provide a
different user interface but the table structure should remain mostly the
same.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario
wrote:


Hi,
I have set up the forms and am trying to add the boxes for the results.
Should I be looking at list boxes or combo boxes?

Whichever is more suitable for your user interface. They both let you edit
fields; they look different (the listbox always displays multiple rows, the
combo drops down thereby saving screen space, and they handle keystrokes
differently). Both work.

My questions are listed in rows as text in tblQuestionsAsked, not as fields,
so how do I enter each question into the form and have a box for the results?

You don't enter questions INTO THE FORM. You *use* the form (or a Questions
form) to enter questions INTO THE TABLE. The form for entering answers
displays the data stored in the table.

Should my results table have rows for each answer?

Yes.

Would it be so terrible if I went back to having a field for each question??

Yes.

In the nature of surveys, questions change over time. If you have a field per
question, then when you add or change a question, you must restructure your
Table, restructure all Queries involving the table, redesign your Form,
redesign your Reports... a monstrous amount of work. In a normalized design
when you change or add a question you open the Questions form, change or add
the question... and you're DONE.

If you'll only ever do *one* survey, and never change it, and discard the
database when you're done, then maybe you can do it that way... but it might
be simpler to do so in Excel, since you're essentially building a spreadsheet
rather than a database.
--

John W. Vinson [MVP]

  #12  
Old November 15th, 2008, 02:02 AM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 7,177
Default huge problems with database deisgn for survey

Is your "ResultsToQuestionText" the append query? Could you share the SQL
view of the append query?

Do you have a form based on the Case table with a subform based on the
results table? Did you set up the Link Master/Child properties?

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Sorry, I did say that I had done the append query. Obviously wrongly but I
wasnt ignoring your advice.

I realise that its hard work talking to a novice but I am trying to do this
right rather than go for the easier option.



"Duane Hookom" wrote:

To get the results, you need to run an append query as per two previous
replies in this thread. The questions are then displayed in a continuous
subform.

I'm not sure if it would help understand but if you look at the Northwind
Orders and OrderDetails tables. Let's make a couple assumptions:
- an order record is like a case record
- an order detail is like a result
- a product is like a question (let's assume there are only 7 products
available or 7 questions)
- we then have to assume that everyone is going to buy all seven products
(answer all 7 questions)

== northwind ==
You would create an append query based on your product table and the one
Order from your order table. This would create 7 order detail records waiting
for you to enter quantities to purchase.

== survey ==
You would create an append query based on your questions table and the one
Case from your tblCase table. This would create 7 tblResults records waiting
for you to enter an answer value.

You would never create a single field for each product to be ordered. For
instance you wouldn't have an Orders table with fields like: Bread, Milk,
Eggs, Apples, ... As I stated earlier, if your questions are fairly generic,
you need to normalize.

Maybe you should just allow the two local "Experts" create all the fields,
controls, reports, queries, ... required with the multiple field solution ;-)

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Thanks for the replies

Excel won't provide the answers though as it won't porvide reports in the
same way as Access does. I am probably wrong there but my experience of Excel
doesnt give me means of saying that those Cases who live in post code xxx,
who are aged xx, answered, yes, scored 20 etc.

My basic problem with this is that now I have the tables. I haven't been
able to construct a form that has all the questions (i realize that I can get
round this by using text boxes,) and a method of entering the answers so that
the results table is populated.

The two local "Experts" suggest that I use a field per question. Even some
online examples use this method, So I am very confused. I really want to do
this the right way tho, I don't want to build in problems for later,

I have made a query that is ResultsToQuestionText. I thought this might help
with the forms as a subform to case. i still have the problem of how to get a
box for each response and show which question.

I have tried substituting my questions into AYS but that was a mess too!

thank you

"Duane Hookom" wrote:

I agree with John (as usual). One of the nice benefits of creating a
normalized survey application is you don't have to create lots of fields,
lookup tables, controls, etc.

When I began working on the At Your Survey solution, I was taking over an
application started by someone else. I very soon got tired of adding fields,
adding tables, adding controls, adding code, adding logic,...

While the normalized structure requires that your survey conforms with some
basic constraints, it is quite flexible. It could be modified to provide a
different user interface but the table structure should remain mostly the
same.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario
wrote:


Hi,
I have set up the forms and am trying to add the boxes for the results.
Should I be looking at list boxes or combo boxes?

Whichever is more suitable for your user interface. They both let you edit
fields; they look different (the listbox always displays multiple rows, the
combo drops down thereby saving screen space, and they handle keystrokes
differently). Both work.

My questions are listed in rows as text in tblQuestionsAsked, not as fields,
so how do I enter each question into the form and have a box for the results?

You don't enter questions INTO THE FORM. You *use* the form (or a Questions
form) to enter questions INTO THE TABLE. The form for entering answers
displays the data stored in the table.

Should my results table have rows for each answer?

Yes.

Would it be so terrible if I went back to having a field for each question??

Yes.

In the nature of surveys, questions change over time. If you have a field per
question, then when you add or change a question, you must restructure your
Table, restructure all Queries involving the table, redesign your Form,
redesign your Reports... a monstrous amount of work. In a normalized design
when you change or add a question you open the Questions form, change or add
the question... and you're DONE.

If you'll only ever do *one* survey, and never change it, and discard the
database when you're done, then maybe you can do it that way... but it might
be simpler to do so in Excel, since you're essentially building a spreadsheet
rather than a database.
--

John W. Vinson [MVP]

  #13  
Old November 18th, 2008, 05:02 PM posted to microsoft.public.access.tablesdbdesign
Desperate in Ontario[_2_]
external usenet poster
 
Posts: 8
Default huge problems with database deisgn for survey


Hi
I am back at my desk and can't seem to find my last post or the reply to it
that arrived on Friday evening. The email link no longer works. I wasn't able
to act on the suggestions as I don't have access to the DB at home. Duane
you suggested i look at Northwind and compare, plus another comment re the
append query. I am going to look at Northwind and redo the query but would
be very grateful if you could repost your comments.

thank you again.


"Duane Hookom" wrote:

Is your "ResultsToQuestionText" the append query? Could you share the SQL
view of the append query?

Do you have a form based on the Case table with a subform based on the
results table? Did you set up the Link Master/Child properties?

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Sorry, I did say that I had done the append query. Obviously wrongly but I
wasnt ignoring your advice.

I realise that its hard work talking to a novice but I am trying to do this
right rather than go for the easier option.



"Duane Hookom" wrote:

To get the results, you need to run an append query as per two previous
replies in this thread. The questions are then displayed in a continuous
subform.

I'm not sure if it would help understand but if you look at the Northwind
Orders and OrderDetails tables. Let's make a couple assumptions:
- an order record is like a case record
- an order detail is like a result
- a product is like a question (let's assume there are only 7 products
available or 7 questions)
- we then have to assume that everyone is going to buy all seven products
(answer all 7 questions)

== northwind ==
You would create an append query based on your product table and the one
Order from your order table. This would create 7 order detail records waiting
for you to enter quantities to purchase.

== survey ==
You would create an append query based on your questions table and the one
Case from your tblCase table. This would create 7 tblResults records waiting
for you to enter an answer value.

You would never create a single field for each product to be ordered. For
instance you wouldn't have an Orders table with fields like: Bread, Milk,
Eggs, Apples, ... As I stated earlier, if your questions are fairly generic,
you need to normalize.

Maybe you should just allow the two local "Experts" create all the fields,
controls, reports, queries, ... required with the multiple field solution ;-)

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:

Thanks for the replies

Excel won't provide the answers though as it won't porvide reports in the
same way as Access does. I am probably wrong there but my experience of Excel
doesnt give me means of saying that those Cases who live in post code xxx,
who are aged xx, answered, yes, scored 20 etc.

My basic problem with this is that now I have the tables. I haven't been
able to construct a form that has all the questions (i realize that I can get
round this by using text boxes,) and a method of entering the answers so that
the results table is populated.

The two local "Experts" suggest that I use a field per question. Even some
online examples use this method, So I am very confused. I really want to do
this the right way tho, I don't want to build in problems for later,

I have made a query that is ResultsToQuestionText. I thought this might help
with the forms as a subform to case. i still have the problem of how to get a
box for each response and show which question.

I have tried substituting my questions into AYS but that was a mess too!

thank you

"Duane Hookom" wrote:

I agree with John (as usual). One of the nice benefits of creating a
normalized survey application is you don't have to create lots of fields,
lookup tables, controls, etc.

When I began working on the At Your Survey solution, I was taking over an
application started by someone else. I very soon got tired of adding fields,
adding tables, adding controls, adding code, adding logic,...

While the normalized structure requires that your survey conforms with some
basic constraints, it is quite flexible. It could be modified to provide a
different user interface but the table structure should remain mostly the
same.

--
Duane Hookom
Microsoft Access MVP


"John W. Vinson" wrote:

On Thu, 13 Nov 2008 07:17:01 -0800, Desperate in Ontario
wrote:


Hi,
I have set up the forms and am trying to add the boxes for the results.
Should I be looking at list boxes or combo boxes?

Whichever is more suitable for your user interface. They both let you edit
fields; they look different (the listbox always displays multiple rows, the
combo drops down thereby saving screen space, and they handle keystrokes
differently). Both work.

My questions are listed in rows as text in tblQuestionsAsked, not as fields,
so how do I enter each question into the form and have a box for the results?

You don't enter questions INTO THE FORM. You *use* the form (or a Questions
form) to enter questions INTO THE TABLE. The form for entering answers
displays the data stored in the table.

Should my results table have rows for each answer?

Yes.

Would it be so terrible if I went back to having a field for each question??

Yes.

In the nature of surveys, questions change over time. If you have a field per
question, then when you add or change a question, you must restructure your
Table, restructure all Queries involving the table, redesign your Form,
redesign your Reports... a monstrous amount of work. In a normalized design
when you change or add a question you open the Questions form, change or add
the question... and you're DONE.

If you'll only ever do *one* survey, and never change it, and discard the
database when you're done, then maybe you can do it that way... but it might
be simpler to do so in Excel, since you're essentially building a spreadsheet
rather than a database.
--

John W. Vinson [MVP]

  #14  
Old November 18th, 2008, 06:36 PM posted to microsoft.public.access.tablesdbdesign
Desperate in Ontario[_2_]
external usenet poster
 
Posts: 8
Default huge problems with database deisgn for survey


This is the SQL of the append query

INSERT INTO tblResults ( CaseID, Results )
SELECT tblCase.CaseID, tblResults.Results
FROM tblQuestionsAsked INNER JOIN (tblCase INNER JOIN tblResults ON
tblCase.CaseID = tblResults.CaseID) ON tblQuestionsAsked.QuestionsID =
tblResults.QuestionsID
ORDER BY tblCase.CaseID;

I have the master child fields set to CASE ID

Thanks
  #15  
Old November 20th, 2008, 03:27 PM posted to microsoft.public.access.tablesdbdesign
Desperate in Ontario[_2_]
external usenet poster
 
Posts: 8
Default huge problems with database deisgn for survey


Bumping this to the top in the hope of an answer.


"Desperate in Ontario" wrote:


This is the SQL of the append query

INSERT INTO tblResults ( CaseID, Results )
SELECT tblCase.CaseID, tblResults.Results
FROM tblQuestionsAsked INNER JOIN (tblCase INNER JOIN tblResults ON
tblCase.CaseID = tblResults.CaseID) ON tblQuestionsAsked.QuestionsID =
tblResults.QuestionsID
ORDER BY tblCase.CaseID;

I have the master child fields set to CASE ID

Thanks

  #16  
Old November 20th, 2008, 09:22 PM posted to microsoft.public.access.tablesdbdesign
Duane Hookom
external usenet poster
 
Posts: 7,177
Default huge problems with database deisgn for survey

Your append query needs to append the CaseID and QuestionsAskedID values to
tblResults. The CaseID should come from the current form where the CaseID is
bount to a text box named txtCaseID. Change your form and control names in
the below SQL. Make sure you have a unique indes in tblResults based on
CaseID and QuestionsAskedID.

INSERT INTO tblResults ( CaseID, QuestionsAskedID)
SELECT Forms!frmYourMainForm!txtCaseID, QuestionsAskedID
FROM tblQuestionsAsked ;

--
Duane Hookom
Microsoft Access MVP


"Desperate in Ontario" wrote:


This is the SQL of the append query

INSERT INTO tblResults ( CaseID, Results )
SELECT tblCase.CaseID, tblResults.Results
FROM tblQuestionsAsked INNER JOIN (tblCase INNER JOIN tblResults ON
tblCase.CaseID = tblResults.CaseID) ON tblQuestionsAsked.QuestionsID =
tblResults.QuestionsID
ORDER BY tblCase.CaseID;

I have the master child fields set to CASE ID

Thanks

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 08:16 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.