Finding Most Recent Duplicates?
Aug 29, 2006
Hi, been searching this forum for a couple of months now im working with access and up till now i have solved most of my problems:D
Ok the problem.
For the purpose of the question I have a training database:
running one table with user id and user name.
another for job id and job name.
Another for training table with an autonumber, user id, job id, and date
Id like to build a query which bought up the newest only for each user id and job id.
eg.
UI JI Date
2 5 01/05/05
2 5 01/05/06
3 5 01/05/05
3 6 01/05/05
4 6 01/05/06
to
UI JI Date
2 5 01/05/06
3 5 01/05/05
3 6 01/05/05
4 6 01/05/06
Iv tried using a few different ways of using duplicate queryies etc, but im just too newb to figure it out :(
Any suggestions or help would be appreciated.
Thanks
View Replies
ADVERTISEMENT
Jul 16, 2005
I have a database with many tables, but only two are relevant to my question.
tblPersonnel holds (amongst other fields) UniqueStaffNo (primary key), Name, Dept, FirstAider (Yes/No).
tblCourses holds details of all courses attended by staff and is linked to tblPersonnel by the UniqueStaffNo. Fields in this table include the CourseTitle and CourseDate (short date format).
I can easily identify who is a First Aider by selecting on tblPersonnel.FirstAider and I can identify which of the thousands of course records are First Aid related by looking at tbl.CourseTitle.
My question is - how can I produce a list of all current first aiders showing ONLY their most recent first aid course (latest CourseDate) so that I can calculate when they need to renew their certificate?
Any help more than gratefully received. Thanks.
View 1 Replies
View Related
Nov 1, 2006
Hi
Is there a way of finding duplicate field entries in a table and a query: e.g.
In the Sickness Query (which contains, say 10 fields), in the Section field, it says "Benefits", and in the Week Commening field, it says "15/10/06"
And then
In the Nil Return Table (which only contains the Section & Week commencing fields), they also say "Benefits" and "15/10/06"
If there is a way of finding them, how do I delete the entry that has been made in the Nil return table?
Thanks
Maria
View 4 Replies
View Related
Jan 21, 2005
I have a Lost and Found Database that I enter information in usually every day.
Lots of items like gloves and glasses are entered into the item/s description field as
name of item then color or some other descriptive.
How do you make the combo box bring up all of the items that are related so that I can
pick from a tighter list? Right now when I click on the combobox for say, gloves, it will
bring up only one record of gloves and then I have to scroll through the entire
database to find the right record.
Table Properties
Lost and Found ITEM/S DESC Field:
Field Size - 50
Required - Yes
Allow Zero Length - No
Indexed - No
Unicode Compression - Yes
Form Combo Box After Update [Event Procedure]
Private Sub Combo22_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ITEM/S DESC:] = '" & Me![Combo22] & "'"
Me.Bookmark = rs.Bookmark
End Sub
View 4 Replies
View Related
May 16, 2005
Sorry for the question's wording.
Here is what bugs me:
- I have one table tblRefctc with the followings fields :
LCtcRef,../..,LRefServ0, LrefServ1,..LrefServ16.
Where all those Lref are long; LCtcRef being the primary key. This table describe a contact from different branches (account, etc..) so while each branch has the relevant information about this contact, they know that that branch too is in contact with the same person.
- I have this other table with describes Events, TblIdxEvt (meeting, whatever) :
LRefidx,IdxEvt,IdxCtc
where IdxCtc is filled with LCtcRef once an event is created.
What I must check for is : For the same IdxEvt, should another branch makes an appointement for LCtcRef (i), I must check if one of the LRefServ0 to LRefServ16 are not already added in the event table.
At this time, I am stuck with making functions which test the existence of the LRefServ(i) (i ranging from 0 to 16) in tblIdxEvt, but I wonder if there is a simpler way to do it with SQL (which, as you could guess, Im not good at).
Thanks in advance for any pointers.
View 2 Replies
View Related
Apr 2, 2014
I need to create a query that will pull duplicate names out of my db.
I would like it to pull all names that have:
duplicate LastName and duplicates of the first 3 letters of the FirstName.
For example, if I had the names:
Bland, Abe
Brown, Abe
Brown, Bill
Buster, Jon
Buster, Jonathon
I would like my query to return only Buster, Jon and Buster, Jonathon.
View 4 Replies
View Related
Dec 13, 2007
Hello,
I am just starting Access...
Or at least trying to learn it on a small project, and I need a jumpstart, if someone woild be so kind and help me out.
I need to organize my e-mail sendings.
In one table (Table Sent) I have e-mail addresses where I have already sent messages.
In an other (Table New) I have some other e-mail addresses, I am preparing to send out.
To make sure to not send mail to those who already got one, I need to compare the two tables, and delete from the Table New those addresses which are already present in Table Sent.
I guess that would be a 2-3 line SQL script...
All of my attempts so far produced no results
Any guidance would be great!
Thanks
Attila
View 14 Replies
View Related
Jun 13, 2013
I am using Access 2010 32-bit on a Windows 7 64-bit platform.I have a database that I have imported existing data into. For the most part, I have eliminated duplicate entries in my Item Number field.
Obviously I have missed at least one (or possibly more) duplicates. When I try to create a NO DUPLICATES index, I am informed that there are duplicate entries.I am looking for a quick way to generate a list of values that are duplicated so I can address those and correct them.Item number field is a number field of type DOUBLE with 2 decimal places.
View 2 Replies
View Related
May 2, 2014
I've been looking everywhere to find a better way of finding duplicates in a table and then recording and adding another value in that record together.
Let me try to explain better.
example:
I have a table that has 2 columns "Name", "DOB". I would like to find all duplicate "DOB" and add all of the "Name"'s together.
Quote:
Name DOB
bob 19800201
Sam 19761211
Jim 19800201
The output I would like is to have Name = bob & Jim DOB = 19800201.
I've tried using the find duplicate wizard in access but I can't seem to group them together and just to find the duplicates it takes upwards of a minute.
View 1 Replies
View Related
Mar 8, 2013
I've been working on trying to get this code to work as expected for days. I'm trying to find duplicates (I can't use primary keys or indexes alone to weed out duplicates due to the structure of the tables involved) in a subform as a user enters data. As soon as a project number is added, the code is supposed to count the number of records that contain that particular project number as well as a category number (there can be multiples of the same project numbers as long as their category numbers are different). This is the code I am using in the "Before Update" event of the field in the subform:
Private Sub ProjectID_BeforeUpdate(Cancel As Integer)
Dim strCriteria As String
Dim RecCount As Integer
strCriteria = "([ProjectID] = " & Me.ProjectID & ") AND ([CatID] = " & Me.CatID & ")"
RecCount = DCount("[ProjHrsID]", "tblProjHrs", strCriteria)
[Code] .....
What could I be doing wrong? Nothing about this code seems to work properly - even the Undo and Cancel=True is a problem (I get the "No current record" error).
View 2 Replies
View Related
Feb 19, 2008
Hello all
I'm sure the answer is on here somewhere but trying to find it and then getting it to work is a bit of a problem, so i resorted to posting.
I have 5 tables
Partnership Details, Invoices, Funding, Communication, Communication Types.
the field Partnership Name in the Partnership Details table has a one to many relationship with the partnership name on tables: Invoices, Funding and Comunication.
What I want to do is create a query that will show the most recent invoice (detirmined by date) and the most recent funding (detirmined by date) for each partnership.
I can do this using max if I only want the feilds Partnership name, invoice date and funding date. however I want to create a report that will show several feilds from partnership details and I want to show the invoice no. and amount as well as the date and also the amount and notes field for funding
Is anyone able to help?
Thanks
Niyx
View 3 Replies
View Related
Feb 12, 2008
Hi this is my first post... so hi all :)
ok what i have is a table with contact details 900k plus
there are about 90k of which are duplicates.
this is the basic feilds that are important in this case.
Id, data_source, data_recived, data_code,
what i want is to have a table with unique records (no dups in data_code)
this table will look like this...
Id, data_code, Num_dups, dup1_source, dup1_date, daysbtw_Dup1_dup2, dup2_source, dup2_date, daysbtw_Dup2_dup3 ,dup3_source, dup3_date, daysbtw_Dup3_dup4 ,dup4_source, dup4_date,
I know there is no more than 4 dups of each record.
what i want from this is a table that will give me a record of how many dups for each record then all the dates that they were added and the date between each record entry.
if anyone can help it would be great .
thanks in advance.
View 6 Replies
View Related
Dec 8, 2005
Hey, all! Thanks for helping, here is my situation.
I have a table with about 70,000 records that have duplicate Address field values. The rest of the field values for those records are different. When I do a find duplicate querry I get the result that 17,000 records have the same address. However, when I do the append qurrey as instructed here: http://support.microsoft.com/?kbid=209183 I get a total of only 600 records in the new table. I have tried deleting all of the indexes for both the new and old table, with no luck.
I'm using Access 2000 on XP Pro.
If anyone could help with this I would greatly appreciate it!
Thanks
Will
View 14 Replies
View Related
Nov 1, 2014
I have an Access table with an indexed key that is currently set to duplicates OK. There are not supposed to be any duplicates in that field. But it is possible than a small number have crept through.
Two questions:
1. What would happen to those duplicate records if I changed the setting from duplicates OK to no duplicates?
2. Is there any way to ferret out those duplicate records first and change them manually? The table currently has 48000 records so it would be a pain to go through them page by page. (I know how to export them to Excel, where the dups can easily be found; I was just wondering if something could do it within Access.)
View 2 Replies
View Related
Aug 11, 2005
I have a table called Tests that contains 80+ tests that need to be performed on our system. A second table is Test_Data. It contains the test results. I need a query that will pull the most recent test data for each test in the Tests table. So supposing there are 83 tests, I should only get 83 results from my query. I am will need to join the tables as I need to get some of the descriptive information from the Tests table. Another problem I have is some tests have not been performed even once, so not every item in the Tests table has a corresponding entry in the Test_Data table.
Any thoughts on how to do this?
View 4 Replies
View Related
Apr 6, 2006
Hi,
I am trying to set up a query which will filter out the 10 most recent additions by date.
My table contains a 'Title' field and a 'Date Added' field. I am trying to get the 10 most recent titles up in a query so I can then create a report based on this.
So far I have managed to single out the most recent date simply by using the MAX function but am a bit lost from there on..:(
View 2 Replies
View Related
Apr 29, 2008
Hi, I have a a table that keeps notes about the status of an invoice. It can have 1 or a lot of notes per invoice. I need it to just display the last 4 notes for each invoice. I used TOP 4 but it only shows the top 4 notes and note the top 4 notes for each invoice. Can anyone help?
View 8 Replies
View Related
Jul 3, 2005
Hi every body. I want create a query that has the following fields on it:
Project : project number
Year: year that project is carried out
Weekno: week no that project is carried out
Task: Task number that is done for this project
Employee: employee number that did this task
Amount: amount of salary given for this employee for this particular project
I used access query designer and selected the db_hours_worked table and dbo_hourly_wages I joined the employee fields together and project fields together. But I cannot join weekno in both tables together since an employee
can have different salary in different weeks!
Amount is calculated by multiplying the number of hours worked during this week multiplied by most recent salary of that employee for the particular project. I be happy if some one show me how I can get the most recent salary of employee from dbo_Hourly_wages table then multiply it by number of hours worked this week and put it in amount column. For the current population of db_hourly_wages as u can see in the picture posted the amount that I want to use for amount calculation is 40.
I managed to write part of the query but it does not out put a result and also it does not calculate the salary amount. Thanks
- One employee can work in more then one project
- One employee can have more then one salary (amount) for the same
project because he might get raise in salary!
- Only tasks carried this week will be printed in the weekly report
Code:SELECT dbo_Hours_worked.Project, dbo_Hours_worked.Year, dbo_Hours_worked.weekno, dbo_Hours_worked.Task, dbo_Hours_worked.Employee, dbo_Hourly_wages.amountFROM dbo_Hours_worked INNER JOIN dbo_Hourly_wages ON (dbo_Hours_worked.Employee = dbo_Hourly_wages.Employee) AND (dbo_Hours_worked.Project = dbo_Hourly_wages.Project);
http://i5.photobucket.com/albums/y180/method007/constraint.jpg
==>pic of database
http://i5.photobucket.com/albums/y180/method007/reporterror2maximumofamount.jpg
===> pic of hours wages table
http://i5.photobucket.com/albums/y180/method007/reporterror1.jpg
====> pic of query in design view
http://i5.photobucket.com/albums/y180/method007/queryresult.jpg
====pic of query result
View 1 Replies
View Related
Dec 11, 2007
Hi, Im building a DB that basically tracks the hours people have worked,
its all going fine apart from one major bug which i cant get my head around, I am trying to work out a way so that if someone decides to change the hours they have worked it replaces the existing record and does not create a new record.
Each record has a unique user ID attached and each day is dated, do you think this is a VBA solution or a change in the relationships perhaps?
Sorry fi there is already a similar answer to this question on the forum, but i couldnt find one!:confused:
Thanks in advance,
Paul
View 2 Replies
View Related
Jul 22, 2005
Using MS Access XP
Here's the scenario. I have 3 fields [AgentID], [QA Date], [Points Issued]. What I want to do is run a query that will give me the last date [QA Date] a point [Points Issued] was issued for each agent [AgentID]. And I want the query to display all 3 fields according. Is this possible?
View 2 Replies
View Related
Oct 6, 2005
I need to know if it is possible to make a query that selects the highest or lowest value below or above a number that a user or form defines.
For example, the attached database has a history of which of three children held the position of mom's favorite by recording the day that they became mom's favorite, the idea being that they stayed there until someone replaced them.
It is possible to make a report that would request a date from the user and then would return with who was mom's favorite on that date? Perhaps by checking the records to see which record had the most recent date before the date entered.
Just in case you haven't noticed, this is not the practical application of this concept, I am just trying to find out if it is possible.
View 3 Replies
View Related
Dec 9, 2005
Hi,
I have a database with test scores that I am trying to get the most recent date for. Each student can take a test multiple times, but I only need to see the most recent test score. The tables I am using are one called Students which has the following fields, Grade, Student number, Last name, First Name, and Inactive, and another table called scores with the folowing fields,student number, score, test result, test name and test date. When I do a total query it still shows me multiple records for each student. How can I get this to show only the most recent test score for each test name? I am pretty inexperienced with access, and am totally unfamiliar with expresion builder. If anyone has suggestions I would appreciate it.
This is what my data looks like:
Grade Student # Last Name First Name Inactive Score Test Result Test Name Test Date
11 751240 BarretoLuisNo577XSBST Math 2/3/2004
11 751240 BarretoLuisNo611PSBST Math 2/3/2005
11 751240 BarretoLuisNoNTBST Math 2/6/2003
What I would like to have it show is the most recent test date of 2/3/05
Please help ASAP!
View 1 Replies
View Related
Feb 13, 2006
I have the following columns:
Date
Price
Item #
Item description
Weight
Cost
Now, what I need is a report that will give the last price of an item. I import the new pricing on to a table once I receive, but not all the items get new princing all the time. This means I can't query for the pricing of a specific date. I need to figure out a query that will give me the LAST or MOST RECENT price entered. Also I can't just sort it out without going through thousand of records to get what I need, that's what I've been doing so far.
Someone please help!!!!!!!!!!!!!
View 1 Replies
View Related
Aug 17, 2007
Hey,
My tables are structured right now such that; Table 1 has all the main information and uses a primary key. Table 2 is linked to table one without a primary key, for every row in Table 1 it can have several rows in Table 2. In Table 2, i have a date field.
I want to query information such that, it brings up information for the row in table one and only the most recent row in its corresponding table 2. Most recent indicated by date field.
I've tried some things using Max(date) etc, but to no avail.
Many thanks in advance!
View 4 Replies
View Related
Jan 22, 2008
I have these fields
ID,InvoiceID,DateEntered,Description
What I need now is to show the most recent transaction per invoice and what was the description for it.
Doing this
SELECT InvoiceID, Max(DateEntered) AS MaxOfDate
FROM TableName
GROUP BY InvoiceID;
Works Perfect. The problem I have is when I display the Description field. As soon as I do that, I see all invoices and not just the most recent. Anyone know how to write this sql statement that will return the invoiceID, dateentered, and Description only for the most recent invoice in the system?
View 1 Replies
View Related
Nov 6, 2005
Hello,
This is my first post.
I have built an access database (Access 2002) which contains around 370 records.
It would be useful to be able to click a button on a form to review which records (forms) have been amended or what records are new.
This could be presented by either (1) sorting the records so the newest/or latest changed records came first, followed by the next most recent etc etc, or by (2) presenting a list of record names which could be followed manually.
I have tried several text books but have been unable to find a solution so any help from the experts would be very welcome.
Thank you ~ David.
View 3 Replies
View Related
