ACCESS FEED: RE: [MS_AccessPros] How do I prevent duplication of records?

Rabu, 27 Maret 2013

RE: [MS_AccessPros] How do I prevent duplication of records?

Hi Robin

Did Duane's query identify the combination(s) with duplicates?

Just a small tip: If you are setting up such a composite unique index and
you cannot save the table because of duplicates, don't exit without saving -
instead, change the Unique property back to "No" and exit with a save. Then
find and deal with the duplicates and, when you've finished, you simply need
to change Unique back to "Yes" instead of recreating the whole index.

Cheers,

Graham

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
Sent: Thursday, 28 March 2013 14:18
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] How do I prevent duplication of records?

Duane,

OOOPS! Yes the field is "DistPostID".

The primary key is "tblDistOfficeID"

Names applied many years ago and would be different if starting now!

Many thanks again,

Robin

At 28/03/2013 10:54 AM, you wrote:
>Robin,
>
>You mentioned PostID as one of the fields in your group of three but
>it isn't referenced in any SQL either before that I am aware of or
>now. Think the field name is actually DistPostID which I didn't know
>if it was the primary key.
>
>Are any of the field values null?
>
>What do you get if you try:
>
> SELECT tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID,
> tDistrictPostHolders.DistPostID, Count(*) AS NumOfRecords
> FROM tDistrictPostHolders
> GROUP BY tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID,
> tDistrictPostHolders.DistPostID
> ORDER BY 4 DESC;
>
>
>Duane
>
>
>----------------------------------------
> > To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
> > From: robinski@mymail.net.au <mailto:robinski%40mymail.net.au>
> > Date: Thu, 28 Mar 2013 08:41:36 +1100
> > Subject: RE: [MS_AccessPros] How do I prevent duplication of records?
> >
> > At 28/03/2013 08:03 AM, you wrote:
> > >Which are the three fields that shouldn't allow duplicates?
> >
> > > > > > > I have a table which includes the fields: "MemberID" and
> > "YearID" and
> > > > > >"PostID
> >
> > >Which field is the primary key field in the table?
> >
> > "tblDistOfficeID"
> >
> > >I would have expected a simple totals query like:
> > >
> > >SELECT MemberID, YearID, [Other Field], Count(*) As NumOfRecords
> > >FROM tDistrictPostHolders
> > >GROUP BY MemberID, YearID, [Other Field]
> > >HAVING Count(*) >1;
> >
> > The SQL was generated by the Wizard.
> >
> > I have revised your SQL to:
> >
> > SELECT tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID,
> > tDistrictPostHolders.DistPostID, Count(*) AS NumOfRecords
> > FROM tDistrictPostHolders
> > GROUP BY tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID,
> > tDistrictPostHolders.DistPostID
> > HAVING (((Count(*))>1));
> >
> > Which also returns no records.
> >
> > Cheers,
> >
> > Robin
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]