I am attempting to create a simple recordset that would return the
number of duplicates that exist in a table with a single column. For
example if I had a table like the following:
ID Reference Amount
1 123456 1.00
2 123456 2.00
3 123 1.00
I would like to get the following result:
ID Reference Amount RecCount
1 123456 1.00 2
2 123456 2.00 2
3 123 1.00 1
Please help!
Thanks,
Shawn
2  2265 
On 13 Jun 2005 15:04:05 -0700, Sygen wrote: I am attempting to create a simple recordset that would return the
number of duplicates that exist in a table with a single column. For
example if I had a table like the following:
ID Reference Amount
1 123456 1.00
2 123456 2.00
3 123 1.00
I would like to get the following result:
ID Reference Amount RecCount
1 123456 1.00 2
2 123456 2.00 2
3 123 1.00 1
Please help!
Thanks,
Shawn
Hi Shawn,
Method 1:
SELECT a.ID, a.Reference, a.Amount,
(SELECT COUNT(*)
FROM MyTable AS b
WHERE b.Reference = a.Reference)
FROM MyTable AS a
Method 2:
SELECT a.ID, a.Reference, a.Amount, COUNT(*)
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Reference = a.Reference
GROUP BY a.ID, a.Reference, a.Amount
Method 3:
SELECT a.ID, a.Reference, a.Amount, b.Cnt
FROM MyTable AS a
INNER JOIN (SELECT Reference, COUNT(*) AS Cnt
FROM MyTable
GROUP BY Reference) AS b
ON b.Reference = a.Reference
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
On 13 Jun 2005 15:04:05 -0700, Sygen wrote: I am attempting to create a simple recordset that would return the
number of duplicates that exist in a table with a single column. For
example if I had a table like the following:
ID Reference Amount
1 123456 1.00
2 123456 2.00
3 123 1.00
I would like to get the following result:
ID Reference Amount RecCount
1 123456 1.00 2
2 123456 2.00 2
3 123 1.00 1
Please help!
Thanks,
Shawn
Hi Shawn,
Method 1:
SELECT a.ID, a.Reference, a.Amount,
(SELECT COUNT(*)
FROM MyTable AS b
WHERE b.Reference = a.Reference)
FROM MyTable AS a
Method 2:
SELECT a.ID, a.Reference, a.Amount, COUNT(*)
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Reference = a.Reference
GROUP BY a.ID, a.Reference, a.Amount
Method 3:
SELECT a.ID, a.Reference, a.Amount, b.Cnt
FROM MyTable AS a
INNER JOIN (SELECT Reference, COUNT(*) AS Cnt
FROM MyTable
GROUP BY Reference) AS b
ON b.Reference = a.Reference
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: Sebastian |
last post by:
The following query needs about 2 minutes to complete (finding dupes)
on a table of about 10000 addresses. Does anyone have an idea on how
to speed this up ?
Thanks in advance !!!
Sebastian
|
by: Mark |
last post by:
I have a table about people containing 25 fields. The table contains the usual
fields - first, last, address, city, state and zip. There is no primary key.
These fields all have data with the exception of a few records missing zip. A
person may be in one to five records in the database. If a person is in multiple
records, the other fields in the table in each record for that person may or may
not contain data. I have two problems:
1. I...
|
by: ChadDiesel |
last post by:
I appreciate the help on this group. I know I've posted a lot here the last
couple of weeks, but I was thrown into a database project at my work with
very little Access experience. No other employee knows anything about
Access. I've searched Google Groups, and that has been a lot of help, but
there are some questions that I just can't find the answer to. I'll try to
take it easy on the group after this question.
I have one more...
|
by: luis |
last post by:
Hi
I'm using activestate python 2.4 on win xp 2 ed. and Ms Access 2002
(reading first http://starship.python.net/crew/bwilk/access.html)
I have writed the following code
def append_from_Access(self):
try:
import ...
conn = win32com.client.Dispatch(r'ADODB.Connection')
DSN = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA
|
by: weird0 |
last post by:
I have two tables accounts and ATM and i am trying to insert a tuple
in ATM with accountId as foreign key. But even this simple work,I
encounter the following error:
The INSERT statement conflicted with the FOREIGN KEY constraint
"FK_ATM_Accounts". The conflict occurred in database "G:\DOCUMENTS AND
SETTINGS\ADMINISTRATOR\DESKTOP\VERSION2\APP_DATA\BANKINGDB.MDF", table
"dbo.Accounts", column 'acc_Id'.
please sugggest how can i fix...
| | |
by: aknoch |
last post by:
My basic situation is this - I ONLY want duplicates, so the opposite
of DISTINCT:
I have two tables. Ordinarily, Table1ColumnA corresponds in a one to
one ratio with Table2ColumnB through a shared variable. So if I query
TableB using the shared variable, there really should only be on
record returned. In essence, if I run this and return TWO rows, it is
very bad:
select * from TableB where SharedVariable = 1234
|
by: ARC |
last post by:
Hello all,
So I'm knee deep in this import utility program, and am coming up with all
sorts of "gotcha's!".
1st off. On a "Find Duplicates Query", does anyone have a good solution for
renaming the duplicate records? My thinking was to take the results of the
duplicate query, and somehow have it number each line where there is a
duplicate (tried a groups query, but "count" won't work), then do an update
query to change the duplicate to...
|
by: David |
last post by:
Dear All
I'm trying to use a multi-select listbox as criteria to either add new
record or edit the current record of the destiantation table with each
row selected in the box. This works fine for adding a new record but
once records exist in the destination table, the routine seems to
overwrite the first highlighted record in the destination table with
the last highlighted record record, therefore giving two duplicate
records. I've...
|
by: VinArt |
last post by:
MS Acc 2003, XP
Thank you in advance for any help.
I have tables called "Makeup" and "Lines". Each makeup can have multiple lines.
Goal is to create a new "makeup" with identical "lines" except with a new makeup id.
I created a form Makeup Copy with Line Subform. In the main form there is ID (key field), Comments, Customer, Description plus an unbound field "NewMakeup". Each line of the subform has the fields "Makeup (primary...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |
