Remove Dups Based On 2 Or More Fields
Oct 2, 2007
Greetings to all,
I have duplicate records in my table (i.e. there are two or more records that are identical, and I only want to keep one of them). Is there another way to delete the duplicate records other than one at a time? Assigning primary keys and appending only unique records to a new table is not an option because in some instances, I want to maintain duplicate values within a field (i.e. the desire to delete is based on more than one field)
Any help would be greatly appreciated
Cheers, Lori (An Access amateur)
View Replies
ADVERTISEMENT
Mar 21, 2008
In my table, I have a number field set to Index (dups OK). I have another field that I do not want to index but need "no dups." Any ideas?
View 4 Replies
View Related
Dec 27, 2007
I have a table with values :
ID Type
A123
A123
B1234
B1234
B1111
C1555
C1666
I need to produce this result from a Query:
ID Type Count
B12342
B11111
C15551
C16661
I don't want to see A, because is a classic duplicate. All I want to see is where ID is repeated (the same), but types are different and the count.
Is this doable? Even if I have to write this in SQL will be fine.
Thanks,
View 2 Replies
View Related
Jun 22, 2006
I am using the following criteria to test if an order is Current. The problem I'm having is that I am getting duplicates. Ex. If an order is Current it can't be "Repair"
This worked fine until I added one more condition: [supplier_status]='55'
Any suggestions??? Thanks!
Current: IIf([supplier_status]='55' Or (nz([dayslate],0)<=0 And [cancelled]=0 And [delivered]=0 And [repair]=0 And [SNRCurrent]=0 And [SNRpastdue]=0 And [rejectedCurrent]=0 And [AwaitingDcccur]=0),1,0)
View 1 Replies
View Related
Apr 10, 2006
What up folks?
I am getting unwanted duplicates due to a joining error, I think.
The goal is to extract a list of employees and their accosiated departments showing a field called [CTD] from qry_employee but there are employees associated with more than one department.. I have a qry_employee liked to another qry (“qry_employee_ctd”) containing the desired field [CTD] joined by [employee_name] but there are employees associated with more than one department. NOTE: Both tables contain the department.
Please let me know if more detail is needs to assist.
Any ideas? I am up for any suggestions and appreciate your help greatly
Thanks
T
View 1 Replies
View Related
Feb 14, 2008
Hi, I am new to access. My father likes to gamble and has lists of horses he likes to check to see if they are running. He is currently using Microsoft Word and has to check each one by one. I thought if I could get the lists in to Access then it would be a lot easier. I have a field called 'Name' where I am going to put in the horses. The problem is many of the horses in his list have the country next to their name in brackets. I want to remove the brackets and their contents automatically. So if I enter 'horse (GBR)' then I want only 'horse' to be entered. How would I go about doing this. I have brackets in other fields so want this limited to the 'Name' field only. Is this possible?
Thanks :)
View 12 Replies
View Related
May 14, 2015
I have a query that displays this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleC TravelerD TravelerC TravelerE
TripTItleC TravelerE TravelerC TravelerD
TripTitleD TravelerF N/A N/A
I want to filter Column1 to have no duplicates so it looks like this
Column1 Column 2 Column 3 Column 4
TripTitleA TravelerA N/A N/A
TripTItleB TravelerB N/A N/A
TripTitleC TravelerC TravelerD TravelerE
TripTitleD TravelerF N/A N/A
Is this possible?
View 1 Replies
View Related
Jul 31, 2007
Hi everyone,
I am working on a database to manage newsletter subscriptions. Each subscriber record has the option of having up to four email addresses registered to his/her name.
Is there a way to check for duplicate email addresses in the entire database? It would have to compare all values in all four fields of all records.
Any ideas on how to implement such a thing? I'm clueless...
Thanks!
View 2 Replies
View Related
Aug 6, 2014
I have a large application that was built 8 years ago and the user now wants to remove some of the data fields. Is there an efficient way to figure out what objects would be affected if we delete these columns?
View 3 Replies
View Related
Aug 9, 2013
We use Access to pull data from data warehouse and this year they upgraded the new system on the back end and moved old data to the new one. Problem is that in some tables having big fields such as Asset Long Description, Work Long Description and when moved to the new database system, somehow there are certain some XML tags such as <br> </br, etc.. added into the description. So when we pull the data onto the table in Access, is there a way to remove those tags out of the descriptions fields? a macro or module?
View 1 Replies
View Related
Jan 11, 2005
I have a query which contains about 19 fields in it and some of the fields contain parameters. What I'm trying to do is to run the query using the parameters I've set, but at the same time remove fields that are empty (therefore only fields that are populated will be shown). I've tried using the 'is not null' parameter but it seems to interfere with the other parameters that I've set and as a result the query doesn't show any information. Also the field containing the 'is not null' parameter is still being shown. I'm not really sure what else to try!! Any suggestions would be most appreciated!!
View 14 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 28, 2015
I have 4 or 5 tables. Most of the fields are exactly the name but they all have at least 1 to possibly 5 or six fields that are not in the other table. Additionally there are some duplicates within the individual tables as well as across tables.
i.e.
I have a
Student Table - with all the info on the student as well as a column called student that identifies them as such however it does not have the columns parent, donor, appeal, designation.....
Parent Table - with all the info on the student as well as a column called parent that identifies them as such however it does not have the columns student, donor, appeal, designation.....
Donor Table - with all the info on the student as well as a column called donor that identifies them as such however it does not have the columns student, parent, appeal, designation.....
Appeal Table - with all the info on the student as well as a column called appeal that identifies them as such however it does not have the columns student, parent, donor, designation.....
-A person can be within one of these tables more than once but with all the same information.
-A person can also fall into all of these parameters so they could be on every table with the same information in addition to the missing columns,=.
Question 1 : what is the best way to dedupe and delete the individual tables (they all have account numbers)
Question 2: I was thinking create a new table with all the columns available, however how do i dedupe across tables while populating the additional columns from each?
View 9 Replies
View Related
Nov 26, 2007
I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.
I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.
The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".
Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)
DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15
IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static
What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".
I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.
thank you
-Tim
View 2 Replies
View Related
May 17, 2007
I have main form and that has one sub-form , this Main form governs/determines the data in the sub form.
This Sub form ( DataSheet Mode) has approximately 130 columns and based on the Main form criteria only ~ 20 columns has to be filled.
What I want to do is based on the main forms criteria I want to show only the columns that are applicable to main form criteria.
If I use Columnar or Tabular single form for the Sub Form I am able to hide the fields that are not required BUT IT LEAVES A SPACE/GAP on form ( for the hidden fields that are not required)
Private Sub Form_Load()
If Forms!frmShowPIforActiveAndCanAddNewPI!FrmSubFrmFi lterProductInformationPerFMT!CASETIF = True Then
Me.CASETIF.Visible = True
Else
Me.CASETIF.Visible = False
End If
End Sub
And If I use DataSheet and hide ( visible = no) a particular filed it still shows up in Sub Form
Is there a way to Auto-Re Arrange all the fields in the sub form so that the hidden ( visible = no) fields no not leave gap
Or is there a way by VBA program to select fields ( Columns) from a table to be displayed on a sub form based on a criteria
Thanks
Rahul
View 1 Replies
View Related
Jun 28, 2015
I have a query that creates counts of fields based on the data in other fields, basically it tells me that in a table there are two entries with value ABC????? and three of DEF????? , the query works perfectly.
When I create a form to display this data and base the form on the Query I keep getting a message box asking for the ID (key field) from the base table.
If I type * in the box (to denote all values) and press enter I get the results expected.
View 4 Replies
View Related
Nov 18, 2013
Basically in my order details table i have the following fields
Product
Unit
Size
At the moment i have the Product field with a dropdown that gives me all the products from my ProductT. But once i choose the correct product in the unit field it gives me all the possibilities of every product not just the units associated with that product. ie
ProductT
Grasshopper Box1000 Adult
Grasshopper Box1000 Subadult
Worm 10pz Big
When I select the grasshopper product and move on to the unit field i also get "10pz" option but this is not a product available.
How do i set up validation of the fields Unit and size based on another fields data?
View 4 Replies
View Related
Jun 6, 2007
Hi,
I am creating a small database to house results of certain tests. Is there a function in access that allows me to add fields if a result is out of the specification required. In other words I still need to record the out of spec results but I also need more fields to appear for the next lot of results to be entered, for example.
When cooking a batch of product I need to test the pH at the 30 minute mark if the pH is too low I will continue cooking the product for another 30 minutes and then test the product again. The cook time is not a constant so I never know how many samples are required, therefore I don't know how many fields i would need in advanced.
View 3 Replies
View Related
Jan 31, 2007
I'm creating a db that provides project listings in the work queue, along with the due dates and a bunch of other data.
I have created a table [recurring projects] that stores the projects that are done frequently. Once I do some field manipulations on this table, I append them to a master table with all projects [projects] - ad hocs, non-recurring, etc.
Most, not all, recurring projects are due 14 days from the date of assignment, however it varies. I've created a query that populates the due date 14 days or whatever the user has inputed for working days from the assignment date.
All works good with this functionality.
Here's my problem:
There are some projects that are due on the 1st, 15th or some designated date each month. Typically the projects are assigned a due date prior to the month due (i.e. February projects are assigned in January). Since the due dates change each month, is there a way to code a query to look for the first process - 14 days - and if it is null, then populate with a day of the month due.
For example, the field [days_to_complete] = 14 so the query will populate 14 days from 1/31/07 resulting in [due_date] = 2/14/07 OR [days_to_complete] is null, but [day_of_month] = 1 , which I need to create the [due_date] = 2/1/07
I'm racking my brain and pencils are being cracked!
Thanks,
Nathan
View 5 Replies
View Related
Jan 16, 2008
I have been working with a simple Dlookup to return a value based on the data held in a specific field. I am now trying to develop this further and need some help.
I have a query "QryMainForm" that brings together fields from 2 tables "TblFS" (fields: Ref, score 1, score 2) and "TblFamily" (fields: Familyname). the query adds the values in Score 1 and Score 2 and saves the value as "points".
Seperatly from this i have a table "Tbllevels" which contains the fields Score, Operational, Admin, Technical. with the points scored relating to a value within each field. What i need to do is run the lookup based on the both "points" and Familyname fields contained in the query.
So the TblLevels looks like this:
Score.....Technical.....Operational.......Admin
100........................... Level 1.........Level 2
200 ........Level 1..........Level 2.........Level 3
300.........Level 2..........Level 3
400.........Level 3
So someone who scores 300 points could be assigned to the technical or operational group (this is done manually). What i need the lookup to do is to return the level based on the points and Familyname fields located on "qrymainform"
any ideas on how i can do this one?
View 10 Replies
View Related
Nov 30, 2005
Here is what I am trying to do.
I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)
How would I code for the value in the carrier field to populate based on the state and service level values entered.
Should I write a query to get the results? Or should it be code in an event on a particular field?
Whichever way works, any ideas on how to get started?
Thanks.
Gary:confused:
View 3 Replies
View Related
Feb 24, 2006
Hello,
Dopes anybody know how to activate/deactivate fields for data entry on a form based on another field.
I have a check box whcih when checked I would like to activate two other fields and when unchecked to grey them out.
Can I do this?
View 2 Replies
View Related
Jun 2, 2014
using access 2010. I have two tables, an example of their structure is in the attached PDF, and in text below. I want to be able to join/ answer question between the tables like the one outlined below.
Reference Table: A table with the most likely pet based on income and location
Header: Country, State, Income Min, Income Max, Pet
Record: US , NY , 1 , 5 , Cat
Record: US , NY , 5 , 9 , Dog
Question Table: A table with income and location
Header: Country, State, Income
Record: US , NY , 5
What type of pet will the people in the question table have?
Answer: Header: Country, State, Income, Pet
Record: US , NY , 5 , Cat
The logic works like this:
Question Country = Reference Country
Question State = Reference State
Question Income >= Reference Income Min AND Question Income <=Reference Income Max
Obviously I am not working with cats and dogs but this is my table structure.
View 3 Replies
View Related
Jan 24, 2008
Hello All,
I have searched the forum but I don't quite get what I am looking for.
I have a subform that has these 2 fields:
- Status
- LastFollowup
What I want is if the Status shows "Pending" and the date entered on the LastFollowup is more than 7 days. Then it should turn Yellow.
I tried this code in the expression box but it doesn't seem to work
[Status]="Pending" and [LastFollowup]>7
Could anyone please assist? I know this is simple but it's not working for me.
Thank you
View 6 Replies
View Related
Aug 24, 2007
Hi,
I've been using this forum for a couple of weeks but this is my first post, so apologies if I've put it in the wrong place!! I have a table with some drop-down lists to fill certain fields, and I would the available list options to change based on information in other fields. To provide an example:
Field One: The drop down list lets the user choose "Alphabet" or "Number"
Field Two: The drop down list lets the user choose A-Z if "Alphabet" is selected in Field One or 1-100 if "Number" is selected in Field One
Hope I've been clear enough. Any help is greatly appreciated!!
Cheers,
Matt
View 3 Replies
View Related
Aug 21, 2005
Hi all,
I am endeavouring to filter a form based on the position of an individual.
I would like the users to be able to select their position and then any record where there is a match in the "Primary Responsibility" field or the "Secondary Responsibility " field will be displayed.
I have an underlying query that is populated by a combo box on an unbound form.
This has worked when generating reports for individuals but I cannot get the required result when using the form.
Thanking anyone in advance who can assist me with this.
Regards,
Rod
View 2 Replies
View Related
