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

Queries :: Find A Way For Access To Find Unique Dates And Unique Names?

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

Find Unique Records

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

Queries :: Find Unmatched Query - Design View

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

Unmatched Query To Find Changed Info Between Tables

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

Queries :: Find Unmatched Contents From The Column By Matching With Another Column?

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

Unmatched Records Urgen Help Req . Thanks

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

Find Unique Items

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

Query Not Finding All The Correct Unmatched Records

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

Queries :: UnMatched Query To Delete Records

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

Queries :: Finding Unmatched Records With A Condition?

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

Queries :: Unmatched Records - Could Not Delete From Specified Tables

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

Find Unique Products To Specific Build ID's

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

Unique Records?

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

Unique Records

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

Subforms & Unique Records - Please Help!

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

Can Unique Records Be Counted?

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

Best Way To Produce Unique Records

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

Count Unique Records

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

Cannot Retrieve Unique Records

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

Count Unique Records

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

General :: How To Get Unique Records

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

Unique Records Sorted By Date

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

Unique Records/Values Problem!!

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

Finding Unique Records Between 2 Tables

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

Counting Unique Records In A Query

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







Copyrights 2005-15 www.BigResource.com, All rights reserved