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

Jumat, 29 Maret 2013

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

John,

I need to hide myself away. What a huge mistake I made!

I loaded the WRONG FILE. The original was the
"Club Posts" table. It should have been the
"District Posts" table. I have changed it.

I will apply the same criteria to Club Posts when this one is correct.

Wearing sackcloth and ashes.

Robin

At 29/03/2013 06:54 PM, you wrote:
>Robin-
>
>I don't see a "DistPostID" field in that table. I do find MemberID, YearID,
>and ClubPostID, and when I run this query:
>
>SELECT tClubPostHolders.MemberID, tClubPostHolders.YearID,
>tClubPostHolders.ClubPostID, Count(tClubPostHolders.tblClubPostHoldersID) AS
>CountOftblClubPostHoldersID
>FROM tClubPostHolders
>GROUP BY tClubPostHolders.MemberID, tClubPostHolders.YearID,
>tClubPostHolders.ClubPostID
>ORDER BY Count(tClubPostHolders.tblClubPostHoldersID) DESC;
>
>… I see tons of duplicates. Here are just a few examples:
>
>MemberID YearID ClubPostID CountOftblClubPostHoldersID
>963 78 12 4
>2035 81 12 2
>1514 81 6 2
>3389 81 11 2
>
>
>John Viescas, Author
>Microsoft Access 2010 Inside Out
>Microsoft Access 2007 Inside Out
>Microsoft Access 2003 Inside Out
>Building Microsoft Access Applications
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>
>
>--------------------------------------------------
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Friday, March 29, 2013 4:48 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] How do I prevent duplication of records?
>
>
>Thanks Duane,
>
>I have uploaded the table in a file called "Index Problem"
>
>Robin
>
>At 29/03/2013 01:01 PM, you wrote:
> >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]
> >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>