Finding and naming duplicates

Hi All,
not sure if anyone can help with this.

I have a column called VNTR24 which has 10+ digit numbers - these are not 
unique and we want to be able to identify people with matching VNTR24 numbers 
to cluster them.

In excel I've used the following formula: =COUNTIF(A:A,A2)>1 
to return in the next column a TRUE or FALSE based on whether two records 
are matched on VNTR24 number.

I need to do the same in Access 2007, returning either a "TRUE" or "FALSE" 
if the records are clustered/matched on this VNTR24 or not in an UPDATE 
query!?

Can anyone help please? 
NB: I use design view mostly as am just learning SQL.
Many thanks in advance for you advice.
Heather 
0
Utf
1/18/2010 11:06:02 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
611 Views

Similar Articles

[PageSpeed] 52

Heather, can I suggest that storing this in a yes/no field (other than in a 
temporary table) is not a good idea in a database. If more records are added 
or some are deleted, the data in this yes/no field is actually wrong. You 
really don't want to design a database so that it will go wrong!

(Technically, the rules of data normalization mean you must not store 
dependent data like that.)

Assuming that the records are sorted by a unique field (e.g. an AutoNumber 
primary key), you could use a subquery to determine whether there are any 
lower ID values for the VNTR24. If not, you want FALSE; if so, it's a 
duplicate so you want TRUE. If subqueries are new, here's an introduction:
    http://allenbrowne.com/subquery-01.html

There may be other simpler solutions, e.g. if you create a report, the text 
boxes have a Hide Duplicates property to suppress the value when it's a 
duplicate.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Heather" <Heather@discussions.microsoft.com> wrote in message 
news:CEE766C6-76AF-4EB6-A7BB-B864E669E9BF@microsoft.com...
> Hi All,
> not sure if anyone can help with this.
>
> I have a column called VNTR24 which has 10+ digit numbers - these are not
> unique and we want to be able to identify people with matching VNTR24
> numbers to cluster them.
>
> In excel I've used the following formula: =COUNTIF(A:A,A2)>1
> to return in the next column a TRUE or FALSE based on whether two records
> are matched on VNTR24 number.
>
> I need to do the same in Access 2007, returning either a "TRUE" or "FALSE"
> if the records are clustered/matched on this VNTR24 or not in an UPDATE
> query!?
>
> Can anyone help please?
> NB: I use design view mostly as am just learning SQL.
> Many thanks in advance for you advice.
> Heather 

0
Allen
1/18/2010 12:30:26 PM
Hi Allen,

thanks for the link which is helpful in general, but I couldn't apply any of 
the principals to my specific query. I'm still learning SQL and query design 
so have a long way to go!

If you have any ideas for a specific Q to address this it would be much 
appreciated.
Thanks
Heather


"Allen Browne" wrote:

> Heather, can I suggest that storing this in a yes/no field (other than in a 
> temporary table) is not a good idea in a database. If more records are added 
> or some are deleted, the data in this yes/no field is actually wrong. You 
> really don't want to design a database so that it will go wrong!
> 
> (Technically, the rules of data normalization mean you must not store 
> dependent data like that.)
> 
> Assuming that the records are sorted by a unique field (e.g. an AutoNumber 
> primary key), you could use a subquery to determine whether there are any 
> lower ID values for the VNTR24. If not, you want FALSE; if so, it's a 
> duplicate so you want TRUE. If subqueries are new, here's an introduction:
>     http://allenbrowne.com/subquery-01.html
> 
> There may be other simpler solutions, e.g. if you create a report, the text 
> boxes have a Hide Duplicates property to suppress the value when it's a 
> duplicate.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> 
> "Heather" <Heather@discussions.microsoft.com> wrote in message 
> news:CEE766C6-76AF-4EB6-A7BB-B864E669E9BF@microsoft.com...
> > Hi All,
> > not sure if anyone can help with this.
> >
> > I have a column called VNTR24 which has 10+ digit numbers - these are not
> > unique and we want to be able to identify people with matching VNTR24
> > numbers to cluster them.
> >
> > In excel I've used the following formula: =COUNTIF(A:A,A2)>1
> > to return in the next column a TRUE or FALSE based on whether two records
> > are matched on VNTR24 number.
> >
> > I need to do the same in Access 2007, returning either a "TRUE" or "FALSE"
> > if the records are clustered/matched on this VNTR24 or not in an UPDATE
> > query!?
> >
> > Can anyone help please?
> > NB: I use design view mostly as am just learning SQL.
> > Many thanks in advance for you advice.
> > Heather 
> 
> .
> 
0
Utf
1/20/2010 11:46:01 AM
Save a query like this:
    SELECT VNTR24,
    Count(ID) AS CountOfID,
    Min(ID) AS MinOfID
    FROM Table1
    GROUP BY VNTR24;

Save. Now create another query using this one and your main table as input 
'tables.' Drag your main table's ID, and drop onto CountOfID in the upper 
pane of table design. Access creates a line joining the 2 tables. 
Double-click this join line. Access pops up a dialog with 3 options. Choose 
the one that says:
    All records from Table1, and any matches from Query1.

Now type an expression like this into a fresh column in the Field row:
    IsDuplicate: (MinOfID Is Null)

Your query will show True (-1) for duplicates, and False (0) for the first 
row (assuming you sort by the ID.)
-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Heather" <Heather@discussions.microsoft.com> wrote in message 
news:8D769E55-F8FC-454C-9426-1B97C8D9D6E8@microsoft.com...
> Hi Allen,
>
> thanks for the link which is helpful in general, but I couldn't apply any 
> of
> the principals to my specific query. I'm still learning SQL and query 
> design
> so have a long way to go!
>
> If you have any ideas for a specific Q to address this it would be much
> appreciated.
> Thanks
> Heather
>
>
> "Allen Browne" wrote:
>
>> Heather, can I suggest that storing this in a yes/no field (other than in 
>> a
>> temporary table) is not a good idea in a database. If more records are 
>> added
>> or some are deleted, the data in this yes/no field is actually wrong. You
>> really don't want to design a database so that it will go wrong!
>>
>> (Technically, the rules of data normalization mean you must not store
>> dependent data like that.)
>>
>> Assuming that the records are sorted by a unique field (e.g. an 
>> AutoNumber
>> primary key), you could use a subquery to determine whether there are any
>> lower ID values for the VNTR24. If not, you want FALSE; if so, it's a
>> duplicate so you want TRUE. If subqueries are new, here's an 
>> introduction:
>>     http://allenbrowne.com/subquery-01.html
>>
>> There may be other simpler solutions, e.g. if you create a report, the 
>> text
>> boxes have a Hide Duplicates property to suppress the value when it's a
>> duplicate.
>>
>> -- 
>> Allen Browne - Microsoft MVP.  Perth, Western Australia
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>>
>> "Heather" <Heather@discussions.microsoft.com> wrote in message
>> news:CEE766C6-76AF-4EB6-A7BB-B864E669E9BF@microsoft.com...
>> > Hi All,
>> > not sure if anyone can help with this.
>> >
>> > I have a column called VNTR24 which has 10+ digit numbers - these are 
>> > not
>> > unique and we want to be able to identify people with matching VNTR24
>> > numbers to cluster them.
>> >
>> > In excel I've used the following formula: =COUNTIF(A:A,A2)>1
>> > to return in the next column a TRUE or FALSE based on whether two 
>> > records
>> > are matched on VNTR24 number.
>> >
>> > I need to do the same in Access 2007, returning either a "TRUE" or 
>> > "FALSE"
>> > if the records are clustered/matched on this VNTR24 or not in an UPDATE
>> > query!?
>> >
>> > Can anyone help please?
>> > NB: I use design view mostly as am just learning SQL.
>> > Many thanks in advance for you advice.
>> > Heather
>>
>> .
>> 
0
Allen
1/20/2010 2:43:32 PM
Hi Allen,
thanks for that. I didn't manage to get the subQ to work, the first Q did, 
but not the second for some reason.

But, trying the second Q in isolation in a new Q did work, so problem solved.

Thanks for your help
Heather
0
Utf
2/1/2010 11:54:02 AM
Reply:

Similar Artilces:

failed to find host name from ip address
Please help a numpty in need!! I have an account set up in outlook express 2003 which works fine. I have now set up another account, and every time i send an email wifrom the new account it comes back as undeliverable, with the following: failed to find host name from ip address Any ideas please?? "g00nor" <g00nor@discussions.microsoft.com> wrote in message news:4D1B4492-A265-4030-8390-9CCDF4A8BEBC@microsoft.com... > Please help a numpty in need!! > I have an account set up in outlook express 2003 which works fine. > I have now set up another account, and every ...

Auto Complete Names
We just starting using Outlook 2003 and we can't get email addresses to come up automatically when you type the first letters of either a name or email address. The appropriate box is checked for this to occur automatically. What other options do we have? Thanks in advance. If, indeed, you just started using Outlook 2003, I would expect this behavior. You must send enough message to populate your autocompletion caches before you see it working. -- Russ Valentine [MVP-Outlook] "jpquilts@mindspring.com" <anonymous@discussions.microsoft.com> wrote in message news:1075501...

Converting Leads to Contacts Duplicate errors
We cannot convert leads to contacts and are getting duplicate errors. There are no duplicates. We are running 4.0 rollup 11. And we get this error everytime we try on CRM server: Event Type: Warning Event Source: ASP.NET 2.0.50727.0 Event Category: Web Event Event ID: 1309 Date: 8/3/2010 Time: 2:35:01 PM User: N/A Computer: CRM-APP Description: Event code: 3005 Event message: An unhandled exception has occurred. Event time: 8/3/2010 2:35:01 PM Event time (UTC): 8/3/2010 7:35:01 PM Event ID: 19b210ef795f42129b2af3d7b447c2bc Event sequence: 157 Event occurrence: 1 Event detail code: 0 Applic...

How do I Look up an Array/Table name
I have an Excel spreadsheet that uses a LOOKUP (array) function. I have since made added rows to the array, or range of data, that is referenced by the LOOKUP function. So now I need to redefine the range but I can't remember how to find the range that is named in the formula. How do I find this range so I can change it? insert --> name --> define "dnamertz" <dnamertz@discussions.microsoft.com> ���g��l��s�D:9F494493-4666-4937-9006-CD3975FCC10B@microsoft.com... >I have an Excel spreadsheet that uses a LOOKUP (array) function. I have > since made added ...

finding a cell from numerical coordinates
I am given coordinates like 10,100 and 125,200 - is there a way to tell Excel to find the corresponding cell? Can I change the top row letters to numbers? On Wed, 8 Jul 2009 13:38:01 -0700, Chip <Chip@discussions.microsoft.com> wrote: > >I am given coordinates like 10,100 and 125,200 - is there a way to tell Excel >to find the corresponding cell? Can I change the top row letters to numbers? Try this =OFFSET(A1, 10-1, 100-1) and =OFFSET(A1, 125-1, 200-1) or, in general =OFFSET(A1, x-1, y-1) where x and y are your coordinates. Hope this helps / Lars-�ke Hi, Ch...

Chart with Named Range error
Using Excel 2007, creating a chart using named ranges as inputs when I try ti enter the named range as a source I get a formula error "the formula you typed containes an error..." So I setup a simple scenario to dumlicate the problem. named a range of cells "Test" and entered arbitrary numbers in the cells of the range. Insert chart, Right click on the chart and select "select data" then "add" In the box type "=Test" or used the "Use in formula/paste name" tool. Select ok and get the formula error. I dont think I had any problems w...

Print Business Card in Card Style with Company Name
Using Outlook 2007 SP2. Viewing the contacts in card style format shows the companyname field. But when printing, the companyname is not printing. How can I get it to print with the company name? ...

Can't find draw toolbar??
I need to find the Draw toolbar so I can add lines to a file. No Draw toolbar up on top??? Rightclick within a toolbar and select DRAWING. "oldferd" wrote: > I need to find the Draw toolbar so I can add lines to a file. No Draw > toolbar up on top??? ...

Excel2000: Is there a way to use name as parameter for ODBC query from Excel table
Hi I have an Excel workbook where dates, started from 01.01.2003 and up to some year in future (2011 at moment), along with some other information (i.e. workday/weekend/holiday, working hours etc.), are listed. This table is read into another workbook, using ODBC query. But the problem is, that I don't need all those future dates. I can't use any functions exept MIN, MAX, SUM and COUNT in query from Excel files, but is there a way to pass a name as parameter. P.e. I define a name: CurrDat=TODAY() and use it in query's WHERE condition, like .... WHERE MyTable.Datefield Is Not Null...

Excel prob = Compile error cant find project or library
when i open excel 2002 on my xp pro pc it opens up with te error Compile error cant find project or library then takes me into the VB, the section it takes me to is talking bout setlanguagestrings? but when you close this screen excel works fine. any help please thanks in advance David Hi, Maybe an add-in with a programming error is bothering you. 1 Tools, Add-ins 2 note which are checked 3 uncheck all (but one) 4 restart XL 5 if no error, check the next one and repeat from step 4 Got the error? uncheck the one you last checked. Not an add-in? It will be a (hidden) workbook t...

Find & Replace
In many cells, = 12 * XXX, where XXX vary for all the cells I want to use find & replace to change cells to XXX, omitting the =12* what should I enter in the replace ? Thanks Excel 2003 I am assuming that XXX represents numbers... Removing =12* from the formula is same as dividing the values by 12... If this is true then enter 12 in any empty cell, copy the cell, select the cells (I am assuming that you can do this without much difficulty) with the formula, choose PASTE SPECIAL and choose Divide and then click OK. "PL" wrote: > In many cells, = ...

Please help with finding correct functions!!!
I am trying to configure Excel to balance my checkbook. Column A is Trans. Type, Column B is Date, Column C is Description of Trans., Column D is Debits, Column E lists if items are posted, Column F is Debits and Column G is Balance. I'm trying to get Column D (debits) to subtract from Column G (balance) and get a new balance (new column? H?) and get Column F (credits) to add to Column G (balance to get a new balance (again, new column? H?). I am not computer illiterate and I should be able to figure this out by myself. But I've been working on finding the right functions (l...

Where to find previous updates for Office 2008?
Hi I tried to install the service pack for office 2008 but when I started installation it gave an error because I need some extra updates not installed previously. Where can I find all the updates available for Office 2008? How to determine which updates should I need? Thanks Which Service Pack did you try to install -- there are 2 of them for Office 2008. Both include the interim updates which preceded them so you shouldn't need to go any further back than SP1 (12.1.0) plus SP2 (12.2.0) followed but 12.2.1 (the latest interim update). You should be able to run Check fo Updates fo...

Methodology
I'm looking for some tips for how to manage naming conventions within and across Visio files. If you have a subprocess that is used in/called by multiple processes, what would be a good way to name the "start" and "end" shapes? For example, if you were modeling something simple such as "serve breakfast", "serve lunch", and "serve dinner", all three would have a subprocess called "set table". The "set table" process is the same regardless of the main process that calls it. Do you just call them "start&...

Couldn't Find c:\windows\system32\system.mdw Message
Loaded Access 97 on my new Vista Machine and got the above message when I tried to start Access. How do I fix this ? Vista has more stringent rules about who, and how you can write to the System32 (or Windows for that matter) folder. If you have another machine with and unsecured system.mdw file try to copy it to your new one. If it lets you, you are fine, if not you'll need to work with someone in a Vista newsgroup to figure out what the problem is. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "JWalker" <JWalker...

Merging Duplicate Contacts
Is there an option in V3.0 to merge selected accounts/contacts/leads that have been selected as duplicates? yes, from the Contact List or from an Advanced Find for contacts, you can select multiple contacts and click the Merge icon at the top of the list (the merge icon looks like two small pieces of paper becoming one larger piece of paper). When you click it, a merge dialog will appear and help you with the process. Sorry for the weak description of the icon by the way :) Dave "Mandy" <Mandy@discussions.microsoft.com> wrote in message news:32468F52-66E7-4738-9148-5...

Searching the first name only
Hi there.... Im having a column of names.....and i usually use "Find / Replace".... but then i would like to find out if there's other method which i can use.....because E.g. when i wanted to search the 1st name containing "Kelly"...it would search everything name which contain "kelly" in it.....i only want the first name........ i tried the match case, or using *kelly* in the Find/Replace...but it still doesnt work...... Pls help me asap....thanks a lot Hi Kelly, To find cells only containing Kelly, select the 'Find entire cells only...

Change name of Wksheet to reflect a month auto.
Hi, is it possible to have a macro change the name of a sheet to a certain month reflected in a cell? If so, how do I do it? Thx! val Assuming your cell (in this case, A1) contains a date value: activesheet.name = format(range("A1").value,"mmmm") -- Olly "dolphinv4" <anonymous@discussions.microsoft.com> wrote in message news:e77401c43ca2$5faf0290$a301280a@phx.gbl... > Hi, > > is it possible to have a macro change the name of a sheet > to a certain month reflected in a cell? If so, how do I > do it? > > Thx! > val Hi Put ...

How can I find an invalid reference?
I have a spreadsheet that used to have a chart in it. I removed the chart and the entire column that it resided in. My spreadsheet is just a report. It has no calculations it it. Yet every time I resize a column I get a "a formula in this worksheet contains one or more invalid references" message. How do I find the offending formula if none exists? thanks Try doing an "Edit, Goto, Speical, Formulas, Ok" and see what cell it highlights. Search for "#ref" (without the quotes). And Bill Manville's FindLink program will find them, too: http://www.oal...

I am trying to find a spreadsheet for staffing forcasts
I have been asked to create a chart showing staffing waves or forcasts. Does anyone have a source for these types of items or example they are willing to share? it really depends on the level of detail you need to show. Staff in total, by level or type, capacity, etc. Can you be more specific? "Beverly" wrote: > I have been asked to create a chart showing staffing waves or forcasts. > Does anyone have a source for these types of items or example they are > willing to share? ...

OL 2003 Find emails read, but not replied to?
In OL2003 How do I search for emails I have read but not replied to? Thanks in anticipation :) I can't find a way in Advanced Find to do that but you can try sort a view of your Inbox by the icon column. It will group messages forwarded, replied, etc. "Broons Bane" <nochance@youmustbejoking.com> wrote in message news:eq%23UMEybFHA.2520@TK2MSFTNGP09.phx.gbl... > In OL2003 > > How do I search for emails I have read but not replied to? "Vince Averello [MVP-Outlook]" <vince@omegageek.com> wrote in message news:Odl23PybFHA.3844@tk2msftngp1...

Sorting Data Employee name time
I have a worksheet of data (Employee names and times from job sheets) which i need to sort by employee name and then totalise times to find total hours worked for the job. There name may apear several times or not at all. I need a total for each person then to be written to the database sheet through the database template add in. Hi for this I would use a pivot table. <See: http://www.cpearson.com/excel/pivots.htm http://peltiertech.com/Excel/Pivots/pivotstart.htm http://www.contextures.com/xlPivot02.html http://www.ozgrid.com/Excel/excel-pivot-tables.htm -- Regards Frank Kabel Frank...

OE shuts down during "find message"
I have tried reinstalling the program to solve this and it doesn'thelp. Every time I go into "find message" for a message search, I get this error message: AppName: msimn.exe / AppVer: 6.0.2800.123 / ModName: directdb.dll / Offset: 0000556c / and Express shuts down. Please advise. "R. Visser" <anonymous@discussions.microsoft.com> wrote in message news:427f01c42b66$a0f7f600$a001280a@phx.gbl... > I have tried reinstalling the program to solve this and it > doesn'thelp. Every time I go into "find message" for a > message search, I g...

Just upgraded to Office 2008, can't find Entourage database
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Power PC Email Client: imap I just upgraded from Office 2004 to 2008, completed the install, but when I attempt to open Entourage, I get the message: &quot;Entourage Cannot access your data. To attempt to fix the problem, rebuild your database.&quot; The problem is, no database is listed on the database utility. Another posting suggested that the database is too corrupted to even be found, but it opens up and works fine in Entourage 2004. What to do? Dear Joel: joelm@officeformac.com wrote: >Version: 20...

named ranges #2
Hello gurus, This is a fun project, or it could be with a little help for you fine folks. I am establishing named ranges to create dynamic charting. Since I will be doing 100+ charts for multiple groups at my company, I’ve set up a standard format for each set of named ranges that generate the charts. However, I am not that far along and defining a new set of named ranges (approx 12 per chart = 1,200 names) 100 times over has proven quite daunting, even with the use of the name manager (http://www.jkp-ads.com/officemarketplacenm-en.asp). Each group will have one workbook, each with...