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?

Jeff,

I have done that and the learned nothing probably because of my ignorance.

Robin

At 29/03/2013 01:16 PM, you wrote:
>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]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>