Most Recent Record First On Form?
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 Replies
ADVERTISEMENT
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
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
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
May 24, 2005
Hi,
This is a toughie (i think so anyway!). I'll attempt to explain!!!
I start with a CONTINUOUS form in my DB that shows all readings for a single given customer's connections. E.g. Customer A has 5 connections and each connection has say 3 readings. Thus this query which uses joins between the Customers, Connections, and Readings tables, would return a list of all readings for each connection, so in the above example: 1*5*3 = 15 records.
Not too tricky so far, but then what I want is for each connection to have just one line showing the latest reading (easily achieved by use of SQL Aggregate Max function on the date field coming from the readings table) TOGETHER WITH the last-but-one (next most recent) reading. So back to the example taking Customer A's connection 1, the row would be as follows:
Cust Conn CurReading PrevReading
A 1 750 500
Where the CurReading value (750) comes from a different record than PrevReading (500).
I've tried all sorts of ways (subqueries etc.) to achieve this without success. The main problem being that any sub query would require parameters from the current record's fields, which seems not to be possible. Can anyone help or is this simply not possible in MSAccess Forms. If it isn't possible anyone have any suggestions as to an alternative way?
Thanks
Darren
View 2 Replies
View Related
Jul 13, 2014
I have a basic database design, well I think so anyway. It only has two tables:
1. tblClientInfo
2. tblNotes
Basically each client has multiple notes/comments that can be linked to its record, hence the tblNotes table. The two tables have a one-to-many relationship, being that each customer can have many notes.
I then have two forms:
1. Claims Loss Form
2. tblNotes_DatasheetSub1
So I can enter multiple notes for each customer. The problem I am having is with the report output. It wants to print every note (record) that is linked to the customer. I just need it to print the most recent note for each customer (It would save a lot of wasted paper).
Example of a print out of what it is doing:
John Doe - 123 Easy St - Notes 1 (Most Recent Note)
John Doe - 123 Easy St - Notes 2 (Previous Note)
John Doe - 123 Easy St - Notes 3 (and so on...)
It is printing duplicates of the same customer by adding the additional notes for that same customer on a new line.
How can I tell it to only print the customer one time in the report, and most importantly, to only use the most recent note that is linked to the customer?
I tried using DMax("NoteDate","tblNotes"), but this only returns one customer with one note. I need it to do that for each and all customers.
I have successfully ran a subquery (two queries with one linked to another) by following detailed instructions from this page I found: [URL] .... It works, but the problem is it only shows the latest date for each note, not the actual contents of the note. I feel so close with this option, but so far at the same time.
The TOP n records per group looks promising that I found here: [URL] ...., but I honestly don't know how to implement it correctly in my SQL. I am very much still learning Access and apparently have stumbled into something that is much more complicated than I had originally imagined. I just assumed I could filter the duplicates out, or tell access to print the last or most recent note record for each customer.
View 2 Replies
View Related
Apr 3, 2014
I have a products form, we are a manufacturing company, with a listbox to show recent inventory transactions. This is based on a query which shows all transactions with the current part id, and that all works well and fine.
The problem is, I would like to limit this query to show only the last 10 transactions in the listbox and not make it editable, ie not enabled. I set the show only in query design view to 10 and it says in the sql statement select top 10, however, the listbox consistently shows all related records. What am I missing?
View 8 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
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 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
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
