I would like to execute a code to remove the duplications in the MS Access Query but keeping one one data of the duplications.
For example, in the below data, I want to check for the data in the column Part No. The part number 123 and 234 are repeated twice so I want to delete the part no 123 and 234 that repeated second time and keeping the one data.
I have two tables "Master List" and "Audit". I want to delete the records from the "Master" that match those in the "Audit". I am using Access 2010 and have used it often but have built very few queries.
i have a data base with a primary key but some records have been added from a excel data base and now the data base has duplicate records with different primary keys. I do a Query looking for duplicate records based on a field not the the primary key. There are 315 out of 22000. I can sort the table and then manually delete the dups. The Query does not allow deleting records so finding the culprits in the full Data base is time consuming. How can i delete the duplicate records using a query? Is this function available hidden someplace in the guts of Access? Seems like this would be a frequent problem. The Microsoft forum addresses this problem and the only viable solution is the manual method, when they get to the Query method it looks like they just forgot what the subject was and tell you how to delete one record if you know what the criteria is. If two non Key fields are Identical then i want to delete one of the records.
I have a combo box which displays the primary key. It then takes the other values from the cbo and then displays them in other text boxes in the form. This is great.
I am trying to keep the values in record 1and then move on to record 2. reecord 2 has the same values as in record 1 (the values from the cbo!!!) I am trying to keep the values in record 1 and then go to record 2 and select different values in record 2 from the same cbo. Record 1 and record 2 as well as any other record contain the same values from the cbo.
I am stuck - I am not sure how to rectify this problem so that I can choose different values. The cbo is based on a query from other tables.
Please let me know how to do this as I am still finding this tiresome. Many thanks.
I have built a table off a form that is being used for record keeping and my question is.... This table will be constantly updated with new information so how can I make it to where when new info is added the old info will not be removed or written over?
1. Persons (list of persons) 2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
I am trying to make a database that will keep track of invoice backlogs for different locations within a company.
Each day a report file is imported into an Access database (2010), and a query will display all locations with the backlog count of invoices for each of them. Something like:
Code: Location Backlog 00001 84 00002 53 00003 26
Now I would like to be able to store historical data in a new table on a daily basis so that it will be possible to see how the backlogs have fluctuated over time for a given location, but I am not sure how I could do this. I was thinking I could make a table with one row for every date and one column for every location, but then I am not sure how I would populate this table based on the query that shows the results based on the most recently imported report file.
I have a database that was built 5 years ago that has an auto field with an integer. There are relationships attached to this. I an rewriting it to simplify the database and I need to keep the relationships somehow. I want to make the auto field a text fields. How to work this out...
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
A strange request but I hope someone can help with this one
I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number
e.g On a form text box I enter the number or records to be deleted (e.g.6000).
The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
how would i go about deleting a set of records? i can get a list of records together in a query taken from 4 tables and would need to, if necessary, delete a single line. not all information needs to be deleted from all 4 tables though? the info to be deleted would only be deleted from 1 or 2 tables being the last 2 in the relationship.
i guessed it might be an append query but im not too familiar with them.
Hi there, i have a master reset to delete all the data in the database. Although, as there is a username and password entry to get into the admin module, i wish for one entry in the table participantTable to not get deleted (to save one password/username so its possible to log into the admin module after the reset). The code below will delete everything, how can i change it so it keeps the first record in the table, and then deletes all the other records after.
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
I have a field in a table that is comprised of mostly numerical data but some records are text.
I want to convert this field to numerical only and make a new field to put the textual data in.
However converting the field will delete the textual data. What is the easiest way to convert the field but save the textual data AND append the textual data to the SAME record that they were in originally in the new field?
if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..
example
sales order 1001 sales order 1002
if i delete all the records on the table and clean it all out
the next new autonumber will be 1003...
i want it to be 1001 again... and yes its set to an index of no dups
I have 3 tables: 1. customer 2. DVD collection 3. Borrow
The aim of this system is to create a home borrowing system.
The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises.
I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table.
I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help??
Thanks
EDIT: the button i used was from the wizard that comes up when you create a button.
I am using a large database, which usually works fine, and is set to compact on close.
Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before)
Does anyone have any ideas as to what is causing this?
if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted..
example
sales order 1001 sales order 1002
if i delete all the records on the table and clean it all out
the next new autonumber will be 1003...
i want it to be 1001 again... and yes its set to an index of no dups
Hi, I am using Vb6.0 as a front end and msaccess as the db. i want 2 delete all the records in a table "Register" by clicking a menu The connection is made using ADODB Till now i hv the code
Private Sub mnudel_Click() Docmd.SetWarnings False Docmd.RunSql ("DELETE * FROM Register;") Docmd.SetWarnings True End Sub
But when i execute this a error msg displays Run time error "424" Object required
Whats the problem in me plz help me to come out from this...