Web Development

Ask something
Question Locked

Duplcate records in access 2007

0 helpful
4 replies

by whateb

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table of family members with addresses and tel nos.
Plus a table with eldest child with date of birth, these are related by an ID No.
I created a query to link the two which shows the members of each address sorted by age.
Query works everything OK, however when I run a report based on the query two of the families duplicate themselves all others are OK.
How can this happen?

4 replies

by tony hopkinson

If you had Families keyed by FamilyID MemberID with name and address
and Eldest keyed by MemberID with DOB, it could happen if you had one member in two different families.

Either your join condition is wrong, or you have some duff data.

In the sceanrio above

Select MemberID, Count(*) From Families
would find the rascal as they would have acount > than 1

Select * From
(Select MemberID, Count(*) as NumberOfFamilies From Families) Counts
Where NumberOfFamiles > 1

saves you doing a lot of scrolling.

I'm just guessing but it's this sort of approach that will identify something you hadn't thought of.

HtHs

by whateb

Thanks Tony,
Checked through changed a join and everything fine. Just annoying that you know it's something simple but the brain gets fogged by just keep looking at it.
Eaon

by tony hopkinson

had me scratching my head this morning, similar type of problem.

Bust it up, do some counts, check your assumptions, can be pretty hard to spot this one.

This discussion is closed to new replies.