Selecting a COLUMN from another QUery

[smartrider]

Here is the issue :
I have this query :
"SELECT dbo_Insured.person_id, Min((Month([date_ordered]))) AS Expr1, (Year([date_ordered])) AS Expr2
FROM (dbo_Cases INNER JOIN dbo_Pipeline ON dbo_Cases.cases_id = dbo_Pipeline.cases_id) INNER JOIN dbo_Insured ON dbo_Cases.insured_id = dbo_Insured.insured_id
WHERE (((dbo_Pipeline.pipeline_code_id)=54))
GROUP BY dbo_Insured.person_id, (Year([date_ordered]));"

which results in 3 columns

Person_id,MOnth,Year

I want to select the person_id from the above query using SQL (not Access) so that i will display

Count(person_id), MOnth, Year


I can do this in Access but just saving the first query and then creating another one. BUt PLease assist me in doing the same task in SQL
I tried the following :

Select Count(DISTINCT(IU.person_id)),Min(Month(Pi.date_ordered))
FROM Pipeline Pi, Insured IU,Cases Ca
Where Ca.insured_id = IU.insured_id and
Ca.cases_id = Pi.Cases_id and
Pi.pipeline_code_id = '54' and
EXISTS (SELECT DISTINCT(Insured.person_id) as p, Min(Month(pipeline.date_ordered))
FROM (Cases INNER JOIN Pipeline ON Cases.cases_id = Pipeline.cases_id) INNER JOIN Insured ON Cases.insured_id = Insured.insured_id
WHERE (((Pipeline.pipeline_code_id) = '54'))
GROUP BY Insured.person_id ,Month(PipeLine.date_ordered))
Group By Month(Pi.date_ordered);


but this didn't work.

PLease help!!!

THanks.

 

[rac2]

In SQL, saved queries are called VIEWS. So you can save a query on SQL Server with the CREATE VIEW command

CREATE VIEW PersonsYearInsured
AS
SELECT dbo_Insured.person_id, Min((Month([date_ordered]))) AS Expr1, (Year([date_ordered])) AS Expr2
FROM (dbo_Cases INNER JOIN dbo_Pipeline ON dbo_Cases.cases_id = dbo_Pipeline.cases_id) INNER JOIN dbo_Insured ON dbo_Cases.insured_id = dbo_Insured.insured_id
WHERE (((dbo_Pipeline.pipeline_code_id)=54))
GROUP BY dbo_Insured.person_id, (Year([date_ordered]))

The view can be used just like a table. You could

SELECT Count(person_id),
       Expr1 AS MOnth,
       Expr2 AS Year
FROM PersonsYearInsured