Query Problem - columns with different criteria

[tcsikany]

I have been working on a database in MS Accesss. I am trying to generate a query in the SQL view but the introduction to SQL class I am taking did not cover how to incorporate multiple WHERE conditions.

Here is the query so far...

SELECT intFID, COUNT(*) AS TotalAudits, AVG(strActualScore/strPossibleScore*100) AS FaciltiyAverage
FROM tblQAMain
GROUP BY intFID;

This works perfectly but my difficulty is that I also want to add 4 more columns to this query to display the following data as part of the results:

COUNT (*) AS TotalMicrobial WHERE strATID = 'AT1'

AVG (strActualScore/strPossibleScore*100) AS AverageMicrobial WHERE strATID = 'AT1'

COUNT (*) AS TotalVisual WHERE strATID = 'AT2'

AVG (strActualScore/strPossibleScore*100) AS AverageVisual WHERE strATID = 'AT2'

I would like the entire query to group the results by intFID.

Thank you in advance for any assistance you can give.

Tina

[tcsikany]

When someone is new to databases and SQL it is very rude to post responses like this. It may be a simple answer for some people. Most likely I will no longer be posting for assistance on this site.

[Master Rattley]

Standard protocol

[crunchie]

When someone is new to databases and SQL it is very rude to post responses like this. It may be a simple answer for some people. Most likely I will no longer be posting for assistance on this site.

Please note that the response given was not indicative of the high standard of help normally given at Daniweb.
Note that the poster can no longer post.

[tcsikany]

Thank you.

I was able to get my query to work and generate the results I wanted but I then tried to put in a WHERE clause to filter a date range. The query works but it prompts twice for the start date and end date before returning the results. I have played with this and cannot seem to get it to only prompt once. Any suggestions?

Here is my query:

SELECT tblQAMain.intFID, tblFacilities.strFacilityName,
Count(*) AS TotalAudits, Avg([intActualScore]/[intPossibleScore]*100) AS FacilityAverage,
Sum(IIf([strATID]='AT1',1,Null)) AS TotalMicrobial,
Avg(IIf([strATID]='AT1',[intActualScore]/[intPossibleScore]*100,Null)) AS AverageMicrobial,
Sum(IIf([strATID]='AT2',1,Null)) AS TotalVisual,
Avg(IIf([strATID]='AT2',[intActualScore]/[intPossibleScore]*100,Null)) AS AverageVisual
FROM tblFacilities INNER JOIN tblQAMain ON tblFacilities.intFID=tblQAMain.intFID
WHERE ((tblQAMain.dtmDate BETWEEN [Enter Start Date] AND [Enter End Date]))
GROUP BY tblFacilities.strFacilityName, tblQAMain.intFID;

Thank you in advance for any assistance you can provide.