Help Needed Debugging Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Help Needed Debugging Query

Author

Topic

charliehough
Starting Member

4 Posts

Posted - 2006-09-14 : 09:59:59

I have tried to base this query on an example from Microsoft, but I get the same error every time. All I want to do is to find duplicates based on the first five characters of a particular field and not the entire field. I also want to show all cases where these first five characters are identical AND the entire field from another column is identical.

Here is my query in SQL:

SELECT Combined.NursingHomeName, Combined.City, Combined.Street, Combined.State, Combined.ZipCode, Combined.PhoneNumber, Combined.CertifiedNumberOfBeds, Combined.TotalNumberOfResidents, Combined.PercOfOccupiedBeds, Combined.TypeOfOwnerShip, Combined.LocatedWithinAHospital, Combined.MultiNursingHomeOwnerShip
FROM Combined
WHERE (((Combined.NursingHomeName) In (SELECT [NursingHomeName] FROM [Combined] As Tmp GROUP BY Left([NursingHomeName],5),[City] HAVING Count(*)>1 And Left([NursingHomeName],5)=Left([Combined].[NursingHomeName],5) And [City] = [Combined].[City])))
ORDER BY Combined.NursingHomeName, Combined.City;

The error I get every time is "You tried to execute a query that does not include the specifed expression 'NursingHomeName' as part of an aggregate function." I have tried different variations on this script, but it's the same error every time.

I really hope that someone can help with this, as this effectively kills a massive project that I'm working on.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-14 : 10:20:52

Every output column in an aggregate query must be the subject of either an aggregate function, or included in the GROUP BY clause.

Use a subquery to identify duplicate strings by city, and then join that to the original table to get the full details.

SELECT	Combined.NursingHomeName,
	Combined.City,
	Combined.Street,
	Combined.State,
	Combined.ZipCode,
	Combined.PhoneNumber,
	Combined.CertifiedNumberOfBeds,
	Combined.TotalNumberOfResidents,
	Combined.PercOfOccupiedBeds,
	Combined.TypeOfOwnerShip,
	Combined.LocatedWithinAHospital,
	Combined.MultiNursingHomeOwnerShip
FROM	Combined
	INNER JOIN --Duplicates
		(SELECT Left([NursingHomeName],5) as NHN5
		FROM	[Combined] As Tmp
		GROUP BY Left([NursingHomeName],5),
			[City]
		HAVING Count(*)>1) Duplicates
	on Combined.City = Duplicates.City
	and Left([Combined].[NursingHomeName],5) = Duplicates.NHN5
ORDER BY Combined.NursingHomeName, Combined.City;

"I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!"

charliehough
Starting Member

4 Posts

Posted - 2006-09-14 : 10:38:17

Thanks for the help, but it looks like these suggested changes are causing a different problem now.

I'm doing this in MS Access, and I tried using the script that you suggested, but I get a syntax error in the FROM clause. It first seems to point to the "--" before Duplicates, but if I delete those it then gives me another error and points to the "(" before SELECT in the Inner Join statement.

charliehough
Starting Member

4 Posts

Posted - 2006-09-14 : 12:30:58

Can anyone help with this? I've spent hours searching other forums and SQL expression guidelines, but I can't find anything that relates to what I'm specifically trying to do.

X002548
Not Just a Number

15586 Posts

Posted - 2006-09-14 : 12:49:44

Sure, read the hint link in my sig and post what it asks for

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

charliehough
Starting Member

4 Posts

Posted - 2006-09-14 : 13:01:09

Unfortuantely, I can't follow those directions. I don't have Enterprise Manager. All I'm using is Microsoft Access. I don't have any way of generating the DDL or DML that you're asking for.

I already did my best to include the SQL coding of my query and to explani what I want to do, which is pretty simple. Unfortunately, I'm not an SQL programmer, otherwise this would probably be a very easy query to build.

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-09-14 : 15:45:40

There are slight syntax differences between SQL Server and MS Access.

Try posting your thread on www.dbforums.com, which has a section devoted to Microsoft Access.

"I have HAD it with these muthu-f$#%in' cursors in my muthu-f$#%in' database!"

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-09-14 : 16:18:13

Charlie,

This is a MS SQL Server site. You'd be better off asking in a forum for Access.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources