Count / Choose ??

[Tezdread]

Hi all,

I've been trying to get a query to work for the last few weeks now but not having much luck...Someone in another forum posted the below SQL but they wasn't able to offer any advice with it and I couldn't get it working in the format it was in.

Database Info:

1 MasterTable - Holds all question responses and user info (department etc)
- MasterTable has 58 Fields - UID, 4 User Fields and 53 Question Fields
5 Lookup tables - Deparments, Job role etc

-----------------------------------------------------------------------------------------

What I'm trying to achive is this:

One Form with 4 combo boxes - Department, Job Role, Time with Company and Survey Date (e.g. Q1 2005) and a button that runs the report.

So if all the combo boxes were left blank the report would return everything, the user would control what's on the report by using the combo boxes, so, if I select Support Teams from the Departments combo the report would show all the answer totals for the chosen teams only.

I can do this part ok.

The problem I have is extracting this info from the table. Like I said, each question has a possible 4 answers and I need to know how many people said Strongly Agree to question 1 and / or Agree to question 2.

This is the SQL someone gave me, but I couldn't get this working and they wouldn't explain how to edit it for what I need.

SELECT [Survey Date], first("1_SA") AS Response, Department, 
Sum(Choose([Q1],1,0,0,0)) AS Q1_Qty, Sum(Choose([Q2],1,0,0,0)) 
AS Q2_Qty FROM tblSurvey Group By  [Survey Date], Department  
Union SELECT [Survey Date],first("2_A") AS Response, Department, 
Sum(Choose([Q1],0,1,0,0)) AS Q1_Qty, Sum(Choose([Q2],0,1,0,0)) 
AS Q2_Qty FROM tblSurvey Group By  [Survey Date], Department 
Union SELECT [Survey Date], first("3_D") AS Response, Department, 
Sum(Choose([Q1],0,0,1,0)) AS Q1_Qty, Sum(Choose([Q2],0,0,1,0)) 
AS Q2_Qty FROM tblSurvey Group By  [Survey Date], Department 
Union SELECT [Survey Date], first("4_DA") AS Response, Department, 
Sum(Choose([Q1],0,0,0,1)) AS Q1_Qty, Sum(Choose([Q2],0,0,0,1)) 
AS Q2_Qty FROM tblSurvey Group By  [Survey Date], Department

From that, as we couldn't get it working we striped it down to this

SELECT MasterTable.Department, MasterTable.TrainDev1, Sum(1) AS Total
FROM MasterTable
GROUP BY MasterTable.Department, MasterTable.TrainDev1;

Now this works, in some kind of way but as soon as we tried entering info for a second question it didn't work.

Can someone please have a look at my database and help me out?

 

[Tezdread]

Forgot the databse...

 

[sportsguy]

Working for Question 1

SELECT MasterTable.Department, MasterTable.TrainDev1, ResponseLookup.Response AS TD1, Count(MasterTable.ID) AS Total
FROM MasterTable INNER JOIN ResponseLookup ON MasterTable.TrainDev1 = ResponseLookup.ResponseAbbreviated
GROUP BY MasterTable.Department, MasterTable.TrainDev1, ResponseLookup.Response;


more to follow

sportsguy

 

[sportsguy]

Working for Question 2

SELECT Working_for_question1.Department, Working_for_question1.TD1, Working_for_question1.Total, ResponseLookup.Response, Count(MasterTable.TrainDev2) AS CountOfTrainDev2
FROM (Working_for_question1 INNER JOIN MasterTable ON (Working_for_question1.TrainDev1 = MasterTable.TrainDev1) AND (Working_for_question1.Department = MasterTable.Department)) INNER JOIN ResponseLookup ON MasterTable.TrainDev2 = ResponseLookup.ResponseAbbreviated
GROUP BY Working_for_question1.Department, Working_for_question1.TD1, Working_for_question1.Total, ResponseLookup.Response
HAVING (((Working_for_question1.Department) Like "*" & [Forms]![searchform]![department] & "*"));

sopy and paste the first two into the existing queries, and then run the second query. . . I think you will have your answer, as well as being able to see what i did in the QBE grid. . .

how does this work for you?

sportsguy

 

[Tezdread]

thanks sportsguy, I'll get on to that in the morning and let you know how it goes

 

[Tezdread]

Hi sportsguy,

I don't seem to be getting this right...

I copied your code as suggested and this is what I noticed.

When I run the Macro that runs both queries one after the other, I get a popup box prompting me for a parameter value for MasterTable.ID

Can we make it so this doesn't prompt for a value?

Also whatever I enter into this popup doesn't give me the correct results when the query returns data...There is only one Customer services when there should be 2 listed?

The filter option doesn't work either, so when I select Support Teams from the combobox I still see all teams?

Is it possible to have one query only and in a way that all the comboboxes on the search form can be used for filtering?

 

[Tezdread]

It doesn't look like I'm ever going to get the help required...So, how much will it cost me to get this sorted?