Queries :: Select Query - Most Recent Date
Dec 24, 2013How would you write a Select Query to select the most recent dates?
Select OrderDates
From Orders
Where >=Date()
No good if recent date is older than todays date!
ADVERTISEMENT
Queries :: Most Recent Data From A Date
Jul 30, 2014I have a problem here that I've been wrestling with for a while.I have a table, ID, Date, and Grade.It is set up that way, and I tried
Code:
SELECT tblGrades.StudentID, tblGrade.Grade, Max(tblGrade.GradeDay)
FROM tblGrades
GROUP BY tbGrades.StudentID, tblGrades.Grade;
I figured since I'm grouping by both StudentID and Grade, it wont return the most recent grade from that Student. Is there any way for me to NOT group by Grade and just display the results of the Max(GradeDay?)
Queries :: Show Only Most Recent Date For Each Customer
Nov 4, 2014I have a query that selects a group of customers and order dates.
I want the query to only show the most recent date for each customer.
How would i achieve this?
Queries :: How To Get A List Of ALL Equipment Showing Most Recent Date And Time
Mar 23, 2013I've got three tables:
Code:
tblequipment
equipmentid
equipmentnumber (user defined ID)
tblrentals
rentalid
rentaldate
rentaltime
fromparty (c for customer, e for employee, o for other)
frompartyid (foriegn key to either customer, employee or other)
toparty (same as from)
topartyid (same as from)
tblrentaldetails
detailid
rentalid
equipmentid
How can I get a list of ALL equipment showing the most recent date and time, also showing the respective toparty and topartyid? I can get it fairly easily, except for including toparty and topartyid.
Queries :: Average Price Of Last 5 Line By Items By Recent Date
Mar 4, 2015Any way to filter the average price of of a Product within the last 5 occurences (Line Items). It would pull a week back so WHERE: Between Now()-7 and Now(). Example:
Code:
PARTID | Price | Date
--------------+-------------+---------
111223344 | 5 | 3/1/2015
111223344 | 7 | 3/2/2015
111223344 | 8 | 3/4/2015
111223344 | 10 | 11/22/2014
111223344 | 20 | 10/1/2014
111223355 | 5 | 2/5/2015
111223355 | 6 | 2/1/2015
to:
What I want:
Code:
PARTID | avgPrice | MinDate
--------------+----------------+-------------
111223344 | 10 | 10/1/2014
111223355 | 5.5 | 2/1/2015
Most Recent Date Query?
Oct 6, 2005I 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.
Most Recent Date Query
Dec 9, 2005Hi,
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!
How Can A Query For The Most Recent Date?
Feb 13, 2006I 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!!!!!!!!!!!!!
Most Recent Date Query
Aug 17, 2007Hey,
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!
Need Query Help For Most Recent Date For ID
Sep 14, 2006I've done querying before, but none as complex as this. What I'm trying to do is query a table for the most recent date (field labeled "Sample Date") for each ID (field lableled "Sample ID"). It is also giving me other output as a sum of my "Results" field. So, in essence, what I want is the total number (sum) of each "Sample ID" for that "Sample ID"'s most recent "Sample Date".
I have a query set up right now where its showing me the sum of "Results" for each "Sample ID", but with multiple "Sample Date"s and "Sample ID"s. I have the "Sample Date" field in descending order, but how do I get it to show only the most recent "Sample Date" for each "Sample ID"?
Queries :: Appointment Activity - Select Query For MAX Date
Jul 10, 2014I need to find the frequency of client's appointment activity after their last appointment in a 4 month window.
My question is:
(1) Is my SQL going to give me the last time a client was in the office within the 4 month window?
(2) What will my SQL need to like to count frequency of appointments before and after window for each client?
(3) Can I do (2) in the same SQL shown above or will I need to make a new SQL statement?
Code:
SELECT tblTest.Client, Last(tblTest.AppointmentDate) AS LastOfAppointmentDate INTO tblTest
FROM tblTest
GROUP BY tblTest.Client
HAVING (((Last(tblTest.AppointmentDate))<#4/30/2014#))
ORDER BY Last(tblTest.AppointmentDate);
Details
Date Window I need to select the clients last appointment: Jan 1, 2014 - April 30, 201
Once the last appointment has been determined for each client in that window I need to count the frequency of appointments before and after that date.
What Expression Do I Need For Most Recent Date In A Query?
Mar 11, 2006Hello people - I am building an APPEND and DELETE Query which deletes STUDENTS in my Camera Loans database who have not borrowed a camera in more than two years.
In the query structure, under DATE_BORROWED, I put the expression <=Date()-730 (where 730 = 365 days x 2.)
However, upon running the query it gave back to me the earliest loan date of the student, (so their first loan date and details basically)
It ignores the more recent loans, so deleting this appended record would be a mistake.
Now I need an expression in a duplicated DATE_BORROWED field in the query structure that picks out the most RECENT date out of them all, the other field running the check on this most recent date to see if it is older than two years.
Surely there is some code or expression that pulls the most recent date out only of a whole series in a record? Or a criteria is performed on the rmost ecent loan only?
Help appreciated, thanks.
Neil.
Queries :: Query To Select The Date From Two Tables On The Criteria Basis?
May 21, 2013get the data from two tables on the basis of criteria...
I want to select the whole table1 which has 6 fields including Emp_id...
I want to select the single field from table2. field name is "Username" from second table2. will select the username on the basis of Emp_ID becaue both tables has same emp_ID.
It should be in order like. Emp id, Username, Startdate, Enddate, Hours, trainingNames.....
Query: Most Recent Date From Several Fields In Two Tables
Jul 15, 2005I have a parent table [Case] and a child table [Action], in a one-to-many [Access 2003].
The parent has an open date and a closed date.
The child has a received date and a completed date.
The child may contain more than 1 record that matches the parent.
Date fields for activities that have not yet happened are blank.
A typical example might be one parent and two child entries for a total of six dates fields.
I am after only a [single] most-recent action date of the six [there could be a tie for that most recent date, and then there would be two records returned in the result].
I am working for a table that includes a list of every parent record with the name and date of the most recent, or latest activity date.
After two days I decided to ask for help.
Thanks,
Query That Grabs Most Recent Date, And Associated Fields
Dec 10, 2007The database I am working with contains several different tables and a variety of information. I need to create a query that accesses information from two different tables. Though the tables contain other fields, the fields I am most interested in are:
Table 1: Protocol Information
"Protocol ID", "Title", "Expiration Date"
Table 2: Regulatory Submission
"Protocol ID", "Description of Submission", "Submitted By", "Date of Submission".
The way the data is stored, each protocol has a Protocol ID, and there is only one record per protocol in the "Protocol Information" table.
However, each time these protocols are submitted to our review board, a log is created in the "Regulatory Submissions" table.
I need to generate a query that pulls only the most recent submission date, so that the query output would look like this:
"Protocol ID", "Title", "Description of Submission", "Submitted By", "Date of Submission" (Where this is the most recent one), "Expiration Date"
From other forums and posts, I tried to create an SQL aggregate function, using Max or Last to get the most recent date. I was successful, but only if the query contained just the "Protocol ID", "Date Submitted", and "Description of Submission" fields. Adding any more, or creating a query to use the outputted data didn't work (it could be that my second query was not written correctly.) I am very novice at creating complex queries, and at SQL.
One additional complication: There are two values in the "Description of Submission" field of the "Regulatory Submissions" table that I am not interested in. They are "Adverse event" and "AE Summary Log". I can successfully filter them out using a criteria expression, but integrating all of these pieces has not been easy (or possible, yet).
I'm out of ideas. This is really complicated, and I apologize. Any assistance would be greatly appreciated.
Need Query For Max Date To Find Most Recent Letter Sent Using Distinct Addresses
Feb 21, 2014I'm trying to create a report for how many "nasty grams" (rejection notices) my company has sent to people who keep sending in paper forms when they are supposed to file electronically.Every letter that goes out has information recorded based on whatever they sent to us - so the only remotely reliable way to count how many each person received is by the address on the envelope (people use different names, different business names, use different telephone numbers on the forms, etc).
I just built several queries that feed into a report that gets sent to my boss on a monthly basis to show the people who've sent in more than one paper form and have received our rejection notices more than once.I'm not the greatest at SQL, but I've been trying to find a way to use DISTINCT Addresses, leave all other fields the same (not DISTINCT), to:
1. Only return people who have received 2 or more letters
2. If at least one of the letters was sent more than 90 days ago
AND If at least one of the letters was within the last 90 days
-If at least one was within the last 90 days, only display the most recent send date of the letter (lots of people get back-to-back letters).
3. Display their names, addresses, telephone numbers, the date of the most recent letter sent, count of the total letters ever sent to that person. (the report will already do this, just need Max date)
My first query counts the number of times each address appears in the main table and simply only has [Address] and [CountofAddress]
My second query has the [Name], the [LetterDated] >=Now()-90, and the qryCountofAddress is linked to the main table by [Address], using [Countof Address] >=2...I have tried Selecting Unique Values in the Properties tab. Yes, I have tried INNER JOIN (but can't get the rest of my fields to display once I make addresses distinct).
Filter Query - Report To Only Show Records With Most Recent Date?
Feb 26, 2014I am having trouble getting a query or report to show only the most recent data.
We have salesmen that use a handheld data collector scanners to count inventory in stores. The scanner data is imported to a Access table. Each record line is one scanned item. I have a query with totals that counts the records and gives me a total count of each item at the store on that date.
I then need to filter the data to only show the most recent date. Using Max Date I get the most recent date but the count fields are showing totals for all dates. I am also getting the unique item from the earlier date in this query which I do not want.
Here is my data table: Inventory Scans from stores.
Scan Date
Item Scanned
location
1/1/2014
item123
Store ABC
1/1/2014
item123
Store ABC
......
Here is my Query with Totals that counts the item records:
Scan Date
Item Scanned
location
(Item Scanned) count
1/1/2014
item123
Store ABC
2
1/1/2014
item 456
Store ABC
3
1/1/2014
item 789
Store ABC
1
2/1/2014
item123
Store ABC
2
2/1/2014
item 456
Store ABC
1
This is what I am trying to get - only the most recent date of counted items:
Scan Date
Item Scanned
location
(Item Scanned) count
2/1/2014
item123
Store ABC
2
2/1/2014
item 456
Store ABC
1
Queries :: JOIN Query To Return Most Recent Record?
Aug 18, 2014I 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.
Select X Number Of Most Recent Records
Apr 1, 2014I have been tasked to determine the actual cost to build a product by summing the cost of the most recent required components to come off of our assembly line. For example, say I'm trying to determine the cost of a car with multiple sub-components (1 engine block, 4 doors, 4 wheels, 2 headlights, etc.). We make parts for all kinds of products (cars, boats, televisions, etc.). All of the data for ALL of these parts are contained in a table called PART_DATA (i.e. part ID, date started, date finished, hours spent, etc.).
Then I have a list of parts required to build a certain model car in a table called CAR_PARTS_LIST. This table contains data like (part ID, quantity needed, etc.).
If I want to know how much it would cost to build a car using the most recent car-part data, I need to pull data for the most recent required car parts to come off the assembly line that are required for a single car. That is, my car needs 4 wheels, so I need data for the most recent 4 wheels to come off the line.
How can I write a query for the "4 most recent wheel records" given that "4" is specified in the CAR_PARTS_LIST?
Queries :: Subquery To Return Latest / Most Recent Value For Each Record In Main Query
May 7, 2014I 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...)
Queries :: Appointment Database - Query Most Recent Entry For Each Record In Separate Table
Jun 20, 2013I 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.
Queries :: Select Query To Gather Results Of Other Select Queries
May 11, 2014I'm fairly new to Access. 's various select queries containing useful and useless results. I want to create a select query that will pick out all the useful figures into a 1 row table that can then be pasted into Excel.
e.g Existing Select Query 1 returns 1 row showing Average Age, Average Price, Total rainfall
Existing Select Query 2 returns 1 row showing Average Weight, Average Salary, Total snowfall
Existing Select Query 3 returns *2* rows: It returns Distance from London, Hours daylight and population for Town A and Town B
I want a select query that returns 1 row showing (6 items):
Total rainfall, Total snowfall, Town A Distance from London, Town A Population, Town B Distance from London, Town B Population.
I've been able to handle getting Total rainfall and Total snowfall. But I cant figure out how to get Town A Distance from London, Town A Population, Town B Distance from London, Town B Population to appear in the same row of the same query results as Total rainfall, Total snowfall.
Queries :: How To Select Records When Date Is Less Than 30 Days Apart
Mar 10, 2014I have a large database filled with customer records. Some customers come one time. Other customers come 50 times at year. I want to find all customers that have records that are less than 30 days apart so I can ultimately see the types of products they buy. How do I write the query?
I attached a picture of a sample database. The 30 day (+/-) field doesn't exist but I would like the query results to build it.
Queries :: Using A Form To Select Date Range
Jul 1, 2014I'm using a form to select a date range. Using the following, and entering start date of 6/1/14 and end date of 7/1/14 I would expect to pull the records with a date of 7/1/14; however it doesn't. I have to enter and end date of 7/2/14 to pull 7/1/14 records.
>=[Forms]![F_Transaction_Date_Range]![txtStartDate] And <=[Forms]![F_Transaction_Date_Range]![txtEndDate]
Queries :: Select Last Input From Several Date Columns
Oct 2, 2013Having built a database that tracks projects across their lives, I've now been asked it I can identify the latest status of the project in a "quick view"
The table "All Projects" has columns that list the Project Number; Project Title; Project Manager and further colunms that list dates that the various stages of the project were achieved. i.e. Start Date; Date Milestone 1 Completed; Date Milestone 2 completed etc.
The projects are such that Milestone 5 may be completed before Milestone 2 and so on, so I need to be able to identify which Milestone was the last to be completed.
I thought of trying an IIF but the progress across the Milestones isn't logical so it won't work. I found something on the Internet about "Greatest" being used on some other program (not Access), which Access didn't like at all!
If the requirement had been given before starting building the db, I would have done everything differently, but I have no real desire to go back to stage 1 and start again, so how or if it's possible to find the highest / latest date relevant to the Project Number across the row of data ?
Return Most Recent Date
Jul 22, 2005Using 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?