Unique Records Sorted By Date
Jan 10, 2006Hi 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
ADVERTISEMENT
Modules & VBA :: Count Number Of Unique Records Based On Range Of Date
Jan 19, 2015Code:
' count records in query
Dim rs As DAO.Recordset
Dim db As Database
Dim strSQL As String
Dim beginDatum As String
Dim eindDatum As String
Set db = CurrentDb
[code]....
MAX Date Query - How To Have A 'unique' Date?
May 10, 2007My database holds details of visits made to customers by support staff.
I am running a query which returns details of the last visit made to a customer as below with results displayed on a webpage.
SELECT Visits.Date1, customers.CustID AS customers_CustID, customers.Customer, Visits.CustID AS Visits_CustID, Visits.Visitee, Visits.VisitType, customers.RegionID, customers.NextVisit, customers.NextVisitBy, customers.Confirmed
FROM customers INNER JOIN Visits ON customers.CustID = Visits.CustID
WHERE (((Visits.Date1)=(SELECT MAX (Visits2.Date1) FROM Visits AS Visits2 WHERE Visits2.CustID = Visits.CustID)))
ORDER BY customers.RegionID, customers.CustID
SELECT MAX on Date1 is used to select the most recent (largest) date. The problem I have is that if 2 different staff members visit the same customer on the same day, the query logically returns 2 MAX date results for that customer, whereas I only want 1 result per customer. As the dates are the same I am not sure how to proceed. It doesn't matter which of the 2 results are returned. Does anyone have any ideas? Thanks in advance :)
ps - I can't perform MAX on the VisitID as the support staff don't always enter the visit data in the correct order meaning that an older visit date might have a higher VisitID than a more recent visit.
Unique Records?
Sep 2, 2004I 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 RelatedUnique Records
Sep 8, 2006I 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?
Subforms & Unique Records - Please Help!
Apr 12, 2006Firstly, 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.
Can Unique Records Be Counted?
Jun 23, 2006Hello
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. :)
Best Way To Produce Unique Records
Jul 25, 2005Hello, 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...
Find Unique Records
Mar 23, 2006Feeling 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]
Count Unique Records
Sep 15, 2006Hello, 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 RelatedCannot 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.
Count Unique Records
Sep 10, 2003Hi 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
General :: How To Get Unique Records
Oct 24, 2012I 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?
Unique Records/Values Problem!!
Jan 13, 2006Here'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.
Find Unmatched/Unique Records
Mar 14, 2006Hi 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?
Finding Unique Records Between 2 Tables
May 12, 2006Dear 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
Counting Unique Records In A Query
Jun 1, 2006I'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.
Show Records With Unique Field
Jun 8, 2006Hi,
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
Get All Unique Composite Pair Of Records
Aug 4, 2006I 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.
Query Not Delivering Unique Records
Mar 22, 2008I 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;
Unique Values On Similar But Different Records
Jan 31, 2008This 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
General :: Query Last 10 Unique Records
Apr 9, 2014I 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.
Reports :: Count Unique Records Only
Sep 30, 2013In 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?
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?
Finding Unique Data Using Date Field
Mar 27, 2008Hi
I have a table called SWAPS which contains information on each user and the different equipment he has been issued. Query at the moment contains 5 fields
Payroll, Surname, Firstname, Mobile No and Date sent.
SQL for this query is as follows
Select Swaps.PR, Swaps.surname,swaps.firstname,swaps.[mobile no],Max(Swaps,[Date Sent] as Lastdate
From Swaps
Group by swaps.pr, swaps.surname,swaps.firstname,swaps.[mobile no]
Order by swaps.pr;
Now when I run this I get the results I want by date so I get
123456 Smith Fred 0788411025 12/3/8
456789 West Bob 012555447854 13/1/8
So it correctly ids all the last dates for me as I want to see the latest issue date for that user. Now I want to add in another column to this query which contains the serial number of the equipment issued and heres where the problem lies. If a user has been issued more than 1 piece of kit, I get all the serial numbers irrespective of date. How do I get it to display the serial number for the latest date?
Thanks
Forms :: Use Only Year Portion Of Date For Unique ID?
Apr 2, 2014trying to get something working on a form. When creating a new client, due to privacy, we have to create a Unique ID for each one. The ID consists of their initials, the year, a serial number and the location. I have it mostly working except for the year. Let me show you what I have and the results, then what I would like the results to be.
In the control Source of the text box is:
=Left([FName],1) & "" & Left([LName],1) & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![NICN_Date] & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![NICN_Serial] & "-" & [Forms]![frm_20_Enter_New_Client_Info_01]![Combo1]
Form Input:
FName = John
LName = Smith
Date = 2014 (Using Date format and 'YYYY') and default Now()
Combo1 = NW
Results: JS-4/2/2014 9:32:51-18-NW
Desired Results: JS-2014-18-NW
So everything is working but I'm getting the entire and I just want the year.