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?

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
> > From: robinski@mymail.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
>
>
>