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?
View RepliesEmployees 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 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 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.
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 |
[code]...
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.
Starting Example (Before query):
AR94 Table:
-----------
1234
12345
123456
1234567
12345678
RPIN Table:
-----------
1234
4567
45678
12345678
456789
Results that should be achieved (after running query):
RPIN Table:
4567
45678
456789
Thereby deleting the 1234 & 12345678 values from the RPIN table leaving only records that were NOT found in both tables.
Please help me as I am trying to get this done for a project and can't figure it out..:confused: Thanks.
Hello,
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
Any guidance would be great!
Thanks
Attila
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?
View 3 Replies View RelatedI 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?
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.
Quick run of data that would be in this table:
Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014
in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.
I have a report with 2 access tables (1 Master table and another a daily feed table)
The Master table keeps a log of all incoming records. (once append it to this table, should not show in future reporting)
The Daily feed information within the last 48 hours. (uploaded from an excel report into access temporary table)
When the daily feed table gets completed, I append the records and updated them into the Master to avoid duplication.
When I upload the daily feed table and I match it against the Master table to find duplicates, how can I delete the duplicates from the Daily Feed table?
This is my code to find duplicates:
SELECT CMPreport.ID, CMPreport.MbrName, tblMaster.ID
FROM CMPreport LEFT JOIN tblMaster ON CMPreport.ID = tblMaster.ID
WHERE (((tblMaster.ID) Is Not Null));
We are a non-profit that does blind mailings for our membership drive. The company who we buy names and addresses from sends us a delimited file that has these fields as the headings
" ID, FULLNAME,COMPANY, ADR1, ADR2, CITY, STATE, ZIP, FIPS"
Once they send out the mailings, people then send in back a remit slip with a contribution that gets scanned through a program that creates a file that gives us these titles
"ALT ID, AMOUNT PAID, RUN DATE, TENDER, FUND, PURPOSE, SOLICITATION, MEMBERSHIP QUESTION, MEMBER TYPE, CONSTITUENT TYPE, SEGMENT"
The "ALT ID" and "ID" are the same in both tables.
I need to find a way to merge the tables and combine the fields that have the same ID # , and then have it create a csv file that reads like this (see below) for only the files of the people that responded so that I can import it into our membership software.
"Alt ID","Title","First Name","Middle Name","Last Name","Suffix","Address1","City","State","ZIP","ct y_code","Amount Paid","Run Date","Tender","Fund","Purpose","Solicitation","Me mbership Question","Member Type","Constituent Type","Segment"
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
I have a table in a healthcare patient database that stores Admitting Number, Scan Number, Last Name, and some other fields detailing the scan. Each patient can have multiple scans and I'm trying to design a query that lists how many scans each patient has had by finding all the duplicate admitting numbers. I'm using this SQL statement:
SELECT MultScans.Admitting_Number, Count(MultScans.Admitting_Number) AS Num_Of_Scans
FROM MultScans
GROUP BY MultScans.Admitting_Number
HAVING (((Count(MultScans.Admitting_Number))>1));
which works fine but I can't figure out how to also print out the last name, scan number and the other fields in this query. If I add the field to the select part it says I can't because the expression is not part of the aggregate function and if I add the expression to the 'group by' the query runs but displays all the duplicates. Thanks for any help.
Please help. I am having difficulty understanding why a subform is showing duplicates despite using SELECT DISTINCT for its query. I have looked all over the forum but am still quite confused.
I think my database is normalised but this may be the problem and can attach it if necessary.
My query is as follows
SELECT DISTINCT StudentAndCourse.ClassID, StudentAndCourse.LevelID, StudentAndCourse.StudentID
FROM [Level] INNER JOIN (Class INNER JOIN StudentAndCourse ON Class.ClassID=StudentAndCourse.ClassID) ON Level.LevelID=StudentAndCourse.LevelID;
Any help would be most appreciated as I cannot understand what is going wrong.
Hello,
I am just wondering whether there is any way I could run a simple query that would pull all the data I need and inform me of the duplicates if there is any.
Thanks!
Hi everyone.
I have a query linking 2 tables. The idea of the query was to show our longest serving customers. The one table contains CustNo, Address details, etc and the second table contains AccNo, CurrBal, AccType, CustNo, etc etc. (note the bold fields are the primary keys). However this is bringing back results on account level rather than customer level, i.e CustNo 3 might Have AccNo xxxxxx, AccNo xxxxx1, AccNo xxxxxx2. How do I get it to show the 'oldest' account for each customer. So it will only show each customer once and that customers oldest account. Heres the SQL for the query. Hope someone can help. Thanks
SELECT DBSINV97.DateOpen, DBSINV97.AccNo, DBSINV97.CustNo, DBSINV97.PostalName, DBSINV97.CurBal, DBSINV97.AccType, DBSINV97.DateClose, DBSCUS01A.Title, DBSCUS01A.Initial, DBSCUS01A.Surname, DBSCUS01A.Street, DBSCUS01A.District, DBSCUS01A.Town, DBSCUS01A.County, DBSCUS01A.Postcode
FROM DBSINV97 INNER JOIN DBSCUS01A ON DBSINV97.CustNo = DBSCUS01A.CustNo
WHERE (((DBSINV97.DateOpen)<"2002-01-01") AND ((DBSINV97.DateClose)="1800-01-01"));
I've been researching and reading about how to eliminate duplicates in my Query, but all the suggestions don't seem to work. Here is my problem:
I want to create a Query with information from 2 Tables. Ultimately, I want my Query to be a malining list that I will be sending requests to. But, I want only the names, addesses, etc. of the people who fit certain criteria (info. stored in several different fields of the two tables). So, if a person matches two of my specified criteria, they appear twice in my query. Or if they fit all the criteria, they might appear 5 or 6 times. I want each name and address to show up once, even though the person may fit into several criteria. Does this makes sense?
I've tried typing "DISTINCT" after "SELECT" in SQL, but that didn't do anything. I've also tried changing the "Unique Values" to YES, but that didn't work either.
Sorry if it's unclear, but I am very new to Access! :(
Hello
I've created a query that finds duplicate records in a table. I've also made a form based on that query that shows some fields of the duplicate recoeds found. In that form there's also a textbox that counts the records found (using the count([field]) function). Everything works perfectly when there are duplicate records. However, when there are no duplicates , I'd like the textbox value to be 0 (it shows nothing now obviously because the query returns no records), as long as some other actions to be performed. How can I check (in the vb code or somehow else) if the query returns no records as a result ? Thanks in advance
Friends,
I have a table with 3 fields:
NAME
AMOUNT1
AMOUNT2
Field AMOUNT1 and AMOUNT 2 can have the same data and I need to run a query which will return records with similar data in both fields. Both fields are text (5 characters) and the match has to be done on the first four characters.
Please note that I have tried with a Find Duplicates query but does not seem to return all the records with showing the same data probably to the fact the the last character is different.
Any help? Thank you.
I am running a query to return records from a table. I want to eliminate records from the query if the values contained in one field are duplicated. I have tried using the DISTINCT keyword but it only works if the entire record is unique or if I was retieving just one field
Any suggestions appreciated
I am using a combo box to assign gun numbers on an employee form (frmemployees). I also have a query listing only the gun numbers that have been assigned. When I select a gun from my combo box, I what to evaluate if it is already on the gun assignment query. That way I cannot assign the same gun to two different people. I am using the before_update event to do this. The code that I am using is as follows:
Private Sub intgunNumber_BeforeUpdate(Cancel As Integer)
If DLookup([intgunNumber], "qrygunassignmentcurrent", Me.intgunNumber) = Me.intgunNumber Then
MsgBox "Item # is already listed.", vbOK, "DUPLICATE FOUND"
Cancel = True
End If
"me" represents my frmemployees form. The problem is that it's causing an event even for the numbers that aren't listed on the query. I am trying to get the evaluation correct but am having difficulties. All the fields are numeric fields. :( :mad:
I have a Duplicates Query that extract the following information from my Table
Name Surname DOB Joined
The query then extracts info such as...
Bill Smith 25:12:70 1999
Bill Smith 25:12:70 2005
Most of the time the 'Joined' field has the same year. But I need to find the ones where the year differs but the rest of the information is exactly the same. Because there is 15000 odd records to check, I thought that there would be a quicker way to check.
Could someone please assist me with this query if it is possible?
Thank you in advance.
I have the following two querys which are supposed to return only the latest Note for a Prospect.
QRYNotes
SELECT TBLNotes.ProspectID AS Expr1, Max(TBLNotes.NoteDate) AS MaxOfNoteDate
FROM TBLNotes
GROUP BY TBLNotes.ProspectID;
QRYNotes2
SELECT DISTINCT TBLProspects.CompanyName, TBLNotes.NoteID, TBLNotes.Note, TBLNotes.NoteDate
FROM (TBLProspects INNER JOIN TBLNotes ON TBLProspects.ProspectID=TBLNotes.ProspectID) INNER JOIN QRYNotes ON TBLNotes.NoteDate=QRYNotes.MaxOfNoteDate;
When I run the QRYNotes2, I get more than one record per Prospect instead of just the newest NoteDate.
What is wrong?