MS ACCESS :: Sorting Further From A Duplicates Query

Find Duplicates And Create Table With Dates Of Duplicates
Hi this is my first post... so hi all :)

ok what i have is a table with contact details 900k plus

there are about 90k of which are duplicates.

this is the basic feilds that are important in this case.

Id, data_source, data_recived, data_code,

what i want is to have a table with unique records (no dups in data_code)

this table will look like this...

Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,

I know there is no more than 4 dups of each record.

what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.

if anyone can help it would be great .

thanks in advance.

View Replies !

Query Help, Duplicates
I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:

SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));

which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.

View Replies !

Please Help With This Query On Duplicates
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.

I think my database is normalised but this may be the problem and can attach it if necessary.

My query is as follows

SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;

Any help would be most appreciated as I cannot understand what is going wrong.

View Replies !

Query Without Duplicates
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.

Thanks!

View Replies !

Duplicates In Query
Hi everyone.

I have a query linking 2 tables. The idea of the query was to show our longest serving customers. The one table contains CustNo, Address details, etc and the second table contains AccNo, CurrBal, AccType, CustNo, etc etc. (note the bold fields are the primary keys). However this is bringing back results on account level rather than customer level, i.e CustNo 3 might Have AccNo xxxxxx, AccNo xxxxx1, AccNo xxxxxx2. How do I get it to show the 'oldest' account for each customer. So it will only show each customer once and that customers oldest account. Heres the SQL for the query. Hope someone can help. Thanks

SELECT DBSINV97.DateOpen, DBSINV97.AccNo, DBSINV97.CustNo, DBSINV97.PostalName, DBSINV97.CurBal, DBSINV97.AccType, DBSINV97.DateClose, DBSCUS01A.Title, DBSCUS01A.Initial, DBSCUS01A.Surname, DBSCUS01A.Street, DBSCUS01A.District, DBSCUS01A.Town, DBSCUS01A.County, DBSCUS01A.Postcode

FROM DBSINV97 INNER JOIN DBSCUS01A ON DBSINV97.CustNo = DBSCUS01A.CustNo

WHERE (((DBSINV97.DateOpen)<"2002-01-01") AND ((DBSINV97.DateClose)="1800-01-01"));

View Replies !

Duplicates In Query
I've been researching and reading about how to eliminate duplicates in my Query, but all the suggestions don't seem to work. Here is my problem:

I want to create a Query with information from 2 Tables. Ultimately, I want my Query to be a malining list that I will be sending requests to. But, I want only the names, addesses, etc. of the people who fit certain criteria (info. stored in several different fields of the two tables). So, if a person matches two of my specified criteria, they appear twice in my query. Or if they fit all the criteria, they might appear 5 or 6 times. I want each name and address to show up once, even though the person may fit into several criteria. Does this makes sense?

I've tried typing "DISTINCT" after "SELECT" in SQL, but that didn't do anything. I've also tried changing the "Unique Values" to YES, but that didn't work either.

Sorry if it's unclear, but I am very new to Access! :(

View Replies !

Duplicates Query
Hello
I've created a query that finds duplicate records in a table. I've also made a form based on that query that shows some fields of the duplicate recoeds found. In that form there's also a textbox that counts the records found (using the count([field]) function). Everything works perfectly when there are duplicate records. However, when there are no duplicates , I'd like the textbox value to be 0 (it shows nothing now obviously because the query returns no records), as long as some other actions to be performed. How can I check (in the vb code or somehow else) if the query returns no records as a result ? Thanks in advance

View Replies !

Duplicates Query
Friends,

I have a table with 3 fields:

NAME
AMOUNT1
AMOUNT2

Field AMOUNT1 and AMOUNT 2 can have the same data and I need to run a query which will return records with similar data in both fields. Both fields are text (5 characters) and the match has to be done on the first four characters.

Please note that I have tried with a Find Duplicates query but does not seem to return all the records with showing the same data probably to the fact the the last character is different.

Any help? Thank you.

View Replies !

Removing Duplicates From A Query
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field

Any suggestions appreciated

View Replies !

Duplicates In Table/Query
I am using a combo box to assign gun numbers on an employee form (frmemployees). I also have a query listing only the gun numbers that have been assigned. When I select a gun from my combo box, I what to evaluate if it is already on the gun assignment query. That way I cannot assign the same gun to two different people. I am using the before_update event to do this. The code that I am using is as follows:

Private Sub intgunNumber_BeforeUpdate(Cancel As Integer)

If DLookup([intgunNumber], "qrygunassignmentcurrent", Me.intgunNumber) = Me.intgunNumber Then
MsgBox "Item # is already listed.", vbOK, "DUPLICATE FOUND"
Cancel = True
End If

"me" represents my frmemployees form. The problem is that it's causing an event even for the numbers that aren't listed on the query. I am trying to get the evaluation correct but am having difficulties. All the fields are numeric fields. :( :mad:

View Replies !

Query Creating Duplicates
I have the following two querys which are supposed to return only the latest Note for a Prospect.

QRYNotes
SELECT TBLNotes.ProspectID AS Expr1, Max(TBLNotes.NoteDate) AS MaxOfNoteDate
FROM TBLNotes
GROUP BY TBLNotes.ProspectID;

QRYNotes2
SELECT DISTINCT TBLProspects.CompanyName, TBLNotes.NoteID, TBLNotes.Note, TBLNotes.NoteDate
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.ProspectID=TBLNotes.ProspectID) INNER JOIN QRYNotes ON TBLNotes.NoteDate=QRYNotes.MaxOfNoteDate;

When I run the QRYNotes2, I get more than one record per Prospect instead of just the newest NoteDate.

What is wrong?

View Replies !

Hide Duplicates Within Query
Hi All.
I have a db, with, amongst others, one form that I enter job details, and another form that I enter the materials for the job. These forms are linked by the ID_Number. This all works fine

I have a query that links both these forms together, so when the query is run, it shows the ID_Number of all jobs that have been done completed between dates, whether they have materials assigned to them or not.

This all works fine, but if for example, there are 10 jobs in one month and one of those jobs have more than one material booked against them, then that job number will show twice.
What I would like to do is "Hide Duplicates" on the query.

Can someone help?

Many thanks.

Frank.

View Replies !

Query To Show Non-duplicates
I have a query that includes the following fields

Year
EventNumber
CorrectiveActionNumber
ResponsibleGroup
Committment

Each event will have multiple corrective actions. For each corrective action assigned to a group, the "committment" is also assigned to them, but I also have to assigned a committment to our group (SRG). So this makes the query results show two records for each corrective action, one with the group assigned committment and one for my groups committment. I need a query that will show corrective actions that have the group committment assigned but not a corresponding SRG committment assigned. However since my query results in two separate records, I am unsure how to do this.

Any suggestions?

Thanks,
Jim

View Replies !

How Do I Run A Query To Find Duplicates??
hi all, im trying to make a query so that when someone updates a field in a form it searches a table (Companytable) which is a list of company names. If it exists i want it to then display a msgbox " already exists" sort of thing! trying to do this in the criteria field on the query. anyone have any ideas or useful resources, ive not done this for a while and cant remember much of IF THEN sort of stuff. mainly not sure if im barking up the right tree, i can search dependent on a set value but not sure how to do it dependant on whats been typed into a form..any pointers would be appreciated! thanks! James

View Replies !

Query To Find Duplicates
Hi,

Our Contacts database has around 4000 individual contacts (and growing), which have been pulled together from four or five seperately maintained databases. As such there are bound to be duplicate records.

What would be a good way of querying the database to find duplicate entries (i.e. entries where the first name and surname are the same)? Short of putting everything in alphabetical order and manually scanning them to see if there are two names the same next to each other I don't know of a way to do it. Also, the manually-scanning method won't work if the names are spelled a little differently (Davies and Davis).

Any ideas?

View Replies !

Update Query - Getting Rid Of Duplicates.
Hey All

I have 2 tables - A and B

I am assigning trade ID from A to B, where they have several common fields.
Two conditions are needed.
a) Only update records where B doesn't have trade ID yet. (done)
b) Do not update records with a Trade ID , if such id is in a a Table B.

not sure how to do the second one; would making Trade ID - primary key help ?

here is the text --

UPDATE tbl_eblotter_feed INNER JOIN tbl_broker_1 ON (tbl_eblotter_feed.nm_brokers = tbl_broker_1.Broker_ID) AND (tbl_eblotter_feed.CUSIP = tbl_broker_1.CUSIP) AND (tbl_eblotter_feed.SPN = tbl_broker_1.spn_cparty) AND (tbl_eblotter_feed.dt_mat = tbl_broker_1.dt_mat) AND (tbl_eblotter_feed.amt_notional = tbl_broker_1.amt_notional) SET tbl_broker_1.M3_ID = [tbl_eblotter_Feed].[M3_Id], tbl_broker_1.Desk = [tbl_eblotter_Feed].[Desk]
WHERE (((tbl_broker_1.M3_ID) Is Null));

View Replies !

Find Duplicates Query
I know I have duplicate records in a table and therefore I used the "Find duplicates Query Wizard" to identify the duplicates.

I am positive - there are duplicates and the query does not perform the way it should be.

Any suggestion will be appreciated.

TKS
JLA

View Replies !

Difficult Duplicates Query
Hi all,

I have 2 tables, namely: Projects and WeekUren

Where [WeekUren] contains multiple references to unique records in [Projects].

[WeekUren] has the following collums which are important:
-ProjID
-Week
-Persoon

Where these three always form an unique combination. For instance:
1 | 1 | 1
1 | 2 | 1
1 | 3 | 1
1 | 1 | 2
1 | 2 | 2
2 | 1 | 2

Saying: Person1 will work week 1 till 3 on Project1, Person2 will work week 1 and 2 on Project1 and week1 on Project2.

Weeks are here relative to the project start date (a value of [Projects]) and always start with and increment with 1. So week1 of Project 1 and 2 don't neccesarily fall on the same dates, hence Person2 is still able to work both Projects.

Bear with me here.....

Now I need to select all duplicate records (where people are assigned to two (or more) projects in the same real week.

Real week is defined by:
Code:DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId)))

And the Query to convert [WeekUren] in to the relative form is:
Code:SELECT WeekUren.Id, DatePart("ww",((WeekUren.Week-1)*7+(SELECT Projects.Start FROM Projects WHERE Projects.Id=WeekUren.ProjId))), WeekUren.Uren, WeekUren.Persoon, WeekUren.ProjIdFROM WeekUren;

The query to print out duplicates is ofcourse:
Code:SELECT *FROM WeekUrenWHERE (((WeekUren.Week) In (SELECT [Week] FROM [WeekUren] As Tmp GROUP BY [Week],[Persoon] HAVING Count(*)>1 And [Persoon] = [WeekUren].[Persoon])))ORDER BY WeekUren.Week, WeekUren.Persoon;

Sofar so good, combination of the two however give me a syntax error. Hence my question here how to construct such a diabolical Query. Thanks!

Regards,

Jacco

View Replies !

Hide Duplicates In Query
I have 3 field name's Survey, Date, and Rep Name .I want to hide any duplicates from the survey field but i want them to show the most current from the Date Field. How would i go about doing this? I've tried everything i know but no luck...Any input is greatly appreciated.

View Replies !

Need Help - Delete Duplicates Query
I am a rookie! Have a table with names addresses and years. Want to wittle it down to one name and address per. Ran totals query. Did delete duplicates. When I hit view datasheet, it shows the right info to be deleted, but when I try to run the query, it comes back with "Could not delete from specified tables." When I hit the help button, it says Error 3086 and tells me about read only. I have checked permissions, and I have full permissions. Does anyone have any idea what I am doing wrong?

View Replies !

How To Avoid Duplicates In Append Query
Hi all,
I have an append query that I run daily and want to append only records with
different OrderID.
------------------------------------------------------------------------
INSERT INTO tblConfirmSchedule ( OrderID, JobCode, JobDate, Status, [Home#],
[Work #], Ext, [Other#], Ext2 )
SELECT Orders.OrderID, [Schedule Info].JobCode, JobSchedule.JobDate,
JobSchedule.Status, CustNew.[Home#], CustNew.[Work #], CustNew.Ext,
CustNew.[Other#], CustNew.Ext2
FROM (CustNew INNER JOIN Orders ON CustNew.CustomerID=Orders.CustomerID)
INNER JOIN ([Schedule Info] INNER JOIN JobSchedule ON [Schedule
Info].JobCode=JobSchedule.Scheduled) ON Orders.OrderID=[Schedule Info].OrderID
WHERE (((JobSchedule.JobDate)>Now()+3) And
((JobSchedule.Status)="Scheduled"));
--------------------------------------------------------------------------

Any ideas?
Thanks in advance,
Emilio

View Replies !

Deleting Duplicates Query.. With A Twist
Hi,

Been reading the forums for a while now. Thanks to all you knowledgable people out there! Been a huge help in learning this program.

Here's a question that stumped me...

I have a table that is about 90,600 entries long with 4 fields per record.

Now here's the issue:

Approximately 25,000 of these entries are duplicates - based upon TWO of the fields.

SO we have:

Field1 Field2 Field3 EntryDateField

if Field1 and Field2 Match, I want to delete the record that has the OLDER entry date.

any ideas how I might accomplish this?

Thanks!!!

J

View Replies !

Remove Duplicates From Query/form
Hi,

I have the following set up on a form to pull together a query (by form) and resulting report:

Publication
Sector
Product
Region

Each publication can have multiple sectors/products/regions. The resulting query therefore duplicates the publication, for example:

Publication 1 Sector 1 Product 1 Region 1
Publication 1 sector 2 product 1 region 1
publication 1 sector 2 product 2 region 1

etc etc...

The report ONLY has publication on it, but as it is based upon the query utilising the 4 factors above, the resulting report is:
Pub 1
Pub 1
Pub 1
Pub 2
etc

I want the report to only list the publications once - i have tried "hide duplicates" from the form field properties, however this hides the text but leaves a big gap on the resulting report when generated where the duplicate would be if it were not hidden.

There must be a simpler way to acheive this than getting another query to create a table based on the first query which (the table) only includes the publication name, and is filtered to remove duplicates through a primary key...

Can anyone help!?

cheers
amx

View Replies !

Query Giving Too Many Results (duplicates)
I have a query that selects from a table base on 3 entries (Name, BeginDate and EndDate) and should show me 12 other columns and their entries... I have 9 entries for a particular Name, but when i run the query i get 15 results. Some are duplicated but others are not and I don't know where to begin narrrowing it down. Any ideas where to start?

View Replies !

Query Help - Filtering Duplicates Within A Group
hi there,

Im trying to develop a query which will show me players with the same 'player number' in a team.

I have two tables; players and teams.

Each player in a team should have a unique player number, so I want it to show records if eg Jim and Bob are both in Team X and they are both number 6.

Any tips would be great -

View Replies !

Need A Query That Shows Only Info With No Duplicates.
Okay I have a database that stores information on some payments.

Payments can be made on different dates and multiple times during the month.

There is an account number which will have duplicates in the table if there is more than one payment. This is fine.

However, I need a query which will show only those records for account numbers which appear only once.

So if it has duplicates, it is ignored, otherwise it's reported.

TIA for the help.

View Replies !

Eliminating Duplicates From A Query Pull
Hello,

I am building a query that pulls from one databases but I am running into duplicates.

Here is the skinny:

From the [tbl_databases], I am setting criteria that restricts a column containing “Visio”, more specifically, “Not Like “*Visio*”. I am also setting criteria that restricts a column containing “OLD” (it is a Yes/No field).

.

The problem is that there are duplicate rows that contain both “Visio” and “Old. So, simply suing my criteria restriction are pulling out the Visio and Visio/Old orders as well as Old and Old/Visio order.

Can I create a subquery that contain the combination of “Visio” and “Old” to restrict the subset from being extracted twice?

I think I have the logic but not sure not to set it up.

Thanks

Tuktuk

View Replies !

Excluding Duplicates In One Field In A Query
How can I run a query on the table attached to show the number of UR's that were operated on. As you can see some of the UR have more than two procedure dates so I want to exclude duplicates. i have tried "count" but that just gives a "1" value for each UR on each procedure date

View Replies !

Find Duplicates Different Than Duplicates Deleted
Hey, all! Thanks for helping, here is my situation.

I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.

I'm using Access 2000 on XP Pro.

If anyone could help with this I would greatly appreciate it!

Thanks

Will

View Replies !

Help With Sorting A Query?
I have a job database where I sort jobs by upcoming, inactive, closed and active. Each has a corresponding number 1-4 respectively. The main table I update the jobs in is the JOB TABLE. I print a weekly report for our weekly meeting but I only want the active jobs in the report. My question is: how do I perform a sort function in the query, so I will have only the active jobs in the database table show in the query? Thanks in advance! If more info is needed I wil post additonal.

View Replies !

Sorting In A Query
I'd like to sort in my query - used to populate a combo box - first by name, and then by ID.
Is there a way to force Access to sort in a particular order? It seems like it starts sorting with the first field that's to be sorted, but I want it to start with field 2 and then sort by field 1.

View Replies !

Sorting A Query
I am fairly new at relying heavily on Access for my programming. Problem: I have a telephone database with call detail for every call. I only want specific records, which I am pulling by selecting sort (in query design mode) for that particular field. I need to sort like you would in Excel (Column A first, Column B second, and etc). Can someone help direct me in the right path as to how I need to run a mulitple sort? Your help is much appreciated.

View Replies !

Sorting A Table, Apply The Sorting To A Form
Hi all.
I've created a database which contains information about stores. I want to have the forms automaticly sorted by the department number.
I've tried to sort the table by department, but when I try to add a department, the sorting doesn't seem to affect the form at all.

Lets say I have department 1,2,3,6,7,8 in the form, and I add department 4, it will be the last post in the form. I want it to be the fourth, and so on..

I'd apreciate some help with this :) Thanks

Here's the database (http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=12934&stc=1&d=1142018915&PHPSESSID=f730b7f11f6983965698faeacbe5a1ee)

View Replies !

Access 97 - Query Help - Newbie - Removing Duplicates
Hi,

just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.

What I want to display is only one record per entry containing an exact match for criteria X.

Finding it hard to articulate this but I would need something that defines:

If criteria X is the same, just display one record.

Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.

Thanks

View Replies !

Removing Duplicates From A Query On A Query?
Hi All

I have a query which counts sales id numbers against postcodes. As the Id number are unique and can be counted so I know how many sales are in a postcode.
I have then built a second query based upon the results of the first this data contain

[Postcode] [Sales ID] [Customer number] [lots of other details]

What I would like to do is remove those records from the list where the same customer number appears more than once at a postcode. If anyone could help would be great thanks

C

View Replies !