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!
I have a date field in one of my queries (01DEC2004). I have a pivot table linked to the query. I would like the users of the pivot table to be able to group the data by month. Currently the group feature will not work. Does anyone have a suggestion on the best way to extract the month from the date field?
Could I create a new column in the query called month and use an expression to extract the month? If so can someone tell me how to key the expression to do this?
basically I have two date columns in one of my tables. One is created whenever a record is created in that table the other is added to that record later based on business rules. I need to get all records in the table where the first date exists but not the second. I'm unsure of how to write a query specifying that the second date column must be empty.
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'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"?
In my database I have records that are exactly the same except for some dates. My fields consists of the following: “Model Number”, “Serial Number”, “Last Cal Date”, “Cal Due Date” “Days Due” and “Interval”. The Cal Due Date and Days Due are calculated fields in my query. Each time a device has been recalibrated it is logged into the database. Example my torque screwdrivers are to be calibrated every three months. Once the screwdriver has been recalibrated it is logged into the database. The model number stays the same, the serial number stays the same, a new “Last Cal Date” is entered. I select the interval and the “Cal Due Date” and “Days Due” are calculated. Every time calibration is due I repeat this process. So in 1 year I will have 4 records showing that serial number XXXX was calibrated and when. This process is repeated for every piece of test equipment that requires calibration. How can I query to give me the most recent records for the entire database for each unique serial number.
I'm replying because I am having the same sort of problem with my current database. I do have the separate tables like don has described. Basically here is what I have:
Employee table: Employee number (primary key) Other fields for employee information
Training table: (A different table for each training that I am tracking) ID (autonumber - primary key) Employee number Training Date
There is a new entry on the training table each time an employee completes a training. I then have a query that links the 2 tables (using a join between employee number) and calculates next training due dates and filters to overdue training and trainings that are due in the next 30 days. Currently, the query is not pulling in the most recent training dates for each employee. What do I need to do to the query design so that it will only pull in the most recent training date for each employee? Any help would be greatly appreciated.
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 have two tables: tblA with PersonID field; tblB with PersonID field and AppointmentDates field. Tables are joined on PersonID field. How to run a query which would return all records from tblA and only the most recent date from AppointmentsDates field from tableB?
Do I use GroupBy somewhere or Top 1 to acomplish this? Or maybe I should use a sub-query?
First I just want to say thanks to everyone because you guys are great and have helped me with more than one database. Now for my problem...
I've been searching the forum and reading a lot of the posts about how get the most recent date from a query using Max. My problem is that I don't understand SQL. :confused: So, the stack of papers that I printed out to help me aren't really helping at all. ^_^
I know that I don't necessarily need to understand SQL if I know what to put into it -- so please help!
I need to pull the most recent date from a list of court dates that are updated on a subform of a subform. This way I can print a list of names to watch for in a given week.
The tables involved go as follows:
BaseTable CrtTable CrtSubTab
BaseTable is linked 1:Many via [ID#] to CrtTable CrtTable is linked 1:Many via [Court ID#] to CrtSubTab
I need to find out the most recent court date [Pending Court Date] from the CrtSubTab for each [ID#] depending on the quarter (which is a field [Quarter] in the CrtTable). I will set [Quarter] criteria to pull from a choice selected on a seperate form prior to running the query. In addition, I'd like to have the dates that are pulled fall within in a specific date set, which I can also input on the same form that restricts the quarter. This I know is a simple Iif statement using < & >. But - will that impact my ability to use Max through SQL? I have seen several posts referencing the need to use two queries.
Hello 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?
I 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?)
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:
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?
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.
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 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.
I 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.
The 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.
Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:
