Unique Table Relationships to build Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All,

I have “TableA†that stores data I collected from a survey:
Respondee Question1 Questions2
Question3
Response 1 5 20 $500
Response 2 7 2 $357
Response 3 2 8 $700

I also have “TableB†that stores how I want to weight each questions’
response in my analysis and the maximum number of points a response is worth:
Attribute Question1 Questions2
Question3
Weight 25% 10% 65%
MaxPoints 10 10 10

My questions is how can I define a relationship between the two tables so
Access can use “TableB†to score the responses in “TableA�

I’ve been racking my brain for a couple days and how I could do this or even
change the design of my tables but no solution yet so wanted to get an
outside opinion. Thanks!
 
What about creating a survey name in each table as the relation ship. I
would also create a drop down in both tables so that the drop down box will
be updated with the survey name preventing the two tables from having two
different names.

Then create a response table with a score field next to each response.
That would also me to create a response category in both tables (this won't
be a relation ship because the survey name is the uniquie identifier).

Now create a query to link the two main tables and the score table would be
linked to the main table.

Just trying to help, you probably know where I'm heading and can figure the
rest out.
 
Hi Donnie,

Thanks for responding. It sounds like an interseting idea but I already
have a survery name field called Respondee in TableA (its an identifier for
each survey response). Also, I dont see any logical way to add a survey name
field to TableB since its storing data that is not related to any specific
survey response - its only store attributes about how the reponses should be
evaluated. So Im not sure how I can add the survery name fields to each
table as you proposed - making it impossible to build the tables and
relationships you mention. Did I misinterpret your resposnse?

Thanks,
Chad
 
Hi All,

I have “TableA” that stores data I collected from a survey:
Respondee Question1 Questions2
Question3
Response 1 5 20 $500
Response 2 7 2 $357
Response 3 2 8 $700

I also have “TableB” that stores how I want to weight each questions’
response in my analysis and the maximum number of points a response is worth:
Attribute Question1 Questions2
Question3
Weight 25% 10% 65%
MaxPoints 10 10 10

My questions is how can I define a relationship between the two tables so
Access can use “TableB” to score the responses in “TableA”?

I’ve been racking my brain for a couple days and how I could do this or even
change the design of my tables but no solution yet so wanted to get an
outside opinion. Thanks!
Click to expand...

Your Tables are incorrectly structured, if you have (as it appears)
Fields named Question1, Question2 and Question3. This is storing data
in fieldnames! A better design would be to turn your table "sideways":
e.g.

TableANew
Respondee
QuestionNo
Response

TableBNew
QuestionNo
Weight
MaxPoints

With your current structure, you'll need a very complex query joining
TableB to TableA at least twice (once for weight, once for max
points).

Any chance you can restructure the tables in "tall thin" normalized
form, perhaps with a Normalizing Union Query? E.g.

SELECT (1) AS QuestionNo, [Attribute], [Question1] AS Weight FROM
TableB WHERE [Attribute] = "Weight"
UNION ALL
SELECT (2) AS QuestionNo, [Attribute], [Question2] AS Weight FROM
TableB WHERE [Attribute] = "Weight"
UNION ALL
SELECT (3) AS QuestionNo, [Attribute], [Question3] AS Weight FROM
TableB WHERE [Attribute] = "Weight"
UNION ALL
SELECT (1) AS QuestionNo, [Attribute], [Question1] AS Weight FROM
TableB WHERE [Attribute] = "MaxPoints"
UNION ALL
SELECT (2) AS QuestionNo, [Attribute], [Question2] AS Weight FROM
TableB WHERE [Attribute] = "MaxPoints"
UNION ALL
SELECT (3) AS QuestionNo, [Attribute], [Question3] AS Weight FROM
TableB WHERE [Attribute] = "MaxPoints"

with an Append query based on this.


John W. Vinson[MVP]
 
Back
Top