Query doesn't do what i want it do???

[brin]

I have only the one table named "Error log" and the query i am trying to run is a having two count fields. I am using two fields from the table 'Member Of Staff' and 'Welcome Pack Status'. I would like to do the following, but where i have stated error count i want the other count to use 'complete' in the crieria field.

SELECT [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status], Count([Error Log].[Welcome Pack Status]) AS Errors
FROM [Error Log]
GROUP BY [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status]
HAVING ((([Error Log].[Welcome Pack Status])="error"));

 

[Pat Hartman]

The following will give you the counts you need:

SELECT [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status], Count(*) As StatusCount
FROM [Error Log]
GROUP BY [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status];

PS for future reference, speces and other special characters should not be used for table and column names even though Access allows it.

 

[brin]

Thanks that is sort of what i'm after! At the moment the view i'm getting is:

Member of staff, Status, Count.

How is possible to have the counts like this:

Member of staff, Status, Count(error), Count(Complete).

Thanks.

 

[RV]

>How is possible to have the counts like this:
Member of staff, Status, Count(error), Count(Complete).<

SELECT [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status], Count(*) As StatusCount
FROM [Error Log]
WHERE [Error Log].[Welcome Pack Status]="error"
GROUP BY [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status]
UNION
SELECT [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status], Count(*) As StatusCount
FROM [Error Log]
WHERE [Error Log].[Welcome Pack Status]="complete"
GROUP BY [Error Log].[Member Of Staff], [Error Log].[Welcome Pack Status];

 

[brin]

Thanks but this doesn't seem to work its displays the data exactly the same way as before.

There must be a way to have two counts on one query???? But have the counts in seperate columns rather than rows??

 

[Pat Hartman]

Use the wizard to create a crosstab query.

 

[brin]

Thanks Pat thats worked most appreciated.