Still still have duplicates when running the Distinct in Access

[apopa]

I only want the unique values (unique on rcnumber) in my table but keep getting duplicates. Any solutions other than DISTRICT ??

SELECT DISTINCT OFFice.Rcnumber, OFFice.[SAP number], OFFice.Outlet, OFFice.Business, OFFice.BusinessType, OFFice.Address, OFFice.City, OFFice.Prov, OFFice.Pcode, OFFice.PCode_FlexDeliv, OFFice.Phone, OFFice.Contact, OFFice.[Old RC], OFFice.FSA, OFFice.Node, OFFice.Open, OFFice.Close, OFFice.Renewal, OFFice.ContractYear, OFFice.Expiry, OFFice.Extension, OFFice.Division, OFFice.Division_Fr, OFFice.[Lang Pref], OFFice.[Lang Cap], OFFice.[Lang Off], OFFice.Type, OFFice.[Type Desc], OFFice.[Type Desc French], OFFice.PSC_Indicator, OFFice.[Contract Type], OFFice.[Current Image], OFFice.Code2, OFFice.CORP_PRIV, OFFice.CPCUnion, OFFice.[National Acct], OFFice.[Parent RC], OFFice.[Parent Title], OFFice.Manager, OFFice.[Parent Sname], OFFice.[Parent Fname], OFFice.[Supt CC], OFFice.[Supt Title], OFFice.SuptName, OFFice.[Parent2 RC], OFFice.Director_CostCentre, OFFice.[Parent2 Title], OFFice.Director, OFFice.[Parent2 Sname], OFFice.[Parent2 Fname], OFFice.[Parent3 RC], OFFice.GM_CostCentre, OFFice.[Parent3 Title], OFFice.GM, OFFice.[Parent3 Sname], OFFice.[Parent3 Fname], OFFice.[Rev Level], OFFice.Rev2015, OFFice.Rev2014, OFFice.Rev2013, OFFice.Rev2012, OFFice.Rev2011, OFFice.Rev2010, OFFice.Rev2009, OFFice.Rev2008, OFFice.Rev2007, OFFice.Rev2006, OFFice.Rev2005, OFFice.Rev2004, OFFice.Rev2003, OFFice.Rev2002, OFFice.Rev2001, OFFice.AVG_YEARS, OFFice.[Weekly Hours], OFFice.[Hours Issues], OFFice.[Hours Desc], OFFice.[Hours Desc_Fr], OFFice.[Hours NumDays], OFFice.DTPOTonight, OFFice.InfoBoard, OFFice.InfoBoardPosterUpdated, OFFice.AVG_RG, OFFice.Urb_Rur, OFFice.[Op Fee], OFFice.[Rev Sub], OFFice.[Serv Sub], OFFice.[Dealer PrMode Boxes], OFFice.TaxClass, OFFice.[Co-Located], OFFice.CGRP, OFFice.CGRP_E, OFFice.CGRP_F, OFFice.RPS_Transition, OFFice.RPS_Transition_F, OFFice.[RPS Indicator], OFFice.RPS, OFFice.hasCARO, OFFice.[RPS Connection Type], OFFice.[RPS 12 M Avg], OFFice.[RPS Curr M Avg], OFFice.[RPS Last 12 Sales], OFFice.[RPS Nbr Months], OFFice.RPS_SAP, OFFice.PAD, OFFice.PAD_Freq, OFFice.PAP, OFFice.[Caro Avg], OFFice.[Caro Days], OFFice.PostMasterProvided, OFFice.Lease_expiry, OFFice.Latitude, OFFice.Longitude, OFFice.lambert_x, OFFice.lambert_y, OFFice.Market_Type, OFFice.Market_Area, OFFice.Market, OFFice.FED_Name, OFFice.FED_Name_Fr, OFFice.FEDMP, OFFice.FED_Party, OFFice.FED_Party_Fr, OFFice.CUSTOMER_S, OFFice.RevGroupNa, OFFice.Cut_Off_Time, OFFice.Cut_Off_Time_World, OFFice.TotalCallFor, OFFice.AvgDailyCallFor, OFFice.CallForEndDate, OFFice.CallForStartDate, OFFice.AvailableToNetwork, OFFice.RC_NO
FROM OFFice;

[PhilOfWalton]

I am guessing that there are about 90 fields specified in your query.
for the DISTINCT to give a single value, ALL 90 fields must be the same.

If you only want RCNumver your SQL should be:-

SELECT DISTINCT Rcnumber FROM OFFice;
 

I would add that a table with that many fields is almost certainly wrong and the database has not been normalised. It looks like an Excel sheet. Seeing a years from 2001 to 2015 indicates that the table STRUCTURE needs changing each year. Very Bad!!!.

There seem to be a number of references to a Parent. I'm guessing this should be in another table. There are references to people's names. Another table.

There are lots of articles about normalisation.May I suggest you read these and review your DB. Without that, you will not get the benefit of a relational database.

If you see repeated TEXT information on different records in a table it almost certainly is wrong.

A very broad guide is each independent entity should have it'sown table. So the Office, its address & contact numbers and things that are intrinsically a part of that Office in 1 table.

People:- Names, titles, job description, dates of birth sex etc exist in their own right, so another table.

Phil