Trying to get data by distinct field values

[gadall]

I have a table that has job data, Account_Num, Work_Order_Num, Date, Short_Desc, TechID, QCID, QCPassFail.

In this table there may be multiple records with the same Account_Num and Work_Order_Num, however when it comes time to check the QCPassFail, I need to only count 1 for each unique Account_Num/Work_Order_Num pair.

My problem comes in that I need more than just those 2 fields for reporting purposes so a SELECT DISTINCT on those two fields only won't work.

I've tried using a subquery with WHERE x IN, and I've had no luck trying to use an inner join using the same table.

[Delerna]

So for the accounts that have more than 1 record, what are the rules that you want to apply in order to work out which one of them you want the query to return.
Or are you after something like this
[code=sql]
SELECT a.Account_Num, Ctn_Account,
a.Work_Order_Nu m, Cnt_Order,
Date, Short_Desc, TechID, QCID, QCPassFail
FROM YourTable a
left join
( SELECT Account_Num, Work_Order_Num,
count(Account_N um) as Ctn_Account,
count(Work_Orde r_Num) as Cnt_Order
FROM YourTable
) b on a.Account_Num=b .Account_Num
and a.Work_Order_Nu m=b.Work_Order_ Num
[/code]

[gadall]

Account_Num  Work_Order_Num   Schedule_Date    Short_Desc   TechID  QCID QCPassFail
20661406	 25853100	       2007-10-12       VIDEO DISC  73002   73011  1
20661406	 25853100	       2007-10-12       CHSI DISC	 73002   73011  1

For the purposes of this report, I only need 1 of these records, which one, is irrelevant.

[ck9663]

What kind of report are you generating? If just a count or summary, you don't need the entire record, which could be easier to be implemented in a query.

-- CK

[gadall]

What kind of report are you generating? If just a count or summary, you don't need the entire record, which could be easier to be implemented in a query.

-- CK

I need all the information shown in the records (the actual records have a lot more information) This information is pulled into an excel spreadsheet for a detailed report (the count is done by formula in excel).

[Delerna]

Not sure if this suits all of your data but it works for the data you provide

[code=sql]
SELECT Account_Num,
Work_Order_Num,
count(Account_N um) as Account_Num,
count(Work_Orde r_Num as Work_Order_Num,
Schedule_Date,
first(Short_Des c) as Short_Desc,
TechID,
QCID,
QCPassFail
FROM YourTable
GROUP BY Account_Num,Wor k_Order_Num,Sch edule_Date,Tech ID,QCID,QCPassF ail
[/code]

Or this may suit better
[code=sql]
SELECT Account_Num,
Work_Order_Num,
count(Account_N um) as Account_Num,
count(Work_Orde r_Num as Work_Order_Num,
first(Schedule_ Date) as Schedule_Date,
first(Short_Des c) as Short_Desc,
first(TechID) as TechID,
first(QCID) as QCID,
first(QCPassFai l) as QCPassFail
FROM YourTable
GROUP BY Account_Num,Wor k_Order_Num
[/code]

[gadall]

Working solution:

 SELECT DISTINCT Account_Num,
   
            Work_Order_Num,
   
            Schedule_Date,
   
            Assigned_Installer,
            
            MIN(Short_Description) as Short_Desc,
   
            QCID,
   
            QCPassFail
  
      FROM QCData 
  
	  WHERE QCPassFail IS NOT NULL AND Schedule_Date >= '3/1/2008' AND Schedule_Date <= '3/31/2008'
	  
	  GROUP BY Schedule_Date, Account_Num, Work_Order_Num, Assigned_Installer, QCID, QCPassFail