[theidx]

I'm setting up a database for a survey, and these are my tables

    _______________   ______________   ___________  _______________  ________________
    |_tblUsers____|   |_tblAnswers__|  |_tblAlt__|  |_tblQuestion_|  |_tblSurvey_____|
    | userID      |   | answerAltID |  | altID   |  | questID     |  | surveyID      |
    | username    |   | userID      |  | altText |  | questText   |  | surveyName    |
    |_____________|   |_____________|  |_questID_|  |_surveyID____|  |_______________|

I want to make some statistics form these tables.

Rows should be like this:

| questText | altText | count how many answered this | count how many total answered this question | surveyName |

I want to list 1 row for each alternative, and am having a real time creating this query.

Any help would be appreciated!

[DarenR]

you would select your items from table 1 inner join table 2 on table 1 id = table 2 id inner join table three on table 3 id = table 2 id and so on

like this example

 select t_Order.Customer_account_ID,  t_Vendor.vendor_ID, t_Vendor.txt_Company_Name, t_Employee.txt_first_name, 
                      t_Employee.txt_last_name, t_Order_Detail.txt_Order_Number
FROM         t_Order INNER JOIN
                      t_Order_Detail ON t_Order.Order_ID = t_Order_Detail.Order_ID INNER JOIN
                      t_Vendor_Employee ON t_Order_Detail.Vendor_Employee_ID = t_Vendor_Employee.vendor_employee_id INNER JOIN
                      t_Vendor ON t_Vendor_Employee.Vendor_ID = t_Vendor.vendor_ID INNER JOIN
                      t_Employee ON t_Vendor_Employee.employee_ID = t_Employee.employee_ID CROSS JOIN
                      t_Vendor_Detail_Element

this will output your tables per row


and dont forget to use the count option in order to count your items.


My above code is only to help you understand how the tables can be joined..

This post has been edited by DarenR: 08 May 2012 - 01:43 PM

[modi123_1]

Can you run that structure by me again - it's not making sense.

[theidx]

_______________ ______________ ___________ _______________ _____________
|_tblUsers____| |_tblAnswers__| |_tblAlt__| |_tblQuestion_| |_survey_____|
| userID | | answerAltID | | altID | | questID | | surveyID |
| username | | userID | | altText | | questText | | surveyName |
|_____________| |_____________| |_questID_| |_surveyID____| |____________|

Table: tblUsers
userID
userName

Table: tblAnswers
answerAltID
userID

Table: tblAlt
altID
altText
questID

Table: tblQuestion
questID
questText
surveyID

Table: tblSurvey
surveyID
surveyName