SQL select fields multiple recordssame table

[straybullet]

I am trying to select fields from multiple records in the same table as a single line. The table (SQL) has separate records for each task for each job. Im lost on how to have the 4 separate entries appear as the same "record".
My latest attempt (not functioning...) is the following:

SELECT AMDEVTDATA.UDB_JobNum AS [Job Number], AMDEVTDATA.UDB_HomeBuyer AS [Homebuyer], AMDEVTDATA.UDB_Subdivision AS [Development],

(select AMDEVENTS.CompletionTime AS [APIH] where AMDEVENTS.TaskDesc='All Permits in Hand' and  AMDEVENTS.PctComplete=100 and AMDEVENTS.CompletionTime between '%STARTDATE%' AND '%ENDDATE%'),

(select AMDEVENTS.CompletionTime AS [Building Permit] from amdevents as amd2 where amd2.AMDEVENTS.TaskDesc='Order and receive building permit' and  AMDEVENTS.PctComplete=100),

(select AMDEVENTS.CompletionTime AS [Zoning Permit] from amdevents as amd3 where amd3.AMDEVENTS.TaskDesc='Order and receive zoning permit' and  AMDEVENTS.PctComplete=100),

(select AMDEVENTS.CompletionTime AS [Dev Permit] from amdevents as amd4 where amd4.TaskDesc='Order and receive development permit' and  amd4.PctComplete=100)

FROM  AMDEVTDATA 
INNER JOIN AMDEVENTS ON AMDEVENTS.EvRefNum = AMDEVTDATA.EvRefNum 
ORDER BY [Job Number]

Basically, I need to show the Development permit completion date, the Building permit completion date, the Zoning permit date and the All Permits in Hand completion date for each job.

Thank you in advance!

Let them hate - so long as they fear... Lucius Accius

 

[gmmastros]

The data in the events table represents the classic cross tab query. So, to accomplish this query, you'll want to build the cross table query and then make it be a derived table. Like this...

Select  AMDEVTDATA.UDB_JobNum AS [Job Number], 
        AMDEVTDATA.UDB_HomeBuyer AS [Homebuyer], 
        AMDEVTDATA.UDB_Subdivision AS [Development],
        APIH,
        [Building Permit],
        [Zoning Permit],
        [Dev Permit]
From    AMDEVTDATA 
        Inner Join (
          Select  evRefNum
                  Min(Case When TaskDesc = 'All Permits in Hand' 
                                And PctComplete=100
                           Then CompletionTime
                           End) As APIH,
                  Min(Case When TaskDesc = 'Order and receive building permit'
                                And PctComplete=100
                           Then CompletionTime
                           End) As [Building Permit],
                  Min(Case When TaskDesc = 'Order and receive zoning permit' 
                                And PctComplete=100
                           Then CompletionTime
                           End) As [Zoning Permit],
                  Min(Case When TaskDesc = 'Order and receive development permit'
                                And PctComplete=100
                           Then CompletionTime
                           End) As [Dev Permit]
          From    AMDEVENTS 
          Group By EvRefNum
          ) As A 
          On AMDEVTDATA.EvRefNum = A.EvRefNum

This is untested.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[straybullet]

Thank you for your help George!

Let them hate - so long as they fear... Lucius Accius