Combining Query's HELP! @#

[Dest]

I'm in the process of converting an Access DB over to SQL / VB and I need a little help with a couple query's I have..

This one (for example) has 2 parts.. Part A Query's 2 tables based on criteria and pulls out 2 fields from that...

Part B Query's Part A to get a count of one of the rows (variation in another example is the sum of one of the rows)

How can I combine this into 1 Query... Transact-SQL is fine..

HERE is my 2 query's

QUERY A)
SELECT DISTINCT dbo_PROJ_INFO.REGION_CODE, dbo_EVENT_PART.COMPANY_ID
FROM dbo_EVENT_PART INNER JOIN dbo_PROJ_INFO ON dbo_EVENT_PART.PROJECT_KEY = dbo_PROJ_INFO.UNIQUE_KEY
WHERE (((dbo_EVENT_PART.ATTENDED)="Y") AND ((dbo_PROJ_INFO.EV_CANCELLED)="N") AND ((dbo_PROJ_INFO.END_DATE) Between #8/1/2000# And #8/31/2000#));

QUERY B)
SELECT [RO MTrak Report - Event Companies Distinct].REGION_CODE, Count([RO MTrak Report - Event Companies Distinct].COMPANY_ID) AS CountOfCOMPANY_ID
FROM [RO MTrak Report - Event Companies Distinct]
GROUP BY [RO MTrak Report - Event Companies Distinct].REGION_CODE;

[Lafor]

This is a shot in the dark!!

I am not sure exactly what w want to do

It looks like a subquery would do the job...
I could be wrong -- I just give the gist...

SELECT [RO MTrak Report - Event Companies Distinct].REGION_CODE, Count([RO MTrak Report - Event Companies Distinct].COMPANY_ID) AS CountOfCOMPANY_ID
FROM [RO MTrak Report - Event Companies Distinct]
where [RO MTrak Report - Event Companies Distinct].REGION_CODE in (

select A.REGION_CODE, B.COMPANYID
from PROJ_INFO A, EVENT_PART B
where (B.ATTENDED = "Y" and A.EVCANCELLED = "N") AND
and A.ENDDATE between.....)

GROUP BY [RO MTrak Report - Event Companies Distinct].REGION_CODE;

[Dest]

I guess I need to clarify what I have and what I need...

I think what you said Might work.. this is what I have in access now..

QUERY A)
SELECT DISTINCT dbo_PROJ_INFO.REGION_CODE, dbo_EVENT_PART.COMPANY_ID
FROM dbo_EVENT_PART INNER JOIN dbo_PROJ_INFO ON dbo_EVENT_PART.PROJECT_KEY = dbo_PROJ_INFO.UNIQUE_KEY
WHERE (((dbo_EVENT_PART.ATTENDED)="Y") AND ((dbo_PROJ_INFO.EV_CANCELLED)="N") AND ((dbo_PROJ_INFO.END_DATE) Between #8/1/2000# And #8/31/2000#));

That is saved as [RO MTrak Report - Event Companies Distinct]

Then I query that with this
QUERY B)
SELECT [RO MTrak Report - Event Companies Distinct].REGION_CODE, Count([RO MTrak Report - Event Companies Distinct].COMPANY_ID) AS CountOfCOMPANY_ID
FROM [RO MTrak Report - Event Companies Distinct]
GROUP BY [RO MTrak Report - Event Companies Distinct].REGION_CODE

So QUERY B is a subquery of QUERY A... I'd like not to have them in 2 query's but do it somehow via Transact-SQL or the such to put them on the SQL box