:confused: I am importing a file from excel into access. The table has about 6300 records. Some records are duplicates or even triplicates. How do I write a querie to leave only 1 of of these records and rmove the duplicates.
Second, I will be importing new files weekly that after I do the above I will need to append to the first table and then remove duplicates. How dod I do this.
thanks
I have a table t1 which contain many records, the field look like:
id f1 f2 f3 ---fn
Id is the primary key
I have some records which have different id but exact the same other fields.
like
id f1 f2 f3 ---fn
87 1 4 6 ---9
12 1 4 6 ---9
18 1 4 6 ---9
116 1 4 6 ---9
1287 1 4 6 ---9
98787 1 4 6 ---9
for those records, I only want to keep one record (any one) and remove all others. How can I do that?
Thanks.
Jeff
Can anyone suggest a way to remove duplicate records.
I've come up with this:
SELECT *, count(*) cnt FROM tableName GROUP BY fieldname1, fieldname2, .... HAVING cnt > 1
But this does not fully resolve my problem.
Thanks.
Hi,
I need to remove all duplicate records in a table. These are records that have matching Contract and Order fields. I can't do a DISTINCT query, because that still leaves one record. Thanks.
Michael
(Been a while since I had a question.) I have a table that stores a list of paper forms. the fields are ID#, display name, doccument name, and a code to determine if its a Word or Excel doccument, or an access report. Works fine; the user can click a form name (on the Forms/Reports tab on the main menu form) in a list box, set the # of copies and print the form. There are cases when a group of forms is needed (client admission is one). I have made a group table containing: GrpID, Grp Name, formID. I would like my "Form Group Edit" form to have 2 list boxes; the left one containing the (short) list of available forms; the right one displaying what forms are already (if any) in the selected form group. My idea is to have add/remove controls to manage the right list. I know this "wheel" is already invented and would like to plagerize one that works. Thanks in advance for your help.
View 2 Replies View RelatedHi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delte the records which are already null .
i tried delete query but it did not work
Please help me
Hi, I have a query which works perfectly and deletes the unnecessary records according to the criteria i have set , but i dunt want this null values in all column to display in the exported output file. how do i delete the records which are already null .
i tried delete query but it did not work . This is in microsoft Access.
Please help me
I have a Datasheet view of a form and it displays a "+" symbol. When expanded it returns a list of related records. How do I get rid of the "+" in my form?
View 8 Replies View RelatedI have a table which has duplicate records so I want to write down the code so that when the user click on a button then it should remove the duplicate records from the table.
View 2 Replies View RelatedI have a table which has duplicate records so I want to write down the query that will remove the duplicate records from the table.
View 1 Replies View RelatedI have this small database, I would like to have your support to setup this query "QryResults" in order to remove the duplicate records, I can't find a way to get shown only true records, for some reason I'm getting duplicate rows and fake values, the query is calculating operations from two different queries and a table.
View 3 Replies View RelatedI have two tables, one is of departments, and one is of people (with a FK denoting what department this person is in). Now consider the fact that there are duplicates in the departments table, and I would like to remove these duplicates. However, the duplicates have related records (in the people table). So, before removing the duplicates, I must update the FKs in the table of people (this is the step I'm having trouble with).
Here's an example:
As you can see, the "Sales" department is there twice. And both have a related record. What I want to do is:
Update all DepartmentIDs (in tblPeople) to not point to duplicate records. In this example, that would be PersonID 2; Joe. His DepartmentID should update to "1" (as both "1" and "2" are "Sales").Delete the duplicates in tblDepartments (in this case, DepartmentID 2, "Sales").
The second step is no problem, it is only the first I am struggling with.
Also, the example posted here is just an example, the data I actually need to do this for is significantly more complex and there are many more records! In the attached database:
qry1: Simple query to find all duplicates (just used the query wizard)
qry2: Just the first row of each duplicated departments (duplicates that shouldn't be deleted). In the example above, this would be the "2", "Sales" row in the tblDepartments table.
qry3: Basically all qry1 rows that don't appear in qry2
qry4: All qry3 values, and their respective qry2 value.
This is what each of the (soon to be deleted) duplicate values' related records' DepartmentID should be updated to... There's no simpler way to phrase that, so using the example above, qry4 would return "2","1". This indicates that all people with a DepartmentID of "2" should be changed to "1" (so we can subsequently erase the department with the ID of 2.
This is as far as I have gotten. My next step is: Update all FKs in tblPeople based on qry4 (You can't set an update query's criteria to pull from another query, nor can you use the second query for the update value... or maybe you can, but I don't know about it).
I have table call "empTable" and it has an Attachment field called "Attachment". Some of the records has empty Attachment field and some record has one attachment and some has two and the attachment types are jpeg and pdf.
I want to remove all the pdf from all the records in this table automatically with VBA or something, is it possible? How to do it?
Hi i have created a plotchart and at the bottom of the chart it is displayed as SUM of SUM of apr 05 etc for all the months is there anyway i can remove the sum of before each month??
thanks :)
Hi,
I just went through the User-level Security Wizard and added security. I now can't log on using a username I created.
How can I get my database back? I have the back up file but how can I get all the stuff back without this security on the database?
PLEASE HELP!!
Cheers
Bizzy
Hi
Need a little help with the following, running windows ME on a Compaq Presario Desktop, connected to Blueyonder Broadband, Blueyonder have provided a new anti virus and firewall package free of charge to broadband users, you need to remove your existing Virus and firewall package(McAfee) to load the new, which I have done, it appears that I still have remnants of a previous Norton Firewall, the only reference to it is when you access through the control panel and the add/remove programme, I have tried many times to remove it but the text is still there.
I have searched for it but nothing comes up as found.
Many thanks
Derek
I have an enormous Post Code Table 1.7 million records, which I must update every month into usable sectors for our carrier cross refs, the UK postcode is in the format CB6 3HQ, CB6 3HR, CB6 3HS, CB6 3HT, CB6 3HW etc I need to be able to remove the space then only use the first 4 chrs ie CB63, in the above examples I know I could do a search and replace " " with "" but I need this to be run automaticly where would be the best place to do this and if coded how?
Any help or alternative suggetions appriciated
Hi All,
I have one serious emergency.
I have 2 options (2 qeustions) in front of me.
[1] How can I remove primary key and not the column
[2] How can I rename the column which is primary key.
Please help me.
Thanks in advance
Jigs
I have an access database and table name called student. The table includes field name such as identification_id and registration_no. The value in identification_id is 3434-34-213 and registration_no are 234/45. Can i query the field name so i can get both value without the sign - and / so the output will be 343434213 for identification_id and 23445 for registration_no?
View 5 Replies View RelatedHi,
i want to replace spaces in my query, but there are a few hard parts in it.
I dont always know how many spaces. en second, the spaces that need to be replaced are after the first character. The rest needs to be removed.
i have:
----1----1----2
the "-" are spaces!!
and i want it to show
1.1.2
this is what i tried replace([MinutesID];" ";".") & " - " & [minutesTitle]
can someone assist...
thanks
Hello, Tried a search but could'nt come up with an answer.
I have a number field with an amount.
The format will always change as far as the LEN is concerned.
I need to remove the decimal.
There will always be 2 numbers to the right of the decimal but the left will always change.
Example:
21543.11 Need 2154311
11.20 Need 1120
Thanks in advance! :)
I have a big table with belgian tax numbers all in this format:
BE 401.031.867
I would like to remove the "BE" and the "." which are in this field so i have only the numbers.
Cant figure it out myself, any help would be greatly apprieciated
I have a query linked to a main frame database. One of the fields is [significance] and gets a number 1-20. Usually when this data is entered, it gets multiple significance numbers. This causes my query to return separate records for each significance number. For example if case number 123 is given significance codes 1, 5 and 12, then my query returns 3 records.
I need a query that will show all records one time that have a significance code other than 12. This would be easy if there were not duplicate entries for the same case number because I could simply say "Not 12". So in the example above, my query returns 2 records showing significance codes 1 and 5. But I don't want to see the record for case number 123 because it also has a 12 significance code.
So what is the best way to handle this?
I appreciate the help.
Jim
If have a table with billing occurances and one of the fields is "business name" how would I need to setup a make table query which lists unique occurances within that data?
Essentially, I want to remove duplicates. If ABC Pet Store has 5 billing occurances and XYZ Pet Store has 1... I want both to only be listed once.
I hope that makes sense. Thanks for any help as to how to set this up!
Hi,
Is there a way that i can remove the space in between the cahracters of the string.
like: 78- ASDGFKGJ OR 78-SJDHF AJSDFH TO RESULT IN 78-ASDGFKGJ / 78-SJDHFAJSDFH
THANKS SO MUCH
Is it possible to remove a filter using VBA.
I need to remove a filter on a form when a user clicks on a drop down box.