Question re: 'group by' and 'count' function in Access 2003

[Guest]

Objective: separate single address from double address for employees.

Table has two fields. PERSONID (FK) represented by number. ADDRESSID (PK)
represented by autonumber.

PERSONID number represents employee. ADDRESSID autonumber represents either
their primary or secondary address.

I want all single adddresses marked as 'primary' and all double ddresses
marked as 'primary and secondary'

I have tried several ways for several days to achieve the stated oblective
without success. Any suggestions are welcome.

Thanks

richforsandy

 

[David F Cox]

Off the top of my head, and with minimal thinking time:-

make a grouped query and select the first record. Link that to the update
query to insert "primary"
run another update query to update anything not primary to secondary

there probably is a better way

 

[Tim Ferguson]

I want all single adddresses marked as 'primary'

UPDATE MyTable AS o
SET o.AddrType = "Primary"
WHERE 1=(
SELECT COUNT(*)
FROM MyTable AS i
WHERE o.AddressID = i.AddressID
)

and all double ddresses
marked as 'primary and secondary'

How do you tell which address is the primary one out of x addresses?

Tim F