Help Needed Debugging Query
-
Posted - 09/14/2006 : 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.
-
You may try this
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
WHERE Left([NursingHomeName],5) = Left([Combined].[NursingHomeName],5)
And [City] = [Combined].[City]
GROUP BY Left([NursingHomeName],5),[City]
HAVING Count(*) > 1)
ORDER BY Combined.NursingHomeName, Combined.City;
Ram
-
I'm still getting the same error. "You tried to execute a query that does not include the specifed expression 'NursingHomeName' as part of an aggregate function."
I'm guessing that there has to be a simpler and effective way to find duplicates based on a partial match of the first five characters in the NursingHomeName column. But since I have very limited SQL coding ability, I'm completely stuck.
-
Try this.
Select Combined.NursingHomeName, Combined.City, Combined.Street, Combined.State, Combined.ZipCode, Combined.PhoneNumber
from Combined , (
Select Left([NursingHomeName],5) as left5NursingHomeName,[City],Count(*) as Counts
from Combined
group by Left([NursingHomeName],5),[City]
having count(*) > 1 ) Dupe
where Left([Combined.NursingHomeName],5) = left5NursingHomeName and ltrim(rtrim(Combined.City)) = ltrim(rtrim(Dupe.City))
Thanks
Sreejith
-
Thanks for the help! All I had to do was add an ORDER BY statement at the end, and I also made sure that all my columns were selected, instead of just the few you included. I got exactly the results I was looking for.
This is a big breakthrough. I just hope that I'll be able to adapt this same scripting for use with other queries and possibly expanding the matching to include the state as well. As soon as I try to add anything else to the matching or the final ORDER BY statement, I get an error about illegal bracketing for the original Select Left statement. There were a number of cases where there were matching names and cities, but the states were different. Being able to narrow this down to the city AND state matches would save a bit of manual work.
-
Charle,
Are you writting the SQL code yourself? If you use the Design Window for queries, I think you might find this quite a bit easier.
Vic
[font="Comic Sans MS"]Vic[/font]
www.vicrauch.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply