NEWBIE HERE...Two Tables.. Want To Delete Duplicates Between Them
Apr 3, 2006
I think this is an easy one...
I have two tables - one is a Master. I want to delete the records in the Master if they are in the second table. Here's my SQL and I can't get it to work:
DELETE from TestMaster
INNER JOIN on JoinedDupList
ON TestMaster.ID=JoinedDupList.ID
WHERE JoinedDupList.ID = TestMaster.ID
I'm being told I need to define the table to delete from... I thought I defined it.
I have a Customer table with a number of fields including ID (unique primary key), Name, Address and LabelCount (integer).
What I want to do is produce a mailing list report/query which will print out the number of address labels (taken from the field LabelCount) for each record in Customer.
So, for example
1, John Smith, 2 Long Drive, 5 2, James Jones, 4 Some St, 2 3, Billy Bob, 8 High St, 0
The report will print out 5 address labels for John Smith, 2 for James Jones and none for Billy Bob.
just finding my way in Access 97 with no real support. I have made a query that creates duplicate records when I only need to see one for a certain criteria (lets say criteria X). This is due to the data feed involved in making the query where criteria X is found in numerous records.
What I want to display is only one record per entry containing an exact match for criteria X.
Finding it hard to articulate this but I would need something that defines:
If criteria X is the same, just display one record.
Hope this is somehow clear as I am struggling a bit. Would be gratefull of some help.
I have hundreds of duplicates in my database and i've tried using logic to delete them but there are still thousands left over. I know duplicates can be removed by using VBA, how to use VBA IDE.
I wanted to delete some duplicate records off of a table.
I went through the steps of copying the structure of a table to a new table then setting the Primary Key as what I wanted to be unduplicated. In my case, I have the Primary Key setup as the Account SSN and the Persons ID. This way it would be OK to have more than 1 SSN as long as I dont have more than 1 SSN and Person ID together.
I have 430 records on my table, when I run the find duplicate query, I see 22 lines (11 duplicates). I went through the table and confirmed that this is correct.
When I paste the original table to the PK table, it copies 392 records. Basically, it didn't copy 27 records it should have. I hunted down the 27 and they were not duplicates, they only had 1 SSN + Person ID.
So to make it short, the easy way to kill duplicates for some reason doesnt work for me.
Is there another way to do this? I was thinking of using the Find Duplicates Query and somehow turning it into a Delete Query, but I would only want it to Delete 1 of each it finds, not all of them.
I am a rookie! Have a table with names addresses and years. Want to wittle it down to one name and address per. Ran totals query. Did delete duplicates. When I hit view datasheet, it shows the right info to be deleted, but when I try to run the query, it comes back with "Could not delete from specified tables." When I hit the help button, it says Error 3086 and tells me about read only. I have checked permissions, and I have full permissions. Does anyone have any idea what I am doing wrong?
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.
If there a way i can remove duplicates from an email list? I pull these emails from a recordset. But i email may appear more than once and it doesn't look good e.g
I'd like to remove the duplicated email, if its possible. Code below.
Code: Set rs = CurrentDb.OpenRecordset("select * from query") With objMailItem If rs.RecordCount > 0 Then rs.MoveFirst Do If Not IsNull(rs![email]) Then vRecipientList = vRecipientList & rs![email] & ";" rs.MoveNext Else rs.MoveNext End If Loop Until rs.EOF .To = vRecipientList
I have a table that has multiple records (count >1). I used the find duplicate records and then made it a delete query, however, this resulted in deleting all the records that had count >1. I need to retain distinct record, and delete the extra records. Using select distinct.. I do not want to use VBA to achieve this, but at the same time be able to incorporate the steps in a module that would execute the queries in a sequential order and achieve the same results.
I'm having a problem with how my forms add data to my tables. I've been using the wizard to create 3 different tables and a form to update them. One of two things happens to me each time I finish the form, either it won't allow me to input any data or instead of updating a field each time I put something new it will write over the existing data for that field. I've tried using sub-forms too with the same problems. Is there something I can do to fix this or is there a past post that I can look at that may help me out.
Sorry if this isn't too clear like I said I'm new to Access and this is really only my second time using it.
I have constructed a DB for work to record accidents, however I am running into problems. On each form I have a button that Saves the data closes the current form and opens the next form, depending on what data was entered. I have this ruuning fine using this code,
Private Sub SaveRecord_Click()
Dim NextForm As String
If Me.Status = "Pupil / Child" Then NextForm = "StudentDetail" Else NextForm = "EmployerDetails" End If
DoCmd.save DoCmd.Close DoCmd.OpenForm NextForm End Sub
Private Sub SaveRecord_DblClick(Cancel As Integer)
Dim NextForm As String
If Me.Status = "Pupil / Child" Then NextForm = "StudentDetail" Else NextForm = "EmployerDetails" End If
DoCmd.save DoCmd.Close DoCmd.OpenForm NextForm
End Sub
The primary key of this form is "PersonKey" and the two forms taht could open, each have"PErsonKey" as a foriegn key. However the key does not get brought across into the next form and the save function does not appear to work on all forms... :confused:
I put another button on this form using the control button wizard, and it gave me this code,
Private Sub Command23_Click() On Error GoTo Err_Command23_Click
I'm building a simple db to track people with access to systems and buildings (Access 2000). I have three tables:
1. People (names, ID, etc.) with a primary key of an autonumber 2. Bldgs - y/n fields of bldg #'s with a primary key of a number (linked one to many from the People table) 3. Systems - essentially the same set up as Bldgs table (though they're not related)
People may have access to either bldgs, systems, both or none.
So I build a query to combine all three tables. If I add a new record (to the People table and only one of the other two tables) I can't go back later and add data to the third linked table seemingly because the autonumber exists in the other two tables but not that one. Well, sure.
So the question is, I'm obviously missing something insanely obvious, or if not, how do I get around this? It's driving me nuts.
I have created a database which holds architectural drawings for my company. The drawings have a number (dwgNumber), a name (dwgTitle) and a revision (dwgRevision). Each number should only have one title but cannot be unique as they can have several revisions. I am looking for a way to allow the users of the database to only enter a drawing title for a drawing number once. This is because if more than one person is accessing the front end at a time, they may use the same drawing number for different drawings.
For Example:
----THIS IS FINE Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 1 Revision: P2
----THIS IS NOT OK Number: A001 Title: Drawing 1 Revision: P1 Number: A001 Title: Drawing 2 Revision: C1
Basically, each number can only have one title assigned to it.
I have a report to run that gathers info from three tables. If I make individual queries to get the information I need the three queries gather the correct information. If I make one query it makes duplicates. If I combine the three queries that work the main query mixes up the information and duplicates it. How do you make a query that gets information from three tables and not have it be garbled?
Hello hello. I'm new here, and new to Access... I've just started taking Microsoft's Office Online training tutorials and I was wonder about planning tables and repetitive data.
I had transferred some stuff from Excel to Access but now I'm looking at redesigning my whole database using relationships, etc.
I'm mostly working with a mailing list, so my fields are:
Mr./Ms. FirstName LastName Title Company Address City State Zip
Okay. There is also a second list of contacts that will link to the Company field. But my question is, most of the contacts in this list have the same title (be it President or CEO or whatever...) -- there's only like four variations.
Would I make a separate table with just those four titles, so I don't have to repeatedly type "President" in the title field?
And then if so, what about things like Mr./Ms.? It's only two variations and every record needs one. And what about State? Again, only two states ever. These don't get their own little "related" table, do they? And if not, how do I avoid typing it every time??
Thank you so much for reading this, and thanks in advance for your help.
~Jas/LadyGrey
P.S. I have another major question too, actually. As I said, I'll have two different lists hinging on the Company name. Except I have some companies with multiple contacts... so I don't think I can make the Company the primary key, since it may be repeated for a few records. Help, please! Thanks!
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 |
Ok.. I have been racking my brain for hours trying to figure out how to do this. Maybe someone here can help me figure this out.
I have 2 tables. "AR94" & "RPIN" I need a query to look at the "Provider_PIN" field of both tables in order to find duplicates between that field in both separate tables at which point I need it to say If a duplicate provider_pin exists in the AR94 table & the RPIN table Then remove that provider_pin from the "RPIN" table.
The final result should be that the only records showing in the "RPIN" table would be those provider_pins' that were NOT found in both tables.
I am just starting Access... Or at least trying to learn it on a small project, and I need a jumpstart, if someone woild be so kind and help me out.
I need to organize my e-mail sendings.
In one table (Table Sent) I have e-mail addresses where I have already sent messages. In an other (Table New) I have some other e-mail addresses, I am preparing to send out.
To make sure to not send mail to those who already got one, I need to compare the two tables, and delete from the Table New those addresses which are already present in Table Sent.
I guess that would be a 2-3 line SQL script... All of my attempts so far produced no results
I am trying to modify an inventory management database. I want to prevent duplicate entries to specific fields in centralized table. The table is called Work Stations. Its function will be to track various computer equipment by a specific work station name. Each of the items will have a unique asset tag.
The primary Key for Work Stations is WS-ID. The fields I am trying to prevent duplicate entries in are WS-Computer, WS-Docking Station, WS-Monitor_1, WS-Monitor_2, and WS-Switch. Each of those fields are primary keys in 4 different tables that conation more detailed information about item. The exception is WS-monitor_1 and WS-Monitor_2 have a one-to-many relationship with Mon-Asset Tag in the monitors table.
The goal I am trying to accomplish is when a work station ID is created or modified duplicate entries are prevented to those fields listed above. The computer, monitors, docking station, and switch fields in the work station table may contain data or may be null.
I have tried to set the Indexed Option to Indexed (No duplicates) for each of the fields and I have also tried setting Yes to Ignore Nulls in the index option on the table design tab for the individual fields. Both options have returned the same error stating the changes would create duplicate values, in the index, primary key, or relationship.I have not created form for this table yet as I was trying to get no duplicates option to work first. I have verified the data and the only duplicating fields are the Null fields
Attached is my many to many relationship setup. I would like to prevent the possibility of entering the same person more than once for the same training event. I am assuming that I do this by setting an index setting for a field the junction table?
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?
Employees submit information into a form which translates the information into a table. The table has been in use for years. By accident some employees were writing to an archived table while others were writing the the active table. This resulted in a field called "WorkID" being duplicated across the two tables.
Bottom line I am trying to write a query finding duplicates across tables but basing the duplicate only on certain columns.
I'm a newb to Access and SQL and database management. I have a main table and a dependent table with a 1 to 1 relationship, relationship integrity and cascade update and delete. I used an append query after I had modified several records, and after the append deleted one of the records from the main table. It remained in the dependent table. Access didn't catch the disparity even after a restart. I broke the link and tried to reestablish it, and of course Access wouldn't let me. I deleted the record manually from the dependent table and all was well.
Why would referential integrity/cascade delete not be recognized after an append query?Access 2010.
This is the second time in two days that cascade delete has been broken in two days after using a query on the main table. Yesterday, after referential integrity was broken, deleting the records from the dependent table allowed me to restore Cascade Delete functionality and it worked for the rest of the day.
I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.
It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.
However I want to avoid this error of possible duplication of entry.
Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.
I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.
