Duplicates 1

[blairclw]

I have a report that has the employee name, address, city, state but now I need to add the county and that is in another table. When I go to link the table on PersonID it gives me duplicates. Anyway to make that stop?

 

[jdemmi]

You more than likely have a join issue. Can you check your join on the 2 tables and post it here, including both tables structure

-- Jason
"It's Just Ones and Zeros

 

[blairclw]

Ok, I assume your talking about my tables so I've typed them in below. The Join Type is INNER and LinkType is equal. I'm linked by PersonIdNo. When I add the second table (Persons_Addresses) is when I get duplicates.

FIRST TABLE

xxx_PERSONS_PRIMARYADDRESSES_CURR

PersonIdNo
FirstName
MiddleName
LastName
SocialSecurityNo
Birthdate
PersonAddress1
PersonAddress2
PersonAddressCity
PersonAddressStateProvince
PersonAddressPostcode

NEXT TABLE:

PERSON_ADDRESSES

PersonIdNo
PersonAddressSeqNo
PersonAddressTypeIDNo
PersonAddressType
PersonAddress1
PersonAddress2
PersonAddressCity
PersonAddressStateProvinceIdNo
PersonAddressStateProvince
PersonAddressCountryCodeIdNo
PersonAddressCountryCode
PersonAddressCountyIdNo
PersonAddressCounty
PersonAddressPostcode
PersonAddressFromEffectDate
PersonAddressToEffectDate
PersonAddressChangeDate

 

[lbass]

Instead of linking just on personID, try linking on city and postcode and the other matching address fields from each table.

-LB

 

[gluon]

It looks like (based on the name) that the table xxx_PERSONS_PRIMARYADDRESSES_CURR
contains the "primary" addresses that are "current" for each person.
It also looks like xxx_PERSONS_PRIMARYADDRESSES_CURR is a view (or snapshot) of PERSON_ADDRESSES and PERSONS, containing information from both these tables.

As there is a ID field called {PERSON_ADDRESSES.PersonAddressStateProvinceIdNo}, you might be lucky enough to have a table with this as the primary key - probably called {STATE_PROVINCE.StateProvinceIdNo}, or something similar. If your luck holds, this table would contain the country, or at least the CountryCodeIdNo.
So, you could skip joining to PERSON_ADDRESSES altogether, and just inner join xxx_PERSONS_PRIMARYADDRESSES_CURR to STATE_PROVINCE on {xxxStateProvince}.
If possible, talking to the database developer about this problem is the best longterm solution - putting the Country in with the xxx_PERSONS_PRIMARYADDRESSES_CURR table/view is a simple change, and would make life much easier for future reports.