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
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (21) |
Tidak ada komentar:
Posting Komentar