Web Development



Duplcate records in access 2007

By whateb ·
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?

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

Depends on your query and tables

by Tony Hopkinson In reply to Duplcate records in acces ...

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.


Collapse -

Duplicates in access 2007 report

by whateb In reply to Depends on your query and ...

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.

Collapse -

Happens to us all this one

by Tony Hopkinson In reply to Duplicates in access 2007 ...

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.

Related Discussions

Related Forums