Assistance with finding duplicates in SQ 2005

[vcujackson]

I have the following code. THe first creates a table called Make_Batches. The second is an attempt to find duplicate batches if the batch number repeats itself. THe problem is since I chose the count of the giftid in the first table, it is not a true field so the dupicate code will not accept. When I run the second query I get the error message, invalid column name giftId. WHy am I getting that message and what is the solution

------------
/*Table name is Make_Batches*/
SELECT COUNT(giftid) AS CountOfID
,giftbatch,giftclass1,gifteffdat,gifttype
FROM
gifts
WHERE (gifttype = 'y' OR
gifttype = 'g' OR
gifttype = 'b')
AND (gifteffdat >= '2008-01-01 00:00:00')
--and giftbatch = '2530'
GROUP BY giftbatch,giftclass1,
gifteffdat, gifttype

-------------------- Find duplicate batches
SELECT * FROM
Make_Batches inner join batch_number
on giftbatch = table_val
--As Tmp
GROUP BY giftbatch,giftid
HAVING Count(*)>1

 

[Madawc]

I don't follow your SQL, but if this is being used with Crystal you also don't need more than the first selection. Group the records by batch number, and also do a summary count. Use Group Selection to suppress any group with a count of one. What shows will be the duplicates.

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP