Selecting Based On A Text Field And Most Recent Date
Jul 16, 2007
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:
I'm working on a table which has a country field, but this field may contain a text string consisting of more than one country, eg "France, Belgium, Spain"
I want to run a query against the table to select records for any one country, but not sure how to do this.
I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.
For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.
Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.
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 would like the "DateOfConfirmation" to populate with today's date when "SSurvDiagThisYear" is Confirmed.
The DateRecordCreated is a simple =Date() that populates when the record is entered into the table.
SSurvDiagThis Year is the case outcome - Pending, Probable, or Confirmed. It is possible that the case could be confirmed on the same day it was entered into the table but that is RARE.
I am hoping for the The DateOfConfirmation to capture the date the case is confirmed so that I can gather some duration between the case being opened to confirmed.
I am using Access 2007 and I have had a curious issue arise.
I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)
When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)
The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.
I have a form with bound textboxes used for entering currency values. When the form loads the fields are automatically populated with default values. When the user clicks on one of the fields, I would like to automatically select all of the data in the field, so as new values are entered, the defualt value is overwritten. I imagine that I should use the OnFocus property, but is there a command to highlight the data?
I have a field on frmOutpatient which is called EpisodeID. I need to populate this field from a selection in a pop-up form.
The pop-up form (frmSelect) is a continuous form with a number of different EpisodeID numbers according to the date the patient was admitted. At the end of each record I want to put a command button (cmdSelect) and when pushed I want this SelectEpisodeID to be transferred to the field on the frmOutpatient.
I am building an Access database for a client. It is an employee staffing database. With that being said they would like the ability to automatically populate the "T2PPCD" date field based on what is entered into the "Report Date" field. (Same table)
The date is the Monday after 180 days from the report date.
I already know how to get it to auto fill 180 days from the "Report Date" but I'm not sure how to tell it to give me the Monday after 180 days.
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:
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));
Code: tblPrice PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 06.01.2014 Firma A 6 6 02.01.2014 Firma XY 11 7 03.01.2014 Firma XY 9 8 04.01.2014 Firma XY 7 9 05.01.2014 Firma XY 8 10 06.01.2014 Firma XY 10
And I have a table with the dates, for which I need a price.
Code: tblDates PosNr PriceDate Company 1 01.01.2014 Firma A 2 02.01.2014 Firma A 3 03.01.2014 Firma A 4 04.01.2014 Firma A 5 05.01.2014 Firma A (no price available) 6 06.01.2014 Firma A 7 02.01.2014 Firma XY 8 03.01.2014 Firma XY 9 04.01.2014 Firma XY 10 05.01.2014 Firma XY 11 06.01.2014 Firma XY
And now I want to combine this tables, and for the dates which have no price, the last price should be taken.
Code: tblResult PosNr PriceDate Company Price 1 01.01.2014 Firma A 5 2 02.01.2014 Firma A 7 3 03.01.2014 Firma A 9 4 04.01.2014 Firma A 8 5 05.01.2014 Firma A 8 (actualy no priceavailable, so take last price) 6 06.01.2014 Firma A 6 7 02.01.2014 Firma XY 11 8 03.01.2014 Firma XY 9 9 04.01.2014 Firma XY 7 10 05.01.2014 Firma XY 8 11 06.01.2014 Firma XY 10
how I can get this?
I have this code, but it need hours.
Code: SELECT tblDates.PosNr, tblDates.Company, tblDates.PriceDate, (SELECT TOP 1 B.Price FROM tblPrices As B WHERE B.Company = tblDates.Company AND B.PriceDate <= tblDates.PriceDate ORDER BY B.PriceDates DESC ) AS Price FROM tblPrices RIGHT JOIN tblDates ON (tblPrices.PriceDate = tblDates.PriceDates) AND (tblPrices.Company = tblDates.Company);
Is there a way to show the earliest and latest dates of a report generated by a non-date field?
E.g. I generate a report based on Food, and it'll list the days that this food is associated with. Is there a way to show the first and last day that appears in this report (i.e. the range of dates that the report shows based on the food selected)
I need help with an update query that will change records for me based on the date
In the record I have a TEXT record called [GI1] that can have a multitude of data in it. The data can be “EVA”, “CON 10/12/06”, “REQ 10/12/06”, “PAS 10/12/06”, or “B 10/12/06”, and finally “A 10/12/06”
What I need is a query will look at the Data that starts with “B” and change the letter to “A” if the date in the record has passed. {The Date can be any date in any year }
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
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 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 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.
