Count no of records in table using query

B

Boss

Hi,

I have a simple table shown below:

Name Exception Age
John Good 24
John fine 26
John Good 25
Peter Fine 17
Peter Good 29
Peter Good 17
Jack Good 15
Jack Good 11

I need a consolidated report which contain following information:

Number of exception done by John
Number of good exception done by John
Number of fine exception done by John

Number of exception done by peter
Number of good exception done by peter
Number of fine exception done by peter

Can this be done by querying the database. Please help me, i am stuck over
this from quite sometime.

Thanks!
Boss
 
J

Jerry Whittle

Sounds like a job for a crosstab query. Put in the proper table name below:

TRANSFORM Count(TblBoss.[Age]) AS TheTotal
SELECT TblBoss.[Name],
Count(TblBoss.[Age]) AS TheTotals
FROM TblBoss
GROUP BY TblBoss.[Name]
PIVOT TblBoss.[Exception];
 
M

m3

Try this to see if it works for you:-

SELECT SampleTable.Name, Count(*) as TotalException,
SUM(IIF(SampleTable.Exception='Good',1,0)) as Good,
SUM(IIF(SampleTable.Exception='Fine',1,0)) as Fine FROM SampleTable Group by
SampleTable.Name;
 
B

Boss

Thanks!!
This was really amazing...
Thanks a lot..
Boss

Jerry Whittle said:
Sounds like a job for a crosstab query. Put in the proper table name below:

TRANSFORM Count(TblBoss.[Age]) AS TheTotal
SELECT TblBoss.[Name],
Count(TblBoss.[Age]) AS TheTotals
FROM TblBoss
GROUP BY TblBoss.[Name]
PIVOT TblBoss.[Exception];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Boss said:
Hi,

I have a simple table shown below:

Name Exception Age
John Good 24
John fine 26
John Good 25
Peter Fine 17
Peter Good 29
Peter Good 17
Jack Good 15
Jack Good 11

I need a consolidated report which contain following information:

Number of exception done by John
Number of good exception done by John
Number of fine exception done by John

Number of exception done by peter
Number of good exception done by peter
Number of fine exception done by peter

Can this be done by querying the database. Please help me, i am stuck over
this from quite sometime.

Thanks!
Boss
Click to expand...
Click to expand...
 
J

Jerry Whittle

Crosstabs do have problems. If you are going to use it to feed a form or
report, it could break. Forms and reports are expecting set field names. If
you add "Jim" or delete "John" from the table, there could be problems.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Boss said:
Thanks!!
This was really amazing...
Thanks a lot..
Boss

Jerry Whittle said:
Sounds like a job for a crosstab query. Put in the proper table name below:

TRANSFORM Count(TblBoss.[Age]) AS TheTotal
SELECT TblBoss.[Name],
Count(TblBoss.[Age]) AS TheTotals
FROM TblBoss
GROUP BY TblBoss.[Name]
PIVOT TblBoss.[Exception];
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Boss said:
Hi,

I have a simple table shown below:

Name Exception Age
John Good 24
John fine 26
John Good 25
Peter Fine 17
Peter Good 29
Peter Good 17
Jack Good 15
Jack Good 11

I need a consolidated report which contain following information:

Number of exception done by John
Number of good exception done by John
Number of fine exception done by John

Number of exception done by peter
Number of good exception done by peter
Number of fine exception done by peter

Can this be done by querying the database. Please help me, i am stuck over
this from quite sometime.

Thanks!
Boss
Click to expand...
Click to expand...
Click to expand...
 
Top