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

Kamis, 28 Maret 2013

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

Graham,

Duane's query produced a result with no data.

I have no duplicates and still cannot establish an index.

Cheers.

Robin

At 28/03/2013 12:33 PM, you wrote:
>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]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>