Queries :: Return All Records With Last / Max Date
Apr 10, 2013
My issue surrounds retrieving the last (based on most recent date) set of records based on the most recent date. I have query, containing 2 tables as the sources for the query results. Currently, the query yields:
Field A Field B Field C
123456 AAAA 1/8/13
123456 BBBBI 1/8/13
123456 CCCC 1/8/13
123456 DDDD 1/8/13
123456 EEEEEE 3/10/13
123456 FFFFFF 3/10/13
123456 GGGG 3/10/13
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
The desired results would be to return all records with the last/max date, so yield:
123456 HHHH 3/28/13
123456 IIII 3/28/13
123456 JJJJ 3/28/13
I have tried the max & last functions, yet no success.
View Replies
ADVERTISEMENT
Aug 19, 2015
I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.
I have tried
Code:
Between [StartDate:] And [EndDate:]
And
Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"
Neither of which work ....
View 13 Replies
View Related
Apr 24, 2013
I have a table which includes a start date field and completion date field for housebuilding.
I am trying to extract all records that have either a started date or a completed date between 2 dates supplied by the user. I have tried to use Between on both fields but that doesn't return results between the fields.
It workd if I just do it on EITHER the start date field OR the completion date field so that implies to me that I need to break it into 2 queries, one returning start date recrods and the other returning completion date records but then I would need to have somthing that removes records that appear in both the start date and the completion date results.
View 7 Replies
View Related
Aug 8, 2013
I have a query where I display the [OPEN DATE] and [CLOSE DATE] of my cases. However, when I run this query sometimes the cases are not closed yet, therefore there are null values. However, I also have a field to calculate the datediff between these two dates. I need the [CLOSE DATE] field to display today's date when it is a null value so that I can still get a count of the days using datediff when I run the query.
View 1 Replies
View Related
Jul 24, 2013
When I add a record to table A (using a form) the first field I enter is a date. That date will then determine which records I see in my drop down list from table B (via a query).
Some records in table B have EffectiveFrom and EffectiveTo dates recorded - because they are now obsolete, other records have no dates recorded in these fields because they active.
How can I view and then select from the drop down only those records in table B which are effective/valid to the date entered in table A?
View 1 Replies
View Related
Oct 15, 2013
First, I am trying to get a query to return records between a certain date range. In the form I have DateFrom and DateTo unbound text boxes from which faculty select the date range. I know I have done this before, but I cant figure out how to create a field name and write the criteria for the source query in design mode!
I tried [Form]![FormName]![DateFrom]<[Form]![FormName]![DateTo] in the criteria but I can't seem to write a valid field name that doesn't alter the criteria in some way...
Second, in the same form, faculty enter the StudentID for the student they wish to get records for. How do I write the code to show a msgbox when there is no such ID in the event they enter an StudentID incorrectly.
View 1 Replies
View Related
Jan 31, 2006
Hi All,
I am wandering if it possible to check programatically whether a query returns a NULL result?
I am writing a program(VB) that involves an IFF statement where if there are records in the query it does a specific calculation & if there are no records returned in the query it does something else. Is this possible to achieve?
Thanks in advance:D
Cheers,
Karv
View 2 Replies
View Related
Feb 20, 2014
I have a list of dates on which tasks were performed.
I want to be able to see if there is a pattern, i.e. lots done on a Monday
Can I extract the Day from the date, and return a value Monday, Tuesday, Wednesday etc??
View 3 Replies
View Related
Jul 28, 2014
How do you return the most recent date of multiple columns.
I have a table (tbl_courses) that has a list of training courses. We want to know when a client completed the course most recently.
The problem is, for one course there has been up to 4/5 different variations of the course with different names over the years. E.g. "Drug awareness" has also been known as "Drug Aware" "Illegal Substances" and "Stoppers". I want to pull through the most recent date for all of the above.
We have a field in the Courses table that links the courses into groups (e.g. All drug aware courses come under "23"). Not sure if that works?
Is there a way to do this? The Tbl_Courses is linked to Tbl_Clients via a ClientID.
I've managed to do it in SQL using GREATEST() but that isn't an option in Access.
View 6 Replies
View Related
Mar 22, 2015
Ok, so I'm sure this is a pretty simple criteria, but I just can't seem to get the syntax right.
I'm trying to allow my user to select any start date and the query will return that date plus the next three days.
View 7 Replies
View Related
Jul 30, 2015
Basically, i have a table ("Transaction") with payment date and another table ("Control") with accounting dates and corresponding year/month.
Objective: I need to know which accounting year or month these payment date fall under.
Example: If the payment date is 18 Dec 2013, the accounting year should read as 2013 and the accounting month should read as 12.
In excel, this is very simple using vlookup.
I tried for hours using access dlookup query and i'm still stucked ..
View 4 Replies
View Related
Apr 15, 2015
I am looking to return one row from groups of the same EpisodeID whereby the row with the minimum date is selected each time. This includes returning all other fields in the row such as EventID below and ideally others as well if that will be possible.
To illustrate I include the following. What Access 2003 query would I need to return all the rows with the earliest dates? EventID will be unique in the intial table.
Code:
EventsTable
```````````
EpisodeID | EventID | EventDate
-------------+---------------+-------------
1 | 001 | 01/02/2010
1 | 023 | 05/10/2009
1 | 103 | 12/02/2010
2 | 004 | 02/03/2013
2 | 102 | 12/10/2014
3 | 546 | 04/05/2012
3 | 100 | 08/08/2013
3 | 034 | 10/10/2012
3 | 066 | 02/03/2013
4 | 777 | 05/07/2014
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
Code:
ExpectedOutput
``````````````
EpisodeID | EventID | EventDate
-------------+-------------------+---------------
1 | 023 | 05/10/2009
2 | 004 | 02/03/2013
3 | 546 | 04/05/2012
4 | 233 | 01/11/2012
5 | 087 | 10/03/2011
View 13 Replies
View Related
Feb 3, 2014
I'm trying to create a query to show me records for a given year. The issue I'm having is that each record has (4) dates fields and each record can contain null values.Is it possible to do this in a query with data like the example below?
Field 1 ID (1), Field 2 Date (12/22/2012), Field 3 Date (2/06/2013), Field 3 Date (Null), Field 4 Date (Null)
In this example I would want 2013 data but would need to be able to search any year. I could also have dates in all (4) fields
View 3 Replies
View Related
Mar 3, 2015
query to return the most recently created records in a composite key field, and only the most recent ones.
there is a screenshot of the composite key field attached
I want to get te records with the highest InvoiceID, there can be multiple records with the same InvoiceID and I want to get all of them.
Using the screenshot as an example I need to query to return the last two records as they both have the Same, highest InvoiceID.
View 6 Replies
View Related
Apr 4, 2015
I've got a database of club members with names, addresses, contact info etc.
I need to send letters by post to those members that I don't have email addresses for. How to create a query to return a list of people whom I don't have email addresses for.
I know how to mail merge the info with the letter I've written in word, how to group people who's email address cell is empty.
View 1 Replies
View Related
Feb 14, 2014
I understand that a query can accept wildcards in the search criteria but this is different.
I have a form on which a user selects a [product] part number in an unbound combo e.g. "12345-V111".
I then need a subform on the form to list records where the relevant field matches the left most part of this part number but is not necessarily complete. e.g. records with "12345-V1" would be returned, records with "12345-V2" wouldn't. The number of characters recorded in this field will vary, but if the characters that are entered match, then the record should be listed.
In other words, the record entries themselves need to be treated like wildcard entries.
View 7 Replies
View Related
May 2, 2013
Any way to build something into a sub-query that says 'if no records are found, return '0' or some other string'?
Otherwise is there a way to make a master query ignore sub-queries if they return no records?
Allow me to explain in more detail: I have a series of sub-queries, most of these take the sum of several fields from a number of different tables, and I have a main query which combines all of these, to be used as the basis of a summary report.
These queries aren't a problem, but I have a few other essential queries which take the modal (most common....) entry for fields which aren't numerical. So I can't use the sum function.
Now, if all the sub-queries are working then so does the main query, however if one of them fails to find a result, then none of them show up in the main query. I don't know why.
The issue is that depending on the date range selected, some of the tables targeted by the sub-queries don't have any records at all, so when they are run they return nothing. The sum queries can handle this since they just return 0, but those searching for modal records just find nothing (not 0's and not null fields, just blank across all rows).
Here's an example of my sql statement for the modal queries.
Code:
SELECT TOP 1 Trends.Trend AS ModeTboxTalk, "1" AS [Key]
FROM Trends INNER JOIN [Toolbox Talks] ON Trends.TrendID = [Toolbox Talks].TrendID
GROUP BY Trends.Trend, [Toolbox Talks].TrendID, [Toolbox Talks].TalkDate
HAVING ((([Toolbox Talks].TalkDate)<=[Forms]![WeeklyReportSelect]![WeekBox] And ([Toolbox Talks].TalkDate)>[Forms]![WeeklyReportSelect]![WeekBox]-7) AND (([Toolbox Talks].SiteID)=[Forms]![WeeklyReportSelect]![SiteBox]))
ORDER BY Count([Toolbox Talks].TrendID) DESC;
- FYI the weekly select form is where users select the week and site they want to report against. So it would be really peachy if I could tell the above to say something like 'no trend this week' if indeed there were no records.
View 6 Replies
View Related
Jan 23, 2014
I've been trying to create a query that will take a date and return the week number of the date.
My original date is formatted m/d/yyyy
I need my weeks to start on Monday and I would like the week containing Jan 1st to be the first week of the year
I have tried using the following function:
DatePart("ww",#12/31/2001#,2,1)
but the I get a result of 53 in this query, when I expected/need it to be 1.
View 4 Replies
View Related
May 13, 2014
Today is 13 May and this code is supposed to return the date of next Monday (19 May)
IIf(Weekday(Date())=1,Date()+1,Date()-(Weekday(Date())+9))
but it returns 1/5/2014 (Thursday May 1), when "Date" = 13/5/2014 (Tuesday May 13)
My begin week is Sunday (1) ,
View 8 Replies
View Related
Nov 14, 2014
Trying to import some data from a linked Excel spreadsheet into a local table. One of the fields is a Date/Time type and is recorded in EST (Eastern Standard Time). I want to keep this field for posterity but also add a separate field with the corresponding time as per BST
For clarity, daylight savings time comes into effect this year on 26th Oct in the UK and 2nd Nov in the US. So generally, there is a 5 hour difference between the two time zones, apart from the period between these two dates, when it is only 4 hours.Here is my query - I am using a SWITCH function to create the BST field
Code:
INSERT INTO tblTransactions
SELECT ltbPayments.ID AS Reference, ltbPayments.VALUEDATE AS ValueDate, ltbPayments.LOCALAMOUNT AS Amount, ltbPayments.USDAMOUNT AS AmountUSD, tblAccounts.AccountID AS AccountID, ltbPayments.TRANSACTIONTIME AS TransactionTimeEST,
SWITCH(DateValue(ltbPayments.TRANSACTIONTIME) < DateSerial(2014,10,26) Or DateValue(ltbPayments.TRANSACTIONTIME) >= DateSerial(2014,11,2),
[code]....
So - how do I explicitly specify the output of the SWITCH function to be in Date/Time format (I presume, by default, it's returning Text, which contradicts the table properties of tblTransactions & the TransactionTimeBST field?...)
View 1 Replies
View Related
Nov 1, 2014
I am trying to write an access SQL query that returns all records shared between table1 and table2 LESS THAN 3 TIMES.
application has 4 tables: Patients, AnnualPhysical, Doctors, and PatientPhysicalDoctors.
each patient has an annual physical (once a year), and chooses one doctor per annual physical.
each doctor can only perform three or less annual physicals per year.
It is a small application with less than 1000 records per table.
the query will allow the patient to choose an annual physical doctor once a year from available doctors.
Patients is one-to-many to AnnualPhysical;
AnnualPhysical is one-to-many to PatientPhysicalDoctors;
Doctors is one-to-many to PatientPhysicalDoctors.
query for all records NOT shared between Doctors and PatientPhysicalDoctors is:
SELECT *
FROM Doctors
WHERE NOT EXISTS( SELECT * FROM PatientPhysicalDoctors
WHERE Doctors.DoctorID = PatientPhysicalDoctors.DoctorIDFK );
View 3 Replies
View Related
Apr 8, 2013
I am trying to create a query that returns records whether a field has data or not...
There are three fields in question, SSN, DOB (this is a date field), POB (this is a foreign key representing a state in the query shows the actual state). Now unless the criterion is different then I just need the answer for one I can reproduce.
I would like to do this in the criteria box in the query.
The query pulls from one table, some of the employees in this table have the three fields populated some don't. I would like the query to return all employees...
View 2 Replies
View Related
Jul 23, 2013
I am trying to create a query pulling from several tables. I will use the example below to illustrate what I'd like to do.
I have a field called 'Acc_Num', one called 'Stat_Code', and a third called 'Cat_Code'.
Each 'Acc_Num' can have multiple records because there are multiple Stat_Code and Cat_Code values.
What I want to do is isolate just the Acc_Num records where Stat_Code = 1 and Cat_Code equals A1.
I also want to isolate the Acc_num records where the Stat_code = 1 and where Cat_code does not exist.
View 7 Replies
View Related
Sep 26, 2014
I have an existing query, created using the query wizard which works just fine - however, I would like to modify it to return only instances where there are 3 or more records appearing.
Essentially, its an employee history report for a particular action done by those employees, which returns all records between two dates as specified by the user. What I would like to do is only show those employees who have had more than three instances of this action in the given date period.
I am thinking along the lines of DCount? but how it would be phrased in the query?
View 14 Replies
View Related
Jan 17, 2014
I have a table with duplicate records (which is ok) and I want to return distinct data for each client.
It works fine when there is only two fields returned however, when I add a third field to the query it no longer returns only the Distinct records - I am getting Duplicates returned.
I.E
SELECT DISTINCT tblClient.ClientNo, tblClient.Name
FROM tblClient
Works fine with only the Distinct records for each client returned
However
SELECT DISTINCT tblClient.ClientNo, tblClient.Name, tblClient.Address, tblClient.OrderValue
FROM tblClient
Now returns Duplicates!
Is there a limit to the number of fields to be returned using DISTINCT or what else could be the problem? Should I be doing this some other way?
View 5 Replies
View Related
Aug 15, 2013
I have a single table with customer information, one of the fields is a date field "LastContacted".
I'm creating a search form with 2 date fields (txtDate1 & txtDate2) to search a date range of the LastContacted field, and I need to write this into the query that the search form uses.
I have written this using Nz so that it can still return results if the search boxes are left blank:
Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)
This seems to work and it returns lines from the table where there is a date entered. However some of the fields in the table have no entry in the LastContacted field. How to code this query so that it also returns lines where the LastContacted field is blank in the table?
I have tried:
like "*" & (Between Nz([Forms]![frm_AdvancedSearch]![txtDate1],#01/01/1989#) And Nz([Forms]![frm_AdvancedSearch]![txtDate2],#01/01/2999#)) & "*"
but this returns errors when I try to run it.
I'm using Access 2010.
View 14 Replies
View Related
