Duplicate query problem

[PhilWB]

I am using the following for a duplicate query that seems to be failing when the [unitnr] is null.
I am a novice programmer, and have only written this database to help out a local charity organisation organise delivery of Christmas hampers, so speedy resolution would be much appreciated.
My SQL code is as follows:
SELECT tblContacts.HouseNr, tblContacts.StreetName, tblContacts.UnitNr, tblContacts.ContactID, tblContacts.FirstName, tblContacts.LastName
FROM tblContacts
WHERE (((tblContacts.HouseNr) In (SELECT [HouseNr] FROM [tblContacts] As Tmp GROUP BY [HouseNr],[StreetName],[UnitNr] HAVING Count(*)>1 And [StreetName] = [tblContacts].[StreetName] And [UnitNr] = [tblContacts].[UnitNr])))
ORDER BY tblContacts.HouseNr, tblContacts.StreetName, tblContacts.UnitNr;
Used the Duplicate Query Wizard to arrive at this code.
Many thanks in advance, Phil

 

[r937]

try this --

select HouseNr
     , StreetName
     , UnitNr
     , ContactID
     , FirstName
     , LastName
  from tblContacts   as FOO
 where 1 
     < (
       select count(*)
         from tblContacts 
        where HouseNr = FOO.HouseNr
          and StreetName = FOO.StreetName 
          and UnitNr = FOO.UnitNr
       )
order 
    by StreetName
     , HouseNr
     , UnitNr

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PhilWB]

Rudy,
This seems to create a query requiring input to run, my idea was a query to check what had been entered into the table to ensure that an address hadn't been entered twice, and to display addresses that had two entries on a form for correction.
Many thanks, Phil

 

[r937]

my query shouldn't require input, it displays the duplicates

are you receiving a prompt?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[PhilWB]

Yes on each field in the select statement

 

[Clipper2000]

Hi PhilWB,

I was wondering if there is a general way to make Access saved the query definition in a nice format layout you had. It seems that sometimes right after I saved the query, it went back to the default format.

 

[PhilWB]

I don't know if it is an addition to the available program, I am using Access 2003. Click on the query you wish to look at, then click on Design then click on View and select SQL View. Also available from the arrow beside the icon that switches view from design to table.
Phil

 

[Clipper2000]

Hi PhilWB,

Oh yes I know this move as I code most of query using the SQL view only.

My challenge is, let's say that I have a query like this:

SELECT field1,
field2,
FROM
Table
ORDER BY
field1

and saved the query definition

However, when I open up the query, it will read something like this:

SELECT field1, field2 FROM Table ORDER BY field1

In other words, all the nice formats (ie, spaces) I put there are gone.

Any way to get around this?

 

[PhilWB]

I think you are looking at Rudy's code as the neat one. Mine is usually in the format you are speaking off.