set up tables relationship

A

Alex

Hello everybody,

I created a survey questions table with the following fields:

SurvID
QstnID
QstnText
QstnCtrlType
AnswCtrlType
QstnType
Grp1ID
Grp2ID
Grp3ID
PageID
PageType
OrderByID

The Group fields imply that each question can belong to a max three
independent groups.

So, I’ve created another table for Groups with the following fields:
SurvID
QstnID
Grp1ID
Grp1Name
Grp1Vis
Grp2ID
Grp2Name
Grp2Vis
Grp3ID
Grp3Name
Grp3Vis

QstnID is identity incremental field so it’s unique without having SurveyID
to be primary key as well. But we need SurveyID for making tables join
connections.
The number of questions and the questions themselves are different per survey.
The questions’ grouping per survey can be different as well. So, the
separate table with only groups without SurveyID won’t work.
My question is how to link those two tables.
Will it work:
Questions.SurveyID -> Groups.SurveyID
Questions.Grp1ID -> Groups.Grp1ID
Questions.Grp2ID -> Groups.Grp2ID
Questions.Grp3ID -> Groups.Grp3ID
Or it’s needed to be three instances of the table Groups to be linked to
the Questions table: Groups as A, Groups As B, and Groups As C:
Questions.SurveyID -> A.SurveyID
Questions.Grp1ID -> A.Grp1ID
Questions.SurveyID -> A.SurveyID
Questions.Grp2ID -> B.Grp2ID
Questions.SurveyID -> A.SurveyID
Questions.Grp3ID -> C.Grp3ID
Or it should be something else?
What would be the better practice here?

Thanks
 
A

Alex

Thank you for your reply, biganthony.

I was expecting I would be referred to that site. It’s a nice learning tool.
I’m aware of that survey. But, I’ve been requested to use some different
approach for grouping when a one question could belong to several independent
groups.

Please, let’s move away from the survey things as I could name all stuff
differently to not focus whether it’s survey or not.

I’m asking about tables relationships. If it’s appropriate to set up
relationships as I outlined in my question.

Thanks
 
J

John... Visio MVP

Steve said:
Your tables are not correct! Consider this ...
Click to expand...
It is very apparent you are struggling with this database. I can help you!
I provide help with Access applications for a small fee. Contact me if you
would like my help with your database.

Steve
Click to expand...


WARNING! Stevie is our local troll who pretends to be competent

These newsgroups are provided by Microsoft for FREE peer to peer support.
There are many highly qualified individuals who gladly help for free. Stevie
is not one of them, but he is the only one who just does not get the idea of
"FREE" support. He offers questionable results at unreasonable prices. If he
was any good, the "thousands" of people he claims to have helped would be
flooding him with work, but there appears to be a continuous drought and he
needs to constantly grovel for work.

A few gems gleaned from the Word New User newsgroup over the Christmas
holidays to show Stevie's "expertise" in Word.


Dec 17, 2008 7:47 pm

Word 2007 ..........
In older versions of Word you could highlght some text then go to Format -
Change Case and change the case of the hoghloghted text. Is this still
available in Word 2007? Where?
Thanks! Steve


Dec 22, 2008 8:22 pm

I am designing a series of paystubs for a client. I start in landscape and
draw a table then add columns and rows to setup labels and their
corresponding value. This all works fine. After a landscape version is
completed, I next need to design a portrait version. Rather than strating
from scratch, I'd like to be able to cut and paste from the landscape
version and design the portrait version.
Steve


Dec 24, 2008, 1:12 PM

How do you protect the document for filling in forms?
Steve


One of my favourites:
Dec 30, 2008 8:07 PM - a reply to stevie
(The original poster asked how to sort a list and stevie offered to create
the OP an Access database)
Yes, you are right but a database is the correct tool to use not a
spreadsheet.
Click to expand...


Not at all. If it's just a simple list then a spreadsheet is perfectly
adequate...




John... Visio MVP
 
A

Alex

Thank you very much Steve. I have all those tables with almost the same
structure you outlined.

From all those tables I’m talking only about tblQuestions and tblGroups.
According to requirements a one question can belong to a one, two, or I put
as an extreme case even to three independent groups (they’re not subgroups).
It can be as follows:

tblQuestions:
QsnID SurvID Grp1ID Grp2ID Grp3ID
1 10 2 1 1
2 10 3 3 2
3 10 3 4 3
.....
25 11 2 1 3

tblGroups
SurvID Grp1ID Grp1Name Grp2ID Grp2Name Grp3ID Grp3Name
10 1 Leadership 1 Encouragment 1 SomeName
10 2 SomeName 2 SomeName 2 SomeName
....
11 1 SomeName 1 SomeName 1 SomeName
11 2 SomeName 2 SomeName 2 SomeName
....
 
J

John W. Vinson

Hello everybody,

I created a survey questions table with the following fields:

SurvID
QstnID
QstnText
QstnCtrlType
AnswCtrlType
QstnType
Grp1ID
Grp2ID
Grp3ID
PageID
PageType
OrderByID

The Group fields imply that each question can belong to a max three
independent groups.
Click to expand...

If each Question can belong to zero, one or more groups, and each Group may
apply to zero, one, or more Questions... *you have a many to many
relationship*.

In Access (or any relational database) the proper way to model this is with
three tables:

Questions
SurvID
QstnID
<etc>

Groups
GrpID
Group
<other information about the group as a whole>

GroupMembership
SurvID
QstnID <joint links to Questions>
GrpID <which group is this question in>

If a question is in three groups you would have three RECORDS in
GroupMembership.
So, I’ve created another table for Groups with the following fields:
SurvID
QstnID
Grp1ID
Grp1Name
Grp1Vis
Grp2ID
Grp2Name
Grp2Vis
Grp3ID
Grp3Name
Grp3Vis
Click to expand...

So if there are 52 questions in Grp1 you would store Grp1Name redundanly 52
times, and correct the change of spelling 52 times if you detect an error...!?
OUCH! That's "spreadsheet thinking" and is not good design.
 
A

Alex

Thanks, John.

As I said before the same question can belong to different grouping
methodology.
E.g.
SurvID Qstns: Grp1 Grp2 Grp3
10 Question1 External Customers Engagement Leadership
10 Question2 Internal Customers Satisfaction Management
.....
11 Can be different

The customer requirements are to have it as this.

How would you suggest to approach it?

Thanks
 
J

John W. Vinson

Thanks, John.

As I said before the same question can belong to different grouping
methodology.
E.g.
SurvID Qstns: Grp1 Grp2 Grp3
10 Question1 External Customers Engagement Leadership
10 Question2 Internal Customers Satisfaction Management
....
11 Can be different

The customer requirements are to have it as this.
Click to expand...

Don't confuse data PRESENTATION with data STORAGE.

You still have a many to many relationship. Storing it incorrectly will not
help you.
How would you suggest to approach it?
Click to expand...

Just as I suggested in my prior post. You can use a multiselect listbox or a
subform to display it and edit it, or (with a little bit of code) denormalize
for display purposes. Or, if you are willing to pay the price (far more
complex queries searching three fields instead of one, inability to sort by
group, etc.) you can use your three field denormalized table structure. It's
your call!
 
A

Alex

Thanks again, John.

You’re saying “Just as I suggested in my prior post.†I tried to find that
suggestion. Could you please point me out there as I could see the only post
about Stevie?

Yes, I understand that presentation can be different from a db. The previous
example was for demonstrating a logic behind.

How about if we create a third table where we’ll have repeating questions
with groups with having a primary key by those two fields?

E.g.,
SurvID QsntID GrpID
10 1 1
10 1 2
....
10 2 3
10 2 7

Then a tblGroups table could have the only SurID and GrpID, GrpName fields
without QsntID.

Please, advise
 
J

John W. Vinson

Thanks again, John.

You’re saying “Just as I suggested in my prior post.” I tried to find that
suggestion. Could you please point me out there as I could see the only post
about Stevie?
Click to expand...

Sorry, here it is again.

If each Question can belong to zero, one or more groups, and each Group may
apply to zero, one, or more Questions... *you have a many to many
relationship*.

In Access (or any relational database) the proper way to model this is with
three tables:

Questions
SurvID
QstnID
<etc>

Groups
GrpID
Group
<other information about the group as a whole>

GroupMembership
SurvID
QstnID <joint links to Questions>
GrpID <which group is this question in>

If a question is in three groups you would have three RECORDS in
GroupMembership.
So, I’ve created another table for Groups with the following fields:
SurvID
QstnID
Grp1ID
Grp1Name
Grp1Vis
Grp2ID
Grp2Name
Grp2Vis
Grp3ID
Grp3Name
Grp3Vis
Click to expand...

So if there are 52 questions in Grp1 you would store Grp1Name redundanly 52
times, and correct the change of spelling 52 times if you detect an error...!?
OUCH! That's "spreadsheet thinking" and is not good design.

Yes, I understand that presentation can be different from a db. The previous
example was for demonstrating a logic behind.

How about if we create a third table where we’ll have repeating questions
with groups with having a primary key by those two fields?

E.g.,
SurvID QsntID GrpID
10 1 1
10 1 2
...
10 2 3
10 2 7

Then a tblGroups table could have the only SurID and GrpID, GrpName fields
without QsntID.
Click to expand...

Exactly!
 
B

BruceM

The John who responded to warn about Steve's trolling tendencies is
different from John Vinson, in case that is not clear. No problem, just
mentioning it.
 
Top