Queries :: Hide Duplicates From Query?
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 Replies
ADVERTISEMENT
Jul 31, 2006
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 1 Replies
View Related
May 15, 2006
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 5 Replies
View Related
Apr 4, 2006
Goal - Show how many policies (dbo_Policy22.Policy.Number) were cancelled during a certain time period (Invoice Date).
Problem - a policy can be cancelled more than once and I don't want to count it after the initial cancel status (885).
Question - Below is my query. I'm thinking I type "Distinct" somewhere, but I have no idea where.
Please help :) Thank you.
SELECT dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number
FROM dbo_Producer22 INNER JOIN ((dbo_Insured22 INNER JOIN dbo_Invoice22 ON dbo_Insured22.Insured_Key = dbo_Invoice22.Insured_Key) INNER JOIN dbo_Policy22 ON dbo_Insured22.Insured_Key = dbo_Policy22.Insured_Key) ON dbo_Producer22.Producer_Key = dbo_Policy22.Producer_Key
GROUP BY dbo_Producer22.Number, dbo_Producer22.Name, dbo_Invoice22.Invoice_Date, dbo_Policy22.POLICY_STATUS, dbo_Policy22.Policy_Number
HAVING (((dbo_Invoice22.Invoice_Date) Between [Forms]![PfrmYearToDate]![txtStartDate] And [Forms]![PfrmYearToDate]![txtEndDate]) AND ((dbo_Policy22.POLICY_STATUS)=885));
View 2 Replies
View Related
Jul 31, 2015
A report with multiple lines with name. I have chosen the 'Hide Duplicate' option on the name text box and when the report is printed all is well. However in the display prior to printing when the print option popup is present no name are shown. Some simple setting?
View 7 Replies
View Related
Mar 31, 2006
Hi Guys,
I have a problem. I'm trying to import values to a Combo List from a table:
Row Source = SELECT Tasks.Milestones FROM Tasks;
where Task is a table and Milestones a field of that table. In this field (Milestones) there are repeated values, but I'd like to hide those duplicates when I want to chose them from the Combo List. I don't know If it is clear, if not it's because I'm not an Expert of Access :p
Thanks a lot for your attention,
Ciao
View 4 Replies
View Related
Aug 14, 2013
I have a query that selects different values from different tables.The values of these fields change all the time and I would like the result of my query to exclude - hide the fields that are zero. However, I only want to hide the specific zero fields not the hole record.Non zero fields should still be visible.
View 10 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
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
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
May 18, 2015
I have a question on hiding duplication record using query.
The fields in the query are:
full name(trainee), NRIC(trainee), gender(trainee), preferred language(trainee), company(trainee), course name(course), course date(course), competent(course), class(course), L3 survey(trainee), L4 survey(trainee), num of month(course)
When the query is being run, it will show all the people that have not done the L3 and L4 survey after 3 month. The the query will be convert into a form. However the problem is that the record will show a few same name due to one person can take more than 1 course. therefore, the data in the course table will always be different.
How can i make the record only show 1 name even though they have different course name.
I had tried putting 'yes' for unique record and unique value but it did not change the result.
current SQL query:
SELECT DISTINCT trainee.[Full Name], trainee.NRIC, trainee.Gender, trainee.[Preferred Language], trainee.Company, course.[Course Name], course.[Course Date], course.Competent, course.Class, trainee.[L3 survey], trainee.[L4 survey], DateDiff('m',[Course Date],Date()) AS [Num of Month]
FROM trainee INNER JOIN course ON trainee.NRIC = course.NRIC
WHERE (((course.Competent)="c") AND ((trainee.[L3 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=3)) OR (((trainee.[L4 survey])=False) AND ((DateDiff('m',[Course Date],Date()))>=6));
View 6 Replies
View Related
Dec 11, 2013
Is there a way to hide a field in a union query? I need to keep the field in the SQL statement because I need to order by it. The field is "Rank," but I don't want it showing up.
View 3 Replies
View Related
May 29, 2014
I used the wizard to create a find duplicates query. I know for a fact that 15 records are duplicates. I'm getting over 300 records. When I dumped the data into Excel and did a pivot table to count the number of records by account number only 15 had a total count of 2. All others only had 1. Why are the ones with no duplicates showing? I just want to see the 15.
View 2 Replies
View Related
Sep 18, 2014
I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
View 1 Replies
View Related
Jul 16, 2015
Currently I have a query where the criteria is dependent on the combo boxes on my form. I would like to add checkboxes to my form which determines which fields are shown or hidden. For example if I had a checkbox for address, selecting it on my form will show the address column in my query results.
View 4 Replies
View Related
Jul 5, 2015
I have a button on a form which is used to print shipping labels. This button runs a Query and then a report using the code below.
Private Sub SaveBtn_Click()
DoCmd.SetWarnings False
DoCmd.RunSQL "Update BookInTable SET DateBookedOut = '" & Me!DateTxt & "' WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.RunSQL "Update BookInTable SET BookedOut = True WHERE BarCode ='" & Me![BarTxt] & "'"
DoCmd.OpenQuery "PrintLabelQuery"
DoCmd.OpenReport "Labels", acViewPreview
DoCmd.PrintOut , , , , 1
DoCmd.SetWarnings True
End Sub
But I need both the query results and the report to not be seen. All I want to happen when clicking the button is that a label is printed.
View 5 Replies
View Related
Jul 25, 2013
I have created a query that will create a table automatically. I want one of the fields that I have defined in the query, not to show up on the table. the purpose of that field is just to check a minimum value so that the field next to it can use the value. when I uncheck the "show" box in design view the field completely disappears and Access asks the user to enter a value for that name since the value is used in the field next to it.
View 8 Replies
View Related
Aug 26, 2014
can i calculate 3 fields
ex. total: [rate]*[dys] (working)
ex2. total: [rate]*[dys]-[late] (not working when one of the field(late) doesnt have value)what i want to happen is even if the late field doesnt have value i want it to still calculate
just like in excel =a1*b1-c1 (even if the c1 doesnt have value it still works properly but in access total: [rate]*[dys]-[late] (if the late doesnt have value, nothing shows up in total field, even though rate and dys have.what i did was i assigned the default value of late field properties to 0.but the 0 is just so annoying to see when i have to many data.can i calculate 3 fields in access just like excel even if one of the field doesnt have value? if my only option is to set the default value of late to 0 , is it posibble to hide the default value?
View 3 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
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 4 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 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
