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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
I want all single adddresses marked as 'primary'
Click to expand...

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'
Click to expand...

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

Tim F
 
Back
Top