Find Unmatched/Unique Records
Mar 14, 2006
Hi all,
I thought this would be relatively simple task to complete but its proving tricky.
I have two tables with the same data: E1 and E2
E1 has two columns and is as follows
A 1
B 2
C 3
D 4
E2 is similar except for one record '5'
A 1
B 2
C 5
D 4
Now the task is to show those records that do not match...in this instance
E1.Field1 E1.Field2 E2.Field2
C 3 5
Now I've used the find unmatched wizard and this is the SQL:
SELECT E1.Field1, E1.Field2, E2.Field2
FROM E1 LEFT JOIN E2
ON E1.Field2 = E2.Field2
WHERE (((E2.Field2) Is Null));
this is giving me partially the right answer as in
E1.Field1 E1.Field2 E2.Field2
C 3
Anyone else think of some other way?
View Replies
ADVERTISEMENT
Aug 1, 2014
I have been working on a simple data base for some time now (beginner level) and am still trying to improve it. I would like to do something but before that I would like to have your opinion to know if it is even possible?I have a query QryMainReport:
Start Date/Time
End Date/Time
Employee
At the moment this is what the format of my report looks like (I removed other unnecessary fields):
StartTime----------EndTime---------------Employee
12/06/2014 01:00--12/06/2014 03:00------John Smith
12/06/2014 04:00--12/06/2014 06:00------Jane Doe
13/06/2014 02:00--13/06/2014 05:00------John Smith
13/06/2014 08:00--13/06/2014 08:00------Jane Doe
I would like to do as a report. (Dates would always be from Sunday to Saturday). I am not sure it is possible to do that. I suppose first it would mean:I would have to do a query to separate the times from the dates?I would have to find a way for Access to find the unique dates and unique names?Does it mean I have to use cross tab queries?
View 2 Replies
View Related
Mar 23, 2006
Feeling stupid today...how can I query a table to find the last five unique records?
Table1:
PrimaryKey
Field1
Field1 may contain duplicates. I need the five latest entered unique records in Field1, and am struggling to write a query for this.
Many thanks to anyone who can help out.
[Still using Access 97]
View 1 Replies
View Related
Jun 19, 2015
I need constructing a find unmatched query. I don't really know what I'm doing with SQL and I think that I can only take things so far with query design view.
I have two tables. T_Productions and ProPro (see attached). In Access query design, I can only specify one matching criteria. I need a query that will show me records from T_Productions that don't have matching Production_Code (PpNum in ProPro), Avantage_Product_Code (PpPrNum in ProPro) and Step (PpEtape in ProPro) in ProPro.
What I have now only gives me the records where Avantage_Product_Code don't match PpPrNum.
Code:
SELECT T_Productions.ID, T_Productions.Production_Code, T_Productions.Avantage_Product_Code, T_Productions.Step, T_Productions.Quantity_Required
FROM T_Productions LEFT JOIN ProPro ON T_Productions.[Avantage_Product_Code] = ProPro.[PpPrNum]
WHERE (((ProPro.PpPrNum) Is Null));
View 4 Replies
View Related
Aug 24, 2012
I am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
View 3 Replies
View Related
Nov 16, 2013
i have a columns as 1. contactname, 2. firstname 3. lastname 4. email and in this columns some emails are not matching with the contactname or some time firstname or some time lastname so i need the to find out the un matched contacts from the database.
View 1 Replies
View Related
May 22, 2006
Hello all, I have 2 tables.
I wish to fetch all the records which are not common in two tables.
I do not have Exp with the Unmatched query wizard. I want to sleect the records based on more than one criteria and wizards allows to select on only one field.
Any suggestions please.
Thanks!!
View 1 Replies
View Related
Aug 6, 2007
Hello Access experts,
I try using query below to find the unique items in a Field but it doesnt work. Can someone help me with this query?
Thanks very much in advance.
In (SELECT [project_item] FROM [Table1] As Tmp GROUP BY [project_item] HAVING Count(*)=1 )
View 2 Replies
View Related
Mar 31, 2006
I have several tables that I am trying to get information from:
Clock Number Table with fields: Clock #, Name, Title, Dept, Term, HireDate, & TransDate.
Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level
Linked Table, Controlled Documents with fields: Doc #, Title, Effective Date & Rev Level
Linked Table, Distribution Table with fields: Doc #, Distribution, Rev Level & Effective Date.
Some of the tables have more fields that those listed, but they do not pertain to this query.
I am trying to create queries that will provide me with the names of employees who have not been trained on Controlled Documents that have been distributed to their department.
So far, I have been able to determine if no one has been trained, but if even one person has been trained, they do not appear on my list. I would like to know the Clock # and name of those that have not been trained.
I have created 3 queries to get this far. Query 1 is the Clock # Table and the Completed Training Table joining the Clock #. Query 2 is the Controlled Document Table and the Distribution Table joining the Doc #, Rev Level. Query 3 takes these queries and joins Doc # and Rev Level. and pulls records where the Rev. Level and Doc # is Null.
View 2 Replies
View Related
Oct 25, 2014
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code:
DELETE Warranty
FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair]
WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
View 3 Replies
View Related
Dec 26, 2013
I want to find unmatched records, where there is a condition. Specifically, which employees did not get a specific mail.
My employees are in this table:
tblEmployees
---------------
EmployeeID (PK)
FullName (text)
and sent mails are logged in tblMailsSent
tblMailsSent
--------
MailID (FK)
EmployeeID (FK)
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
View 3 Replies
View Related
Jun 19, 2015
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
View 8 Replies
View Related
Dec 14, 2006
I have a table with 5 fields:
Build ID Process Product Product_De Operation_
What I am trying to accomplish is as follows:
Each Product can be in the table mulitiple times depending on how many Build ID's it has. So if Product 123456 has Build ID's G004, E818, N005, F813, D024, C879 it will show up one time each for each Build ID. What I want to query is unique Products that are for Build ID's D024 & C879 only and not for G004, E818, N005, & F813.
How can I accomplish this with a query. I am sure I am making this harder than it is but I sure need help.
View 1 Replies
View Related
Sep 2, 2004
I have sort of a complex unique fields question. And I know there are probably quite a few ways to get at the answer, but I'm a novice so the simplest way would be best. I have a table (with over 25M rows, otherwise I'd be doing this in excel) with many columns. One of the columns is employee ID, and another is a date. Each employee ID however can have multiple rows with different dates. As an end product I need to have one row for each employee ID with the most recent date. An idea I had would be to sort by employee ID and then by the date. Then insert a column of if statements that asks if a record has the same employee ID as the record below it, but I don't have any experience with if statements in access (and VERY little SQL experience). But there is probably an easier way. Any help would be appreciated. Thanks!
View 1 Replies
View Related
Sep 8, 2006
I have a database that contains sales order information. A sales order could have multiple records (if that sales order has several different items ordered) or just one record. There is a status column in each record that shows a "C" (for closed). For items that were not delivered in a particular sales order, the status column is left blank.
Here is an example:
Sales Order Item Ordered Status
908111 Coaxial Cable C
908111 Transreceiver C
908111 Connector
908112 Coaxial Cable C
908112 Transreceiver C
908112 Connector C
The above example shows that only 2 items from sales order # 908111 were delivered or "C" but one of them is not. For sales order # 908112, all items were delivered or "C". I want to take all records of sales order # 908111 and show it on a table of partially delivered and all records of sales order # 908112 in delivered table.
I've tried different filtering options and group by but it doesn't seem to be working. Anyone has any ideas?
View 1 Replies
View Related
Apr 12, 2006
Firstly, my thanks to everyone who is going to be able to help with my problem, and also my apologies if this thread isn't in the right forum.
Ok, I am trying to construct a database at the moment for a small business. The database holds a number of customer records, and all of the basic design and construction I am skilled enough to write myself.
However, I'm running into major problems whilst trying to tackle a certain task...
The Required Situation
I have a form called Contacts. This form shows all of the customer details, laid out neatly, and draws its information from a table of the same name. This aspect works fine.
Now what I've been asked to look into is the possibility of adding two buttons to this form. Each button, when clicked, will open a pop-up window where extra details, such as a log of received telephone calls, can be entered and searched if necessary. the two buttons should do the same thing where the code is concerned, but store their details separately (one button is for sales calls logged, the other for support calls).
Now it is essential that these records are tied - uniquely - to whichever record the database user was on when they clicked the button.
My Current Thinking
I'm stumped. Well, half stumped. I think that the solution lies in sub-forms, but I'm not nearly skilled enough to actually impliment them (and don't want to waste my time doing so if there's an easier way).
Currently I have two tables, contacts and calls, the former to store the customer information, the latter to store the calls that are entered on this new form.
The form contacts has a button on it which opens the form calls. Now I can go to any record I like, click the button and open the calls form. I can enter details for such, and the record gets saved to my calls table. The problem is that it is not unique. No matter which record I'm on when I open the calls form, the details are always the same.
Yes, I know the absoute easiest way is to simply have a few fields on the main contacts form to log these details, and that it will uniquely tie to that record, but unfortunately that is not a practical solution. These calls may run into the hundreds, and must be easily navigated by the database users.
If I've not been clear enough, I can attach a copy of my database for you guys to take a look at, but what I'm after in the first instance is (a) whether there is an easier way to solve my problem, and, (b) if not, whether I'm right about needing subforms.
View 14 Replies
View Related
Jun 23, 2006
Hello
New to Access, new to this forum
I hope this is the right place for this question?
I have a table of data, with the following headings:
"group1","group2","groupage","Reference","name","team","pres","CB","CBPR","LA","LA PR","IS","ISPR","N","NPR","Origin"
I am trying to get a count of unique "References" (there are normally over 20,000) for each "group1" or "group2" by "groupage"
I can do this using Excel........but it is a very long winded process getting the data ready to use a pivot table.
Can anyone help me? Or do I need to provide more information?
Any help will be greatfully received. :)
View 10 Replies
View Related
Jul 25, 2005
Hello, hoping for some help on the following scenario:
I need to produce a query which results in one record for each part number.
In a sample table of purchasing history (tblPODetails):
Fields:
PartNumber, Description, Cost, DatePurchased
We have purchased any given part number numerous times over the years. From time to time, descriptions have changed, as have our cost and of course the date received.
I am attempting to create a query which gives me a list of part numbers with no duplicates, showing the part number, description and cost by max date received.
The resulting description field would of course be the one in use for the latest received date.
I am not having much luck. When I add more fields to the totals query (other than part number and max date received), I get duplicates. I know this should be simple for me...
View 4 Replies
View Related
Sep 15, 2006
Hello, I am new to Access and trying to run a query that will count only unique invoice numbers in my table. I am sure this is an easy command, and thank you for your help!
View 3 Replies
View Related
Mar 14, 2006
:confused:
I have been working on a normalized db for the past few months and it works wonderfully. But now I need to find a way to create a form so that the rest of the company can update their data.
My Table has the 5 following fields. Also listed is an example of the types of data I have there
StoreID_Catagory_Customer Year_Month_MixPercentage
IN00001_Japanese_____2005_____Jul_______.01______
IN00001_Japanese_____2005_____Aug______.02______
IN00001_Japanese_____2005_____Sep______.01______
IN00001_Chinese______2005_____Jul_______.35______
IN00001_Chinese______2005_____Aug______.25______
IN00001_Chinese______2005_____Sep______.26______
IN00001_Japanese_____2006_____Jul_______.01______
IN00001_Japanese_____2006_____Aug______.02______
IN00001_Japanese_____2006_____Sep______.01______
IN00001_Chinese______2006_____Jul_______.35______
IN00001_Chinese______2006_____Aug______.25______
IN00001_Chinese______2006_____Sep______.26______
As you can see I have multiple StoreID's for one store, IN00001. My db goes up to IN00419 so it ends up displaying 29,664 records. There are 4 different categories (Japanese, Chinese, Local, and Other). Until now everything has been taken from a datasheet and put into an Update Query but now that other people have to quickly access the system I need a slick interface.
What I need is a way to have a form that can update the table and add new entries that looks like this in the form:
IN00001____2005________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
IN00001____2006________Jul__Aug__Sep__Nov__Dec__EC T.
Japanese MixPercentage__.01___.02__.01___.03__.01
Chinese Mix Percentage___.35___.25__.26___.33__.30
Local Mix Percentage_____.60___.70__.69___.62__.65
Other Mix Percentage_____.04___.03__.04___.02__.04
I hope this gives you a clearer picture. I need to be able to update 96 entries at a time (12months, 4 customer types, 2 years) but do it in an efficient manner. The table only has 5 fields so I need to figure out how to display multiple entries on the same form. I also need a way to add new entries. In other words for me to add another StoreID such as IN00420 then I would have to enter in a value for every field for 96 records in the table. When in reality I should only need to update the mix percentages since the months and years remain constant. HELP!!
Any help would be much appreciated. Due to the variable nature of the db I cannot establish very many one-to-one relationships. So therein lies another problem.
View 3 Replies
View Related
Sep 10, 2003
Hi there:
I have a Question
In Access 2002 (XP) I have to write a query that displays unique Id's:
For Example:
CustomerID Company Name
1 ABC INC
1 ABC INC
2 XYZ INC
2 XYZ INC
3 PQR INC
The query should be 3 records.
But for some reason when I write the query in sql view it doesnot work.
Thanks,
Ashi
http://www.ringvirginia.com
View 12 Replies
View Related
Oct 24, 2012
I am trying to build a query using two tables. One is a table called sanctuary lakes cleaning data. The other one is called Faulire data. In cleaining table there are four years of cleaning records for each pit. I want to find the performance of these cleaned pits using failure table. Each failure date should have only one correspoding cleaning date. I wrote the following query. This query works. But it gives duplicate data. Some of the records will show two cleaning dates for a failure.
My query is like this.
SELECT DISTINCTROW [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], Min([Failure data table].Failure_date) AS MinOfFailure_date, [Failure data table].[Failure SR no]
FROM [Failure data table] LEFT JOIN [Sanctuary Lakes cleaning data] ON [Failure data table].Pit = [Sanctuary Lakes cleaning data].PIT
GROUP BY [Sanctuary Lakes cleaning data].PIT, [Sanctuary Lakes cleaning data].[Cleaned Year], [Sanctuary Lakes cleaning data].[Cleaning Date], [Sanctuary Lakes cleaning data].[SR NO], [Failure data table].[Failure Year], [Failure data table].[Failure SR no]
HAVING (((Min([Failure data table].Failure_date))>Min([Sanctuary Lakes cleaning data]![Cleaning Date])))
ORDER BY [Sanctuary Lakes cleaning data].PIT;
My results are as follows:
PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
1-08 2007/08 16-Oct-07 213458 2011/12 13-Aug-11 414984
1-08 2007/08 16-Oct-07 213458 2011/12 25-Jun-12 478589
1-08 2009/10 19-May-10 313497 2011/12 13-Aug-11 414984
1-08 2009/10 19-May-10 313497 2011/12 25-Jun-12 478589
1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
1-08 2010/11 22-Feb-11 379081 2011/12 25-Jun-12 478589
1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589
I want the following results.
PIT Cleaned Year Cleaning Date SR NO Failure Year Failure_date Failure SR no
1-08 2010/11 22-Feb-11 379081 2011/12 13-Aug-11 414984
1-08 2011/12 17-Apr-12 458430 2011/12 25-Jun-12 478589
How can I modify my query?
View 2 Replies
View Related
Jan 10, 2006
Hi All,
Just a quick enquiry if I sort a query by a date field in ascending order then select unique records will I get the latest records.
Thank You
View 1 Replies
View Related
Jan 13, 2006
Here's my situation...
I'm making a query that uses information on company members from a database. The most important data field is the MemberID. Many members are listed in the table more than once because different records are put in when they have more than one contact person or health plan, etc.... However, right now I am trying to have the query make a table that produces only one record for each company so I can get a total number of companies. Therefore, I dont want the query to produce records with duplicates of the MemberID. My problem is that by setting Unique Values to 'Yes' doesn't work because if a different field in the record is different, it makes the record unique. I can't figure out a way to make it so only one field is considered for unique values. The funny thing is that it is simple to do this in Excel, you just gotta go to advanced filters. Does anybody know how to do this in Access?? Thanks.
View 14 Replies
View Related
May 12, 2006
Dear all,
Many thanks for taking the time to read this.
I have a slight problem which seems like it should be easy to solve but I have no idea how to do it.
I have 2 tables (table1 and table2 for instance) Table2 contains more data than table1 but should contain everything in table1 as well as more.
How is it possible to return just records that are in table2 that are not in table1
Eg
Table1 contains:
IDab
112
222
322
422
511
611
711
Table2 contains:
IDab
112
222
322
422
511
611
711
833
The query should return just the line:
833
as this is unique.
thanks again
View 6 Replies
View Related
Jun 1, 2006
I'm having trouble getting a query to return a simple count of unique lot numbers for a given ProductID. The data is stored in a large table where each test result of a stability program is stored. Each result has an associated lot number, product id and several other data fields. I've managed to get a combination querries to return the count, but if the lot has both real time and accelerated data then the counts are added and reported as double for each type. The current SQL is as follows.
SELECT tblProducts.ProdName, Count(qryAccelerated.Lot) AS AccelCount, Count(qryRealTime.Lot) AS RTCount
FROM qryRealTime RIGHT JOIN (qryAccelerated RIGHT JOIN tblProducts ON qryAccelerated.ProductID=tblProducts.ProdID) ON qryRealTime.ProductID=tblProducts.ProdID
GROUP BY tblProducts.ProdName
ORDER BY Count(qryAccelerated.Lot) DESC , Count(qryRealTime.Lot) DESC;
qryAccelerated and qryRealTime are simple SELECT DISTINCT querries returning the product id and a list of unique lot numbers for that ID.
(e.g. SELECT DISTINCT tblResults.ProductID, tblResults.Lot FROM tblResults WHERE (((tblResults.TypeID)=3));)
Currently the top query returns 4 in the both the AccelCount and RTCount columns when there are only 2 unique lots for the product. Other products without both real time and accelerated lots count correctly.
Any help is greatly appreciated. Thanks.
View 4 Replies
View Related