Suggestions / better ways to approach?

[longhair]

Have a table with the following fields:
job number (integer primary key)
date repaired (date)
repaired by (text non-unique)
tre (text non-unique)

What is needed is a report that lists the 'repaired by' and [count]number of repairs, [count]'tre' that is set to yes and the 'job number' if tre is set to yes. I can get what is needed with the following 2 SQL statements but was wondering if there is a way to combined them into one statement.
SQL #1
SELECT [Tool Room Operator Table].[Repaired By], Count('Repaired By') AS Total_Dies
FROM [Tool Room Operator Table]
WHERE ((([Tool Room Operator Table].[Date Repaired]) Between [Enter Start Date] And [Enter End Date]))
GROUP BY [Tool Room Operator Table].[Repaired By];
SQL #2
SELECT [Tool Room Operator Table].[Repaired By], [Tool Room Operator Table].[Job Number], count('TRE') AS Total_TREs
FROM [Tool Room Operator Table]
WHERE ((([Tool Room Operator Table].[Date Repaired]) Between [Enter Start Date] And [Enter End Date]) AND (([Tool Room Operator Table].TRE)="yes&quot)
GROUP BY [Tool Room Operator Table].[Repaired By], [Tool Room Operator Table].[Job Number];
Any suggestions would be appreciated.
TIA

Regards,

longhair

 

[dodgychris]

Try using the following :

TRANSFORM Count([Tool Room Operator Table].[job number]) AS [CountOfjob no]
SELECT [Tool Room Operator Table].tre, Count([Tool Room Operator Table].[job no]) AS [Total Of job no]
FROM [Tool Room Operator Table]
where [Tool Room Operator Table].[date repaired]Between [Enter Start Date] And [Enter End Date]
GROUP BY [Tool Room Operator Table].tre
PIVOT [Tool Room Operator Table].[repaired by];

It's a crosstab query and should give you all the information you need...

good luck -

Chris Dodgy Chris
-----------------------------------

confucious say : better to save a mans life than to build 7 storey pagoda

 

[longhair]

Chris,
Thanks a bunch, gave me most of what I need. One other question - is there a way to make the WHERE part of the clause dynamic?
currently I have WHERE ((([tool room operator table].[Date Repaired]) Between [start] And [end]))
this produces an error "The Microsoft Jet database engine does not recognize '[start]' as a valid fieldname or expression.
If I change to WHERE ((([tool room operator table].[Date Repaired]) Between #02/01/03# And #02/28/03#)) it runs fine. But I don't want the users in the query. Much rather pop up a the data entry boxes.

Regards,

longhair

 

[longhair]

Error fixed. There were instructions on MS' website

regards,

Dave