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
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?
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
- 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).
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
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.
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.
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).
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
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.
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!
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.)
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.
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..:(
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?
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
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:
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?
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.
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
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.
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.
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?
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.
