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

Rabu, 27 Maret 2013

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

Which are the three fields that shouldn't allow duplicates? Which field is the primary key field in the table?

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;

Duane Hookom MVP
MS Access

----------------------------------------
> To: MS_Access_Professionals@yahoogroups.com
> From: robinski@mymail.net.au
> Date: Thu, 28 Mar 2013 07:49:36 +1100
> Subject: RE: [MS_AccessPros] How do I prevent duplication of records?
>
> Thanks Duanne,
>
> I am, as usuual, out of my depth!
>
> SELECT tDistrictPostHolders.DistPostID,
> tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID,
> tDistrictPostHolders.tblDistOfficeID
> FROM tDistrictPostHolders
> WHERE (((tDistrictPostHolders.DistPostID) In (SELECT [DistPostID]
> FROM [tDistrictPostHolders] As Tmp GROUP BY
> [DistPostID],[MemberID],[YearID] HAVING Count(*)>1 And [MemberID] =
> [tDistrictPostHolders].[MemberID] And [YearID] =
> [tDistrictPostHolders].[YearID])))
> ORDER BY tDistrictPostHolders.DistPostID,
> tDistrictPostHolders.MemberID, tDistrictPostHolders.YearID;
>
> Cheers,
>
> Robin
>
>
> At 28/03/2013 07:32 AM, you wrote:
> >Robin,
> >What is the SQL view of the "Find Duplicates" query?
> >
> >Duane Hookom MVP
> >MS Access
> >
> >----------------------------------------
> > > To: MS_Access_Professionals@yahoogroups.com
> > > From: robinski@mymail.net.au
> > > Date: Thu, 28 Mar 2013 07:27:14 +1100
> > > Subject: RE: [MS_AccessPros] How do I prevent duplication of records?
> > >
> > > G'Day Graham.
> > >
> > > I have run a "Find Duplicates" query on the table to prove that all
> > > entries are unique.
> > >
> > > I have built the index on the three fields as you described.
> > >
> > > An error message advises that " .. duplicates would be established .. .,."
> > >
> > > Now I am really confused !!!!
> > >
> > > Cheers,
> > >
> > > Robin
> > >
> > > At 27/03/2013 08:40 PM, you wrote:
> > > >Hi Robin
> > > >
> > > >You need to create a composite index comprising the three fields.
> > > >
> > > >In table design view, click the Indexes button to show the
> > floating Indexes
> > > >window.
> > > >
> > > >In the left column (Index Name) of the first blank row, type a name, such
> > > >"Member_Year_Post", and in the second column (Field Name) select MemberID.
> > > >
> > > >In the properties below, change Unique to "Yes".
> > > >
> > > >In the next two rows, select YearID and PostID for Field Name (leave Index
> > > >Name blank).
> > > >
> > > >You will now have an index which will disallow two records with the same
> > > >combination of these three fields.
> > > >
> > > >Cheers,
> > > >Graham
> > > >
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > From: robinski@mymail.net.au
> > > > > Date: Wed, 27 Mar 2013 11:52:53 +1100
> > > > > Subject: [MS_AccessPros] How do I prevent duplication of records?
> > > > >
> > > > > I have a table which includes the fields: "MemberID" and "YearID" and
> > > >"PostID".
> > > > >
> > > > > How do I prevent duplication of the combination of the three fields
> > > > > berceuse data entry is not by Access form?
> > > > >
> > > > > Data entry is on a remote server by ASP pages.
> > > > >
> > > > > Many Thanks.
> > > > >
> > > > > Robin Chapple
> > > >
> > > >
> > > >
> > > >------------------------------------
> > > >
> > > >Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>