Queries :: Identify Duplicates With Three Criteria
Apr 5, 2013
I have the following columns in my table 'Catalogue'.
Contract_contractitem
contract
Supplier name
Item number
Short description
Long description
I need to identify duplicates if the short desciption is the same, long desciption is the same but the item number is NOT the same.
e.g.
4700000551_978, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6014, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs
4700000551_977, 4700000551, FISHER SCIENTIFIC BIOTECH LINE ApS, mbp-6010, 0,2 ml PCR Tube, Hotstart, wax bead, NS, 0,2 ml PCR Tube, Hotstart, wax bead, NS 96 tubes/pack, 10 packs/Cs
View Replies
ADVERTISEMENT
Aug 8, 2014
I have a table [PickData] in a WMS (Warehouse Management System) database, that records details of each item picked. The key fields are;
[Movement] - a unique ID for a collection of items to be picked.
[ToAssignRef] - the order ID
[Product] - the product!
[Picked] - the date/time the item was scanned
[Pick Actioned] - the date/time the [ToAssignRef] was completed
[Reason] - A code to indicate why an item could not be picked - AKA F3'd
An operative would be allocated a movement, connected to the [ToAssign Ref], containing a number of products to pick. If an item can not be picked for some reason the operative presses F3 and selects a reason (no stock, damaged etc). These F3'd items (other stock allowing) will later be picked on a different [Movement]. I need a query to identify the subsequent [Movements] and the associated fields following the occurance of an F3'd [ToAssignRef] & [Product].
I have a query, but it runs very slowly (perhaps due to the DB size currently 780K records). Is this the right approach, is there a better (faster) way to do this?
Code:
SELECT PickData.ToAssignRef, PickData.Product, PickData.Picked
FROM PickData
GROUP BY PickData.ToAssignRef, PickData.Product, PickData.Picked
HAVING (((DCount("[Movement]","[PickData]","([ToAssignRef]='" & [ToAssignRef] & "') AND ([Product]='" & [Product] & "')"))<>0));
View 2 Replies
View Related
Aug 27, 2013
want my query to count only records that are not duplicated based on a set of criteria.I have a table with customer IDs and Call dates and the employee ID. I want a count of the call dates for each customer ID for each employee. However I only want to count as one occurrence if multiple calls happened on the same day. For example
Cust_ID Call_Dt
12315 8/1/2013
12315 8/1/2013
12315 8/1/2013
14253 9/1/2013
Customer ID 12315 would only count a 1 call entry instead of 3 for the date of 8/1/2013
View 3 Replies
View Related
Dec 22, 2005
I have two tables, which have same fields. The tables are used for keeping record of news clippings, that are clipped in two different offices. I understand that the best way to manage the database will be combining them into the same table. Yet, it's not an option for the time for some technical reasons. Each table has the same fields as follow:
Input date
issue date
headline in English
news source
category
I want to have a list of news clips from the two tables by a specific category. I will have a form, that allows user to select category as the criteria to query the two tables. I manage to do that in a single table, but how to do it for two tables. This will be very help if you can help. ideally, I can use category to query news clips as well as academic journal clips (from another table) table.
Thanks.
View 2 Replies
View Related
May 10, 2007
Hello,
I was wondering if there is anyway to be able to identify or monitor changes made to a query.
Basically looking to identify if anyone goes in and manipulates the criteria field within the query ?
Using Access 2003.
Any thoughts or help are greatly appreciated.
Thanks
View 2 Replies
View Related
Jul 14, 2015
I have a query that will draw down student details who have completed a course in a given month (May for example), i would like to use this data to identify those learners who are not enrolled on a course in the next Month (June for example). There is no field that denotes whether a student has left only that a course assosciated with their ID has a completion date within that Month. There are approx 250 records.
In my head it should work something like this
1) Identify all learners who finsihed a course in May (Identify learner ID, must have a course end date in that month)
2)Cross reference these against all those who started a course in June and identify the students that have completed in May but did not start a course in June.
Is it possible to store all those who completed (May) in a table/query and cross reference those who started in June and identify of the May completions who did not start in June?
View 3 Replies
View Related
May 22, 2014
Basically I would like to populate column three "OUTCOME" according to the following logic:
if train A10 has multiple destinations (we see this in the second column) then in outcome I woudl like to have "multiple destinations" if the destination is only one (in case of trains A15 and A16) , then I would like to have that city in column 3 "OUTCOME".
View 3 Replies
View Related
Feb 5, 2015
I have a table that holds information for a type of show and the base cost of the show. I am trying to make a query that gathers information for all the shows in the table and uses the Dlookup function to identify the base cost of that show. This will in turn be used in the same query to create a quote, but I can not get the Dlookup function to work and I am sure that I may be doing it very wrong.
The table is called ShowList. That table contains 3 fields, ID, Show Type, and Cost.
The query is called Venue Cost and will have a series of fields that work to develop some math on all items from another table called Venue Info. Venue Info uses ShowList to select the type of show.
What I want the query to do is look up the Show type from VenueInfo and then look up the base cost of the show type from ShowList. What I have been using is not working but is as follows:
DLookUp("Cost","ShowList","[Cost] =" & "[Venue Cost]![Venue Type]")
View 4 Replies
View Related
Feb 27, 2014
I am trying to design a delete query that has an additional criteria needed.
I want to delete identical IDs in one column only if there are identical values in an adjacent column.
So for example, in the table below I want to delete the last row where the duplicate ID is "2700023" because the Code field has identical values, "LRAC". I do not want to delete the second row because the Code field is different for the row.
ID Code
2699974 BCC
2699974 SRAC
2700023 LRAC
2700023 LRAC
View 1 Replies
View Related
Feb 10, 2014
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
View 4 Replies
View Related
Apr 2, 2014
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
View 4 Replies
View Related
Oct 23, 2013
I'm looking for a way to grab values from one table that match a criteria, and append them/update them to another, but only grab one value once. Example:
Part ID ---- Profile Profile ID ---- Locations
Part1 - Profile 1 Profile 1 ---- Location 1
Part2 - Profile 1 Profile 1 ---- Location 2
Part3 - Profile 1 Profile 1 ---- Location 3
I have a database of parts that fit in certain profile sizes. Each profile size has multiple open locations. I need a query to grab a location, assign it to a part, grab a new location, assign it to the next part, etc, without assigning duplicate locations to different parts.
Right now, I'm doing this by using the First/Last aggregate functions, marking them as used, and looping it again and again until all have unique locations. Is there a better way to do this?
View 8 Replies
View Related
Jun 4, 2015
I want to be able to find duplicate numbers in the "claim #" field that exceed 20 within the "date of service" field for a month. I have a date box which allows me to select data from only one month at a time or all. What I'd like to be able to do is group the results by month. Currently it is only matching numbers by day not month.
SELECT HMR.[Claim #], Count(HMR.[Org #]) AS Total, HMR.[Date of Service], Count(HMR.[Date of Service]) AS [CountOfDate of Service]
FROM HMR
GROUP BY HMR.[Claim #], HMR.[Date of Service]
[Code] ......
View 14 Replies
View Related
Nov 4, 2013
I bounced into a big problem with Access 2010 where I cannot seem to be able to remove duplicates from a table containing millions of entries midway through a series of queries.
The table is formed like this:
Date - Serial Number - Reading 1 - Reading 2 - Reading 3
30.7.2013 - 1122334455 - 12345 - 987654 - 654321
30.7.2013 - 1122334466 - 12345 - 987654 - 654321
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334455 - 12210 - 987654 - 643210
29.7.2013 - 1122334466 - 12344 - 986013 - 453213
As you can see, there are some rows which contain exact duplicates and some are a bit different. I wish to remove those fields with exact duplicates and only the duplicated fields. Running "Find duplicates" in Access gives me about 250,000 rows with such data.
I've tried a few options already:
- I cannot use the date as primary key as there are several serial numbers. I cannot use serial numbers as primary key, because there are several dates. Using reading value as primary isn't an option either.
- Microsoft says I should mark all duplicate values with an x and then make a delete query to get rid of all the x-marked rows. For 250,000 duplicates, that's a bit too much manual 'x-ing'.
- If I do a delete query using the Find Duplicates query as a base, it removes all 250,000 entries from that table, instead of just the 125,000 which had a duplicate elsewhere in the table.
- If I make a query which identifies duplicate data and gives me just one row for each duplicate, the delete query still deletes both entries from the original table.
- I could make a new query which would have only unique values using Totals as criteria (for instance, using First for the Date-column). However, this still leaves the duplicate values in the original table. Note that this database is already 800 MB large and new data is imported once a week, for the next decade or so. I cannot have a table get duplicates every week and leave it there.
- If I make a macro which would create the unique values table first and then deletes the old table, what happens next week when I try to import new readings? I would need to make a new macro each time I try to import new data as the table names change. Or is there a way to first run the unique numbers out, then replace the original table with the new one? With a 800MB database, this would put me dangerously close to the 2GB size limit. I wouldn't be able to use this as a part of a macro, as the database would have to do Compact & Repair each time it deletes the original table, midway through a longer series of queries.
- Having duplicates removed from the original import isn't an option either, as it comes in as a overriding excel sheet for the past 3 months, once a week.
As you can see, it's quite a pickle getting the duplicates out of the original table. This is just a small part of a very long macro, which takes about 15 minutes to complete, but due to duplicates the database is getting way too large.
View 9 Replies
View Related
Aug 1, 2013
I have a query showing duplicates, I want to hide the duplicates that have a null value in one of the rows..
Trunc_Name M_UNDERLYIN MaturityDate Duration SumOfSumOfM_VEGA Volatility
SPX P1400 DEC13 INX 21-dec-13 4,766666667 -108 812,00 0,22437
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00 0,21
SP6652 CALL SP6652 04-okt-17 50,86666667 124 376,00
Here's an example of the a duplicate, all 4 first rows are duplicates, but the last value is null for the last duplicate. I want to hide the one the duplicate that has null value!
View 4 Replies
View Related
Aug 30, 2013
I have a table with the following fields:
Customer
Month
Qty
Unit Price
Extended Price
I want to know, by month, what the total extended price is for all customers. But some customers appear more than once in a month. So I also want to know the total extended price for the month, but I only want to include the extended price for each customer once, using only the record with the highest unit price (or extended price, qty for each customer is consistent), and ignoring additional records with the same customer in that month.
I am an excel user, and I could quickly do this with pivot tables... but I have too many records for Excel to handle this efficiently (it just keeps crashing!), so I thought I'd move to access... but I don't know how to use access! So I may need a little hand-holding here.
View 10 Replies
View Related
Oct 19, 2013
I am trying to combine two peculiar tables in Microsoft Access and have been unable to do so even after doing a lot of brainstorming and searching on the internet.
The two tables are spend and export
Spend
+-------------+--------+-------+-------+
| Country | Metal | Month | Spend |
+-------------+--------+-------+-------+
| China | Iron | Jan | 100 |
| China | Iron | Feb | 200 |
| China | Iron | March | 300 |
[code]...
View 2 Replies
View Related
Feb 12, 2008
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 6 Replies
View Related
Jun 11, 2013
I have a table called Stock Levels which contains 3 fields. (ID, ProductID, StockLevel) ID is the Pkey, ProductID contains duplicates and StockLevel which contains different stock levels
and I am trying to remove the duplicates and retain the the data so I am left with the correct stock number
what I have done is the following, but I am still getting duplicate values in productid and stocklevels
SELECT DISTINCTROW id, productid, stocklevel into mynewtable from stocklevels
I have attached a screenshot of the table
View 3 Replies
View Related
Jul 30, 2013
I would like to use an append query that appends data from a union query into a table, but doesn't add duplicates based on a certain field.
I have been trying to use this code, but it doesnt seem to work.
INSERT INTO [Cotton13/14]
SELECT DISTINCT [Contracted Farmers].*
FROM [Contracted Farmers]
WHERE NOT (SELECT * FROM [Cotton13/14] FROM [Cotton13/14] AS [Cotton13/14] WHERE [Cotton13/14].[OF Codec] = [Contracted Farmers].[OF Codec])
Cotton13/14 is the table I want to append to and Contracted Farmers is the union query. However the code is giving me an error.
View 7 Replies
View Related
Nov 19, 2013
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
View 14 Replies
View Related
Sep 9, 2013
I'm trying to create ONE query which would fetch me the number of occurences on a particular value in a field while keeping duplicates.
For instance let's say i've got the following table
Color......Value
Red.........5
Red.........8
Orange....1
Then i would like to have the following result from my query
Color.........Value.......CountOfColor
Red............5..............2
Red............8..............2
Orange.......1..............1
I know i could achieve this by creating a view first. For instance by saving this expression as qryCountOfColors
Code:
SELECT tblColors.Color, Count(tblColors.Color) AS CountOfColor
FROM tblColors
GROUP BY tblColors.Color;
And then make the following query
Code:
SELECT tblColors.Color, tblColors.Value, qryCountOfColors.CountOfColor
FROM tblColors INNER JOIN qryCountOfColors ON tblColors.Color = qryCountOfColors.Color
However I'm woundering wheter or not I could achieve it without creating the qryCountOfColors.I'm myself into a mental meltdown . I've been playing with the idea of creating of creating a subquery to achieve this but im unable to get to work
Code:
SELECT tblColors.Color As BANANA, tblColors.Value, (SELECT Count(tblColors.Color) AS CountOfColor FROM tblColors GROUP BY tblColors.Color HAVING (((tblColors.Color)=BANANA))) AS Expr1
FROM tblColors;
View 2 Replies
View Related
Oct 23, 2014
I have a find duplicates query with the following SQL:
Code:
SELECT tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4, tblData.ID, tblData.Line, tblData.CoCd, tblData.[Document record number], tblData.PurchDoc, tblData.Reference, tblData.Curr, tblData.[Entry dte], tblData.Status, tblData.Version, tblData.Outcome
FROM tblData
WHERE (((tblData.Vendor) In (SELECT [Vendor] FROM [tblData] As Tmp GROUP BY [Vendor],[Loccurramount EUE],[Last4] HAVING Count(*)>1 And [Loccurramount EUE] = [tblData].[Loccurramount EUE] And [Last4] = [tblData].[Last4])))
ORDER BY tblData.Vendor, tblData.[Loccurramount EUE], tblData.Last4;
This works fine however I want to add another clause to the WHERE and I'm not sure how. At the moment the query highlights duplicates where the Vendor, Loccurramount EUE and Last4 match. I want to further restrict it so that it only finds duplicates where the Vendor, Loccurramount EUE and Last4 match BUT the number shown in version Does Not Match
So if two records have the same details for Vendor, Loccurramount EUE and Last4 and also have the same Version number then they don't show in the result.
View 3 Replies
View Related
Feb 27, 2014
I have imported a large number of emails into a table tbl_requests.
I had intended to have unique file tbl_requests.date_opened unique, but have ended up with a lot of duplicate records (i.e. tbl_requests.date_opened is not unique !). How to delete any duplicates? I have 15,000 records...
View 3 Replies
View Related
Aug 4, 2015
I have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.
The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!
What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.
The SQL is:
Code:
SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name]
FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID
WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#))
GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised
ORDER BY dbo_Films.[film name];
View 4 Replies
View Related
Sep 11, 2013
I'm attempting to create an append query that will add new records only if there isn't an equivalent record already existing. Typically I would add the existing table to the query, and only add new records if the same do not exist. In this case, the table is maintaining records over time (start date and end date).
I have 4 tables:
[t_employees]
employee_ID
employee_name
[t_cities]
city_ID
citiy_name
[t_city_assignment]
city_assignment_ID
start_date
end_date
employee_ID
city_ID
[t_temp_employees]
effective_date
employee_ID
city_ID
I'm checking if [t_temp_employees].[effective_date] <> [t_city_assignment].[start_date]. However, if the employee has historical entries it will still add a record (in fact, it'll add multiple records).
How can I append a new record only if one for the same time period does not exist?
View 1 Replies
View Related