Finding The Most Recent Record
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 Replies
ADVERTISEMENT
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 3 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
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
Jun 14, 2006
My form has a button that opens a report. This report is based on a query that sets the report to display ONLY information related to the person in the current query record.
How can I get the report/query to display ONLY the last (most recent) record in the query?
View 2 Replies
View Related
Jan 3, 2005
Hi--I have a subform where a contact's contributions are entered. After much wrangling with the subform's properties, I finally got more than one record to display at a time, but is there a way to get the contributions displayed in descending order (i.e. more recent to less recent) without having to totally redo the subform by basing it on a new underlying query? I tried sorting the contribution date of the underlying bound table that way, but it had no effect.
Thanks for any help.
TDP
View 3 Replies
View Related
Jul 6, 2005
I have a TRANSACTIONS table that adds a new record each time a customer makes a payment. Fields are: our account number, date of payment and amount of payment. Our account number has a relationship with the main CUSTOMERS table which never changes.
I need to query out the most recent payment record for each customer in order to arrive at the most recent date a payment was made.
I've been looking over my books and having a hard time figuring out how to do this. Any help will be appreciated.
Thanks,
Mark A.
View 11 Replies
View Related
Jul 29, 2006
I have a database table comprising inspection records for about 300 process control instruments. A new record is entered for each inspection so that an inspection history builds up for each instrument.
For one report I need to extract all the records with the only the latest inspection date for each instrument.
I attempted to build a query using the design grid screen. Initially I thought I could use the "Max of" approach as I have done with numbers. This did not work so tried without success to convert the dates to serial format thinking I could "Max of".
Have now concluded that a module is needed. However with my Access Basic programming skills this would take me a couple of weeks and still be wrong!
Can anyone point me in the right direction?
View 12 Replies
View Related
Jun 1, 2006
My database includes a Project table and a Status table. They are linked by the ProjectID. The status table contains records sorted by date pertaining to work accomplished on each project. I have created a report based upon a query to give me an update of the status of each project. I only want to see the most recent record for each project from the Status table in my report.
How can I accomplish this?
View 7 Replies
View Related
Jun 2, 2014
I have a table similar to the following:
PatientID | LabID | LabDate | Result
001 | 55 | 01jan14 | 9.5
001 | 55 | 01feb14 | 10.0
001 | 55 | 01mar14 | 8.7
001 | 66 | 30jan14 | 11.2
001 | 66 | 30feb14 | 15.4
001 | 66 | 30mar14 | 13.0
002 | 55 | 01jan14 | 12.1
002 | 55 | 01feb14 | 9.9
002 | 55 | 01mar14 | 14.5
002 | 66 | 30jan14 | 16.5
002 | 66 | 30feb14 | 13.0
002 | 66 | 30mar14 | 10.0
Using a single-step Access query, I need to retrieve, for each PatientID, the most recent LabDate and Result *of a given LabID*. Thus, from the example dataset above, the desired output for LabID 55 is:
PatientID | LabID | LabDate | Result
001 | 55 | 01mar14 | 8.7
002 | 55 | 01mar14 | 14.5
I have searched this forum and others, but have not found an answer that I can directly tanslate to my situation. I have successfully written queries (with included subqueries) that retrieve the most recent of all the Labs, but have failed at obtaining a result dataset that contains only the records within a specified LabID.
For example, the query below fails because whenever the most recent of *all* the LabDates is not the same as the most recent of *the LabDates with a LabID=55*, the correct record is not included in the results. In the example dataset above, 0 records are returned.
SELECT a.PatientID, a.LabID, a.LabDate, a.Result
FROM Labs AS a
INNER JOIN (SELECT PatientID, MAX(LabDate) AS MaxLabDate FROM Labs GROUP BY PatientID) AS b
ON (a.PatientID = b.PatientID) AND (a.LabDate = b.MaxLabDate)
WHERE (((a.LabID)=55));
View 3 Replies
View Related
Aug 18, 2014
I have two tables with a one to many relationship. The tables are linked by the INDEX column.
EXAMPLE:
Code:
TABLE_1
INDEX NAME
1 Name_A
2 Name_B
3 Name_C
TABLE 2
INDEX NUM_INDEX STATUS
1 1 REJECTED
1 2 REJECTED
1 3 OPEN
2 1 CLOSED
3 1 REJECTED
3 2 OPEN
I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).
Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN
SQL that I have now.
Code:
SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));
This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.
View 2 Replies
View Related
May 7, 2014
I have a table of accounts and a table of rates. There is a one-to-many relationship between them (i.e. each account can have multiple rates, each with their own - unique - effective date)
I'm trying to build a query which will show me all of the accounts in the accounts table and the most recent rate (based on the effective date) for each of those accounts.
This is as far as I've gotten with the SQL :
Code:
SELECT [tblAccounts].[AccountID], [tblAccounts].[AccountNumber], [tblAccounts].[AccountName], [LatestRate].[IntRate], [LatestRate].[EffectiveDate]
FROM [tblAccounts]
LEFT JOIN
(SELECT TOP 1 [tblRates].[AccountID], [tblRates].[IntRate], [tblRates].[EffectiveDate]
FROM [tblRates]
ORDER BY [tblRates].[EffectiveDate] DESC) AS LatestRate
ON [tblAccounts].[AccountID] = [LatestRate].[AccountID]
But this can't work because the [LatestRate] subquery can only ever return one record (i.e. the most recent rate across all of the accounts)
I need the most recent rate for each of the accounts in the main query
(FYI - I use an outer join as it is possible for no rate to be available in the rates table for a given account, in which case I want to return the null value rather than omit the account from the resulting dataset...)
View 2 Replies
View Related
Jun 20, 2013
I have a database that is used to allocate appointments to our staff. It has 2 tables, one that lists the clients we need to call in that day, and another that stores details of each contact attempt. I'd like to design a query that find all clients who we have not dealt with so we can easily get their details in a list. I know what the criteria for the query would be, but I'm stuck for how to actually execute it. Here are the details.
Table tClients stores the current clients - primary key is named "clientRef"
Table tContactEvents stores each contact attempt and the date/time is stored in a field named "dateTime".
When an entry has been dealt with successfully a yes/no field named "completed" will be set to "Yes".
There may be many attempts to contact a specific client on a given day, unsuccessful attempts will not have the completed flag set.
Once the completed flag is set that client will be ignored so no further entries will appear.
So I need a query that searches tContactEvents for the most recent match to each number in tClients.clientRef and checks if the completed flag is set. If the completed flag is false, or if the number has no match (i.e. no contact attempts made yet) then the clientRef should be displayed. I also need this to be restricted to the current date, as the same client could have rebooked their appointment to a different day.
View 10 Replies
View Related
Feb 24, 2005
Hi
I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!
Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?
Any help would be hugely appreciated :)
View 2 Replies
View Related
Feb 5, 2007
Hi guys, need a bit of help with my Database. I have a Table with has ever player to play for my football club (1700 records in total), now i have another table called games (which has a record for every season played for each player, and is linked to the first table using the relationships and a field called ID. Make sense so far?
Well i then use a query to combine and calculate totals of certain fields and then i can use the query to show every single statistic possible on ASP pages. But when viewing my query in Access only 1699 records show up. So my thinkin is that either two records are identical (which is impossible because every p;layer has an ID and they cant be duplicates) OR a player has ZERO games in all years (in other words the player has no records related to it in the second table called games).
My question is, instead of searching 1700 records manually to find which one is there any way i can find which player would have NO records from the related table joint to it?
View 2 Replies
View Related
Nov 27, 2006
Hi ,
I have developed a very basic contact DB to record enquiries coming into my business. The last field on the DB is for comments, so that anyone entering the DB can see the latest conversation we have had with a particular customer. What I want to do is create a means whereby I can find a customer within the DB and then be able to add the contents of our last conversation into the Comments field. Would there be any way of doing this via a switchboard???
I am a very inexperienced Access user so the simpler the answer the better. Many thanks in anticipation
Keith:(
View 2 Replies
View Related
Aug 17, 2007
Hello,
I have looked for ages for a solution to this and have not yet found one to suit me so here I am.
I am creating an Access 97 db in which I have a form (section1) which shows its records in datasheet format(results from a query). On this form is a button which opens another form (fileViewNewSec1) where the user can input the data for a new record. In the closeEvent() part of this form I have put:
=[Forms]![section1].[Requery]
This successfully refreshes the form and the new record can be found. However, there are many records in this database and it would be nice if once the form (section1) has been refreshed that it would focus on the newly created record. I am not sure if anyone can help me with this.
The records are sorted by "fileRef" and therefore the newly created record is not placed at the bottom of the records. They do however have an auto number primary key, I am not sure this will be useful but they you go.
Cheers
MArk
View 3 Replies
View Related
Aug 13, 2005
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name, description, type and date.
Thanks
J
View 6 Replies
View Related
Aug 19, 2005
--------------------------------------------------------------------------------
How do I set up a search on my form where 4 fields must match exactly to return the correct record? I must match - persons name (text), description (text), type (text) and date (date). I would always search on all four fields.
Thanks
J
View 2 Replies
View Related
Jul 18, 2006
Hi All - I'm developing a database that requires records to be entered and updated. When updating records, the user must select an item from switchboard (Update a Record) which takes them to a screen/form where the user can enter the record number. When the record number is entered and the user clicks on the "OK Button" I need one of two actions to occur ... (1) the user is taken to the record for updating or (2) the application returns a message "Record Not Found".
Can anyone tell me how I can code this?
At this time I get a message that my form name is not recognized within the expression I've "built" in the macro.
If anyone can help me it would be greatly appreciated before I go bald from pulling my hair out!!
Thanks in advance ... Spicoli
View 1 Replies
View Related
Jul 18, 2012
Product Name
Start Weight
End Weight
Product 1
379000
389000
Product 2
288435
226710
Product 3
699000
318882
Product 4
36800
50000
Product 5
89180
117280
Product 6
132000
132000
Product 7
18
18
Product 1
Product 2
Product 3
Product 4
Product 5
Product 6
Product 7
View 3 Replies
View Related
Sep 5, 2005
Hi everybody,
My question may sound you stupid but i am a newbie and i think you can help me...
My problem is,
I have records that have the id of a stuff and the error codes of the stuff which are occured in production. Therefore not all of the fields in the record are non-zero. I want to find the non-zero field/fields in a record...
Any help will be greatly appreciated...
Thanks...
View 1 Replies
View Related
Jan 4, 2006
I have a form in which old customers can be edited and new customers can be added.
Currently, to edit a customer, the user clicks in the "Last Name" field and then selects EDIT-FIND and types in the last name. The user will then cycle through all customers with that name to select the correct one.
How do I allow a user to begin typing in the last name field and have the name appear? Is there an easier way to do this?
Many thanks.
View 2 Replies
View Related
Sep 12, 2006
Hello,
I have tried everywhere for a solution to this:mad: Any help is much appreciated.
I have a main form with a subform. The subform shows related records using a match criteria. Each record that shows in the subform also has a unique ID. This unique ID is also shown on the main form record (although not used to link the main and sub forms).
I would like to be able to click on a command button on each of the records in the subform and goto the Main form record. I can't find any threads that deal with this though - sorry!
Hope it makes sense
DJ
View 1 Replies
View Related
Oct 5, 2004
I have created a database with a switchboard and a form ("RMA"). Currently, on my "RMA" form I have a command button that allows the user to find a specific record by entering an RMA number into an input box. My code is as follows:
Private Sub cmdFindRMA_Click()
Dim myFilter as String
If vFindRMA = "" Then 'vFindRMA is a global variable, string.
vFindRMA = InputBox ("Enter the RMA Number to find:", "Find RMA", "", 5000, 3000)
myFilter = "[RMA_Number]='" & vFindRMA & "'"
Me.Filter = myFilter
If Me.Filter = "" Then
Me.FilterOn = False
Else
Me.FilterOn = True
End If
End If
vFindRMA = ""
End Sub
I want to be able to remove this command button from my RMA form and add it to my Switchboard. My problem is, I don't know what code I need to use in order for it to work from the switchboard. Can anyone help?
S.Pommier
View 1 Replies
View Related
Feb 22, 2005
Hello all, I have a couple questions. First I am trying to create a select query that will show the results that I have two fields , one is "Date" & the other is "Current" For the date I am using the a between statement "Between [start] and [end]. Under the "Current" I have "Applied" & below that I have "Overage" The problem is when I enter my date range, it shows all records with the Overage or Applied, but if I go & remove either "Overage" or Applied, then it works fine.
Is there somthing that I am missing
Second question is when using a find command box, is there a way to have it search multiple tables, if so how would I set the find box to search both tables, if the table names were called Let say Table 1 & table 2
Thanks!!!
View 1 Replies
View Related
