[AccessD] Top1

John is working in a Sql Server query, not Access for this query...
iirc First is not a valid keyword in Tsql,

You could however use the Min keyword for the following solution

SELECT MIN(PKID), Address1, Zip5, Zip4
FROM #TEST
GROUP BY address1, zip5, Zip4


/*
Below is the sample data I created to make it happen
*/
CREATE TABLE #TEST (PKID INT , Address1 VARCHAR(50), Zip5 VARCHAR(5),
Zip4 VARCHAR(4))

INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('1','123 ST', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('2','123 ST', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('3','123 ST', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('4','123 ST', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('5','123 ST', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('1','123 RD', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('2','123 RD', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('3','123 Rd', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('4','123 RD', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('5','123 RD', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('1','123 AVE', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('2','123 AVE', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('3','123 AVE', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('4','123 AVE', '12345', '1234')
INSERT INTO #TEST (PKID, Address1, zip5, zip4)
VALUES('5','123 AVE', '12345', '1234')



On 1/26/07, Gustav Brock <Gustav at cactus.dk> wrote:
> Hi John
>
> How about using First for the name and Group By for the address?
>
> /gustav
>
> >>> jwcolby at colbyconsulting.com 26-01-2007 16:01:06 >>>
> I have a dataset where I have multiple addresses, multiple people living at
> the same address.  "Address" is defined as "Address1,zip5,zip4".  Is it
> possible to directly create a result set with just the first record at each
> address?
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...