I'm sure this is easy - my minds gone blank!

[addy]

OK

I have a table which has the following fields included:

ProjectCode
Contractor

Each ProjectCode can have more than one Contractor assigned to it, for example:

ProjectCode Contractor
001.000 ACME
001.000 J. Smith Construction
001.000 F Bloggs Builders

002.000 ACME
002.000 F Bloggs Builders

etc. etc.

What I want to do, is count how many ProjectCode's a particular Contractor is assigned to. So, for example, if I ran the query for ACME I would get a result of 2, if I ran it for J. Smith Construction, I would get a result of 1.

I'm having a complete blank and have't got anywhere.

Thanks in advance.

 

[Crowley16]

select count(projCode), contractor from table group by contractor

--------------------
Procrastinate Now!

 

[SkipVought]

Hi,

select Contractor, Count(ProjectCode) from ttt
group by Contractor

Skip,

_{[red]Be Advised![/red] A man's home is his castle...
in a Manor of speaking.}

 

[addy]

OK, I got it!

SELECT Count([01tblProjectControl].ProjectNumberCode) AS CountOfProjectNumberCode
FROM 01tblProjectControl INNER JOIN 10tblProjPropEstimateOriginalEQs ON [01tblProjectControl].ControlNumber = [10tblProjPropEstimateOriginalEQs].ControlNumber
WHERE ((([10tblProjPropEstimateOriginalEQs].Contractor)="ACME"));