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
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
Hi,
I'm running MS Access (2002 SP-2)
I have a table that has the following columns in it:
Sent NBR (text) / Sent Date (date/time) / PIC (text)
It's a customer listing of our customers and which long distance carrier they have selected. Unfortunately my data also shows OLD customers that no longer own that phone number. Because of this, various phone numbers are listed numerous times, but only the Sent NBR with the most current Sent Date is the one I need to see. The others are irrelevant and need to be deleted. I'd manually delete the duplicated Rows but I have over 115,000 duplicates!!
Do you guy know of a way to eliminated the duplicates for each Sent NBR and only show me the Sent NBR's with the Most current date?
Any help would be greatly appreciated!
Scott
I have data that I want to combine. However the separate data sets have some duplicates.
Let's say it is Customer Num field.
What I am saying is I am trying to combine the data sets to make a table with Customer Num and Customer Name.
I want to eliminate the duplicates. What is the best way to do this? Is there a feature in Access for this?
I have a table with few Employee ID re-appearing over and over again for two different job status.
Data Example
EMP ID Job Code Emp Name Country Job Status Time Spent (In Hrs)
222 9250 XYZ ABC Complete 6
222 1059 XYZ ABC InComplete 12
222 5050 XYZ ABC Complete 7
222 1050 XYZ ABC InComplete 9
111 2750 MNM IJK Complete 2
111 2150 MNM IJK InComplete 3
111 6550 MNM IJK Complete 7
111 8250 MNM IJK InComplete 9
I need an output which merges the data based on Emp ID, however, adds the time spent for completed and incompleted job status in 2 rows.
Output Requirement:
EMP ID Emp Name Country Job Status Time Spent (In Hrs)
222 XYZ ABC Complete 13
222 XYZ ABC InComplete 21
111 MNM IJK Complete 9
111 MNM IJK InComplete 12
Which query to use? I use Access 2010.
High everyone I've had a look around the FAQ's and other threads but haven't been able to find what I'm looking for.
I have a table that contains two fields, one being 'part number' and the other being 'quantity'. Unfortunately there are multiple duplicate 'part numbers'. I am looking at keeping only one instance of the duplicate 'part numbers' but at the same time I want to add the 'quantities' together to keep a running total.
For example:
Before
Part Number Quantity
a1 1
a2 2
a3 2
a1 1
a2 1
a1 4
a1 1
After
Part Number Quantity
a1 7
a2 3
a3 2
Any help would be greatly appreciated.
Cheers
I have created a database which is used as a sign in and out machine for students. I have 2 forms a 'sign in' which has a lookup list containing all student names. as well as date and time and a 'sign out form' which uses a lookup list from the sign in form.
It collects data fine but when a student signs in and out a second time there name is duplicated in the 'sign out' drop down list. The student must select the right name out of the duplicates otherwise it wont record.
Im just wondering if there is some sort of fix to this.
i have queries and tables with several duplicated "username" records. What should i do to remove the record with a smaller value of "win/loss" and keep the duplicated "username" with a bigger value of "win/loss".
View 1 Replies View RelatedI have a date field that shows the date like 12-Sep-05 (medium date). I have a report in which the date has to display without the placeholders (e.g., 12Sep05).
I believe what I need to do is create a field in the query to remove the placeholders but I don't know how to write the code. Will anyone be willing to show me? Any help would be appreciated.
Im trying to write a query that shows all the container movements. Yet when I run the query qryFullHistory I get a duplicate value for container Off Island. Ive tried adding some criteria that says that the DateRequested has to be between the ImportDate and ExportDate but that doesnt seem to work. There are duplicate entries for container Off Island in tblContainerDetails as the same container has arrived and left and then returned on another voyage. Yet there is no entry for the second voyage in the tblMEMRContainer.
A brief description of the tables is:
tblMEMR Movement requests details
tblMEMRContainers the containers that were moved on the movement request. There can be more than 1 container for each request.
tblContainerDetails details and dates for the container when it arrived and when it left
There are other tables but these are the 3 that are used in the query.
I am in the process of making a database and basically I have everything done but this saving, loading and adding/removing part.
Would it be possible for me to allow end-users to run queries that they could save, load, add to the existing table or remove from the existing table?
When I say Add/Remove what I mean is could a user of the database run a search of every customer in the state and then remove everyone in County X or on the other hand could they run a search of every customer in the state and then add every customer in a neighboring state.
Kinda like layers and again I have all of the criteria set up I just need the Save, Load and Add/Remove buttons on the form. I should add that I do not want users to save over the main table I want them to save it as there own text file they can upload later.
I have a query that produces a set of rows that contain product IDs and dates (sent). What I want to do is filter out rows that share IDs with another row such that I keep only one row which has the most recent date. As an example my query might contain the following two rows:
DATE ID
01/01/2015 1
02/01/2015 1
What I want to do is filter my table and under the condition that ID #1 appears more than once only keep the row with the most recent date.
I'm unsure what the easiest way to do this would be (or how to do it at all).
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.
I'm creating a job/timesheet database and have a problem...
I have a main timesheets table that includes every job/piece of work thats been done (sometimes multiples for each employee each day). Each record contains a ref to the employee, ref to the contract, ref to the type of work, time taken and date.
There are then various tables that support the main table, which provide actual employee names etc.
I want to produce a query that provides the number of hours undertaken on each day of a particular week for each employee. So I'd have one row for each employee from the employees table, then a field for the sum hours for Monday through Friday.
The way I was going about this was
- to create five query's for the main timesheets table that would limit the entries to the five days in question - Monday-Friday. That worked fine.
- to then create a query that takes the employee name and using each of the five day based queries perform a Sum Total on the time field to give me the total hours worked for each day for each employee. That also worked fine, but the trouble is it removes any null values. So, if I only include the first day there might be 80 employees listed, but when I include the second day as well that goes down to 72 - presumably as 8 employees who entered timesheets for day 1 didn't for day 2. By the time I add all five days, I have almost no employees. I assumed that there would be a query level property to set, but i can't see one.
Also, is there a simpler way to do what I want? If I can get this working I'd like to replicate to breakdown by contract, job type etc in the same way as I have with employees.
Hello all,
I have made a crosstab query that is sporatically making duplicate counts. I'd like to know how to fix the problem, but more importantly - I'd like to know why and how my query is giving me these results so I can avoid making this same mistake again.
The relationship is set up so that all records from one table are returned, with only matching records from the other - so no problems that way as far as I can see. This is my crosstab query that is giving some counts of the same record as being matched sometimes two, three, or even four times:
TRANSFORM
Count([Testing DB].[DB_ID]) AS [CountOfDB_ID]
SELECT
[Testing DB].State, [Location].[North], Count([Testing DB].[DB_ID])
AS [Total Of DB_ID]
FROM [Testing DB] LEFT JOIN [Location] ON [Testing DB].[Address] = [Location].Address
WHERE (([Testing DB].[Window Length]) Is Not Null))
GROUP BY [Testing DB].State, [Location].[Size]
ORDER BY [Testing DB].State, [Location].[Size]
PIVOT Format([Date],"mmm-yyyy");
When I do a simple query for a list of the records being counted by this query, I can see the duplicate entries and can eliminate them simply by adding "Distinct" to the select statement - but I haven't had any luck adding any kind of distinction with this crosstab that will do the same.
How can I eliminate duplicate counts of records in my crosstab? If anyone has a suggestion, it would be much appreciated.
To keep it short and sweet, my query set up is: Employee, Team Name, and then I have the same field in the column as I do in the data and it presents pass/fail data. I have the data shown as a percent of the row so that there is a success rate shown but I want to remove the grand totals from the rows because it is obviously going to be 100% every time and it's unnecessary.
View 13 Replies View RelatedHey, 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
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.
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.
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!
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"));
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! :(
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
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.
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:
I have a Duplicates Query that extract the following information from my Table
Name Surname DOB Joined
The query then extracts info such as...
Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005
Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.
Could someone please assist me with this query if it is possible?
Thank you in advance.