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?

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
>
>
>