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 Replies


ADVERTISEMENT

General :: Query Last 10 Unique Records

Apr 9, 2014

I got a large transaction file with deplicated records!! For unit price checking, I need to copy the last 10 unique records to another file with the same criteria but price is not the same as the current checking record.

For instance, I got 5000 records with around 400 records unit price is not the same as history. For each checking, I needed to copy the same criteria historical records to a tempory file and then delete corresponding duplicated records first, following by another deleting action only to keep the last 10 records according to date field.

View 2 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

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

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 :: Unique Identifier From Different Sources

Nov 27, 2014

I'm looking into create a small unique identifier to put on top of each invoice generated by the database. (.MDB A2010)where '000' its just 3 zeros added for extra badassery, '1' is the actual ID from the database table and -2014 its the current year when the record was added...So maybe I have info from 2 fields (ID, YEAR)... but How do I add the ceros to the ID field... so its 0001 not 1 or 0135 not 135... ???.

View 5 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

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 2 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

Show Records With Unique Field

Jun 8, 2006

Hi,
I have two tables:
tblOutTransmittals and tblTransmittedDocs. A document is sent with a transmittal document which the recipient signs to confirm that they received it. I've created a query that will show all the drawings/documents and the information about the associated transmittals.

What I want to do is show display records with a unique CCNum and preferably the one with the most recent TransmittalDate.

I've attached a screenshot and here is the sql:
SELECT tblOutTransmittals.CCNum, tblTransmittedDocs.DrawingNum, tblTransmittedDocs.RevisionNum,tblOutTransmittals. *
FROM tblOutTransmittals INNER JOIN tblTransmittedDocs ON tblOutTransmittals.DocID = tblTransmittedDocs.outTransmittalID
WHERE tblTransmittedDocs.DrawingNum="32-35554"

I've tried it using the DISTINCT keyword but that would only work if I'm returning one field. I tried Group By as well but couldn't get that to work.

Thank you,
RCurtin

View 1 Replies View Related

Get All Unique Composite Pair Of Records

Aug 4, 2006

I will try to explain this in as detail as possible. I have One table having numerous columns.
Columns
RowNumber <primary key>
A
E
B
C
D


Now what I am trying to do is retrieve all records in this table where the combination of A and E is unique. e.g. if table looks like

RowAEBCD
111blahblahblah
221blahblahblah
322blahblahblah
421blahblahblah
532blahblahblah
633blahblahblah

When I Run a query I should get only the following records

RowAEBCD
111blahblahblah
322blahblahblah
532blahblahblah
633blahblahblah

I want all such records where combination of A and E is unique.

View 1 Replies View Related

Query Not Delivering Unique Records

Mar 22, 2008

I am trying to set up a query to select PaymentDate, CustSurname, CustForename, AgentName showing only the last record for each customer if the last payment is over 7 days and also include customers with no payment.

the table structure is:

tblLoans
LoanID (PK)
CustID (FK)
AgentID(FK)

tblPayments
PaymentID (PK)
PaymentDate
LoanID (FK)

tblAgents
AgentID (PK)
AgentName

tblCustomers
CustID (PK)
CustSurname
Custforename


I have tried the code as below: but it now gives me every payment for the defaulters as opposed to just the last defaulted payment. Can you shed any light on what I have done wrong.


select b.PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from tblCustomers a, tblPayments b, tblAgents c, tblLoans d
where d.LoanID = b.LoanID and d.CustID = a.CustID and d.AgentID = c.AgentID
and (DATE()-(SELECT max(PaymentDate) from tblPayments f where f.LoanID=d.LoanID ))>7
UNION select '' as PaymentDate, a.CustSurname, a.Custforename, c.AgentName, d.LoanID
from
tblCustomers a, tblLoans d, tblAgents c
where a.CustID = d.LoanID
and c.AgentID = d.AgentID
and (select count(*) from tblPayments where tblPayments.LoanID = d.LoanID) = 0;

View 1 Replies View Related

Unique Values On Similar But Different Records

Jan 31, 2008

This may be a simplistic question but I'm having some diffuculty figuring it out. I have multiple rows of data where I need to use the "Unique Values" property in my query to limit only what I need. However, I have a "time" field and their are similar times on different records. So say I have a 100 records but 8 of them have the same "time" value, how can I use someting like the "Unique Values" property to get my rows down from several hundred to 100, but not lose the 8 similar rows and end up with 92 rows, when I have to turn around and sum these times and need all the times to be there?

Maybe I'm approaching it the wrong way and should be doing a SUM of "times" to begin with for that field, but I'm not being able to get that to work either. Any suggestions? /Thanks

View 6 Replies View Related

Reports :: Count Unique Records Only

Sep 30, 2013

In a report, I have a textbox to show the number of employees in the report.

I use this formula : =Count([last name])

Sometimes the same person has multiple entries, so the count is wrong.

How can I show only the number of different employees and ignore the duplicate names?

View 3 Replies View Related

Finding Unique Records In A Table

Mar 25, 2015

I have one table with 250 fields and hundreds of records. Each field represents a computer we have and the records in that field represent the type of applications we have on that computer. So the table looks like this:

Computer 1..........Computer 2.......... Computer 3........ Computer 250

Microsoft...............Google.................... .Paint.....................Microsoft
Windows Hotfix......Security Updates.......Java ......................Google
Java.....................Mediaplayer.............. Google.....................Java

As you can see, most computers have the same applications, but some have applications that others do not. I just need the list of applications we have from all computers. Meaning each app is listed only once.

I just need each application listed once. So I was trying to come up with a way to delete all the duplicates or use a Totals query to group the apps but since my table has 250 columns I'm not really sure how to go about it. What kind of query would be best to make this work?

View 3 Replies View Related

General :: Auto Generate Unique Customer IDs

Aug 16, 2012

I am in the process of creating a DB for customer names, job numbers and document numbers, but have run into a problem right from the start.We are using a naming convention of the first 3 letters of the customer name, and 3 numbers after that. Eg. Customer names Allstar, Allpro would be All001 and All002 respectively.

The job numbers would then be sequential numbers, for each customer. So the first job for Allstar would be All001-001 and so on.How do I get Access to check/create the customer ID's? And then how do I get Access to check for the next job number for each customer?I was able to do this in Excel with the following;

=LEFT(B12,3)&COUNTIF($B$3:B12,LEFT(B12,3)&"*") in one cell
=IF(B12="","",LEFT(D12,3)&TEXT(RIGHT(D12,1),"00")) in another to add the preceding zero's.
Obviosuly , Excel is not the best way of tracking customers, jobs, documents etc.

View 14 Replies View Related

General :: Unique Values For Two Column Combobox

Jul 18, 2013

Here's my dilemma: I have a table that keeps track of job postings and how many people applied for each posting. So, for instance, I can have 20 people apply for a job posting say....on July 18. On another column I have the shift name for that posting such as DAY Shift. So, I have 20 people that have applied for the Day Shift on July 18.

What I'd like my combobox to show me in just one instance of July 18 in column 1 and one instance of Day Shift in column two. I have tried using the wizard and then going to the properties of the combobox and attempted to edit the SQL statement to use the DISTINCT value to no avail.

View 14 Replies View Related

Count Function To Return Unique Records

Sep 20, 2005

Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..

Here are my fields..

I've assignment_num field, project_num field, project_name field, emplyee_name field.

here are my data..

assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally

What I want is to display the employee name with 2 projects or more...

What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..

pls help.

View 13 Replies View Related







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