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?

Robin,

One of the things I do in a situation like this is to copy the table into Excel with a copy and paste. Then, once in Excel, I have a better view of the data and can do "what-ifs" such as sorts and dropping of duplicates and concatenating fields (cells) together when I have a compound index to again do sorts and things to see the data better and analyze the field relationships between rows.

Jeff
----- Original Message -----

I think you need to upload a copy of at least the table to the help wanted files. Duane
> To: MS_Access_Professionals@yahoogroups.com
> From: robinski@mymail.net.au
> Date: Fri, 29 Mar 2013 10:45:12 +1100
> Subject: 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
> >
> >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>

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

--

Jeffrey Park Jones
Excel, Access, Word, Office Expert
Excel and Access, LLC®
http://ExcelAndAccess.Com

919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net

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