Help! Too many queries

[blade6247]

Hi

I need to create a report to produce the number of complaints / compliments based on at least 19 different criteria in one table.

Essentially each one of the 19 criteria have 3 options - Yes, No & Partly and I need to return a figure for each.

This will require a ludicrous amount of queries - about 60! Is there any way to cut down on this by coding into a command button in SQl that can link to a report?

 

[PHV]

A starting point:
SELECT GroupField
, Sum(IIf(crit1="Yes",1,0)) As 1_Yes, Sum(IIf(crit1="No",1,0)) As 1_No, Sum(IIf(crit1="Partly",1,0)) As 1_Part
, Sum(IIf(crit2="Yes",1,0)) As 2_Yes, Sum(IIf(crit2="No",1,0)) As 2_No, Sum(IIf(crit2="Partly",1,0)) As 2_Part
, ....
FROM yourTable
GROUP BY GroupField

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[OhioSteve]

I can think of two ways to achieve your goal:

1) Use a M-to-M relationship
Lets say that you want to are doing a survey. I am guessing that you have one table, survey. That table contains a field for each question. An alternative would be to express this as a many-to-many relationship. That would fix the problem on a systemic level. Then coding your report would be a snap. Here is an example of the tables:

survey
surveyDate
surveyID (pk)
etc

surveyDetails
questionID (fk) (the two id fields form a composite pk)
surveyID (fk)
answer

questions
questionID (pk)
questionText (pk)

You can then base the query on surveyDetails. Here is an example of the sql:

SELECT
questions.questionText AS question,
surveyDetails.answer,
Count(surveyDetails.surveyID) AS total
FROM surveyDetails
LEFT JOIN questions ON
surveyDetails.questionID = questions.questionID
GROUP BY questions.questionText, surveyDetails.answer;

 

[blade6247]

Ok - I've used PH's method in the query and it works great, until I come to a point where I have to use 2 criteria in the IIf statement - Access reports either I've written the syntax incorrectly or the argument is too complex

 

[PHV]

Why not posting what you've tried ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blade6247]

select groupfield

,Sum(IIf(satcomments="Yes" and daily life="Yes",1,0)) AS dailycountyes, Sum(IIf(satcomments="No"and daily life = "Yes",1,0)) AS dailycountno, Sum(IIf(satcomments="Partly"and daily life = "Yes",1,0)) AS dailycountpart

from visit table
group by groupfield

I've also tried

If [satcomments] = "Yes" and [daily life] = "Yes" then
satcommentsyes = satcommentsyes +1
end if

Sorry - my knowledge of VB is very poor as I haven't used it in a few years

 

[lespaul]

So you have a table kind of like this:

PkID Field    Field1    Field2   Field3   Field4
    1          Yes       No       Yes    Partly
    2           No        No       Yes       No
    3          Yes      Yes        No     Partly

are each of the fields "questions" and the Yes No and Partly are the answers to the question? The reason your query is so long and complicated is because this structure is not normalized (see The Fundamentals of Relational Database Design for more information on normalization).

Another solution is to create a query that normalizes your data and then use that query as the source of the report.

The query to normalize would be something like this:

SELECT PkIDField, 'Field1', Field1 FROM tableName
UNION
SELECT PkIDField, 'Field2', Field2 FROM tableName
UNION
SELECT PkIDField, 'Field3', Field3 FROM tableName
UNION
SELECT PkIDField, 'Field4', Field4 FROM tableName

the results of this query will be:

PkID Field    FieldName    FieldValue   
    1          Field1         Yes       
    1          Field2          No       
    1          Field3         Yes    
    1          Field4        Partly
    2          Field1          No 
    2          Field2          No       
    2          Field3         Yes
    2          Field4          No
    3          Field1         Yes
    3          Field2         Yes
    3          Field3          No
    3          Field4        Partly

Now the query to get the counts would be something like this:

SELECT FieldName, FieldValue, Count(FieldValue) FROM queryName Group by FieldName, FieldValue

Leslie

 

[PHV]

select groupfield
,Sum(IIf(satcomments="Yes" and daily life="Yes",1,0)) AS dailycountyes, Sum(IIf(satcomments="No"and daily life = "Yes",1,0)) AS dailycountno, Sum(IIf(satcomments="Partly"and daily life = "Yes",1,0)) AS dailycountpart
This is SQL, not VB.


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[blade6247]

Hi lespaul

I've tried what you've suggested - but access returns the error - syntax error - missing operator.

SELECT Form No, 'Daily Life', Daily Life FROM visit table
UNION

SELECT Form No, '', "Contact", contact FROM visit table
UNION

SELECT Form No, 'Clothing',clothing FROM visit table
UNION

etc.etc.

 

[PHV]

Replace this:
SELECT Form No, 'Daily Life', Daily Life FROM visit table
By this:
SELECT [Form No], 'Daily Life', [Daily Life] FROM [visit table]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

try this:

SELECT [Form No], "Daily Life" As Type, [Daily Life] As Value FROM visit table
UNION
SELECT [Form No], "Contact", contact FROM visit table
UNION
SELECT [Form No], "Clothing", clothing FROM visit table




Leslie