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?
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.
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.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.