Show only Latest date in reuslts of a query

Hi
I have a parent table holding review dates and a child table with audit 
trail entries made with dates.
I want to extract all documents with a review date within a range (select 
query doing this) and would also like to see only the latest audit trail 
entry in the the child table. 
At the moment my select query shows a document with all audit trail entries.

Any suggestions on how I can get only the latest audit trail entry would be 
deeply appreciated.

Thanks Dee
0
Utf
2/12/2008 11:37:00 AM
access 16762 articles. 2 followers. Follow

4 Replies
1293 Views

Similar Articles

[PageSpeed] 22

Select query left join to ---
       Group by query with these fields --
       Audit_Identifier     Max(audit trail entry date)  left join both 
fields --
                  Child table fields
-- 
KARL DEWEY
Build a little - Test a little


"Dee" wrote:

> Hi
> I have a parent table holding review dates and a child table with audit 
> trail entries made with dates.
> I want to extract all documents with a review date within a range (select 
> query doing this) and would also like to see only the latest audit trail 
> entry in the the child table. 
> At the moment my select query shows a document with all audit trail entries.
> 
> Any suggestions on how I can get only the latest audit trail entry would be 
> deeply appreciated.
> 
> Thanks Dee
0
Utf
2/12/2008 4:59:01 PM
Dee:

You can restrict the results by means of a correlated subquery which 
identifies the latest (MAX) review date for the current value of the columns 
which join the tables, e.g.

PARAMETERS [Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT *
FROM Parent INNER JOIN Child AS C1
ON Parent.MyID = C1.MyID
WHERE C1.ReviewDate =
    (SELECT MAX(ReviewDate)
     FROM Child AS C2
     WHERE C2.MyID = C1.MyID
     AND ReviewDate >= [Enter start date:]
     AND ReviewDate < [Enter end date:] +1);

A couple of things to note:

1.  The two instances of the table Child are given aliases C1 and C2 to 
differentiate them.

2.  If date/time parameters are used, as in the above example, it’s a good 
idea to declare them as such.  Otherwise they could be interpreted as 
arithmetic expressions if entered in short date format and give the wrong 
results.

3.  The method for determining the date range on the basis of all rows on or 
after the start date and before the day following the end date ensures that 
any rows with dates on the final day of the range which might inadvertently 
contain a non-zero time of day (which can easily happen if steps have not 
been taken in the table definition to prevent this) are returned, whereas a 
BETWEEN….AND operation would exclude such rows.

4.  In the above example MyID is the name of the primary key of Parent and 
the corresponding foreign key column in Child.

5.  I've assumed that by " latest audit trail entry" you mean the latest 
review date within the specified range, even if there should be a later 
review date outside of the range.

Ken Sheridan
Stafford, England 

"Dee" wrote:

> Hi
> I have a parent table holding review dates and a child table with audit 
> trail entries made with dates.
> I want to extract all documents with a review date within a range (select 
> query doing this) and would also like to see only the latest audit trail 
> entry in the the child table. 
> At the moment my select query shows a document with all audit trail entries.
> 
> Any suggestions on how I can get only the latest audit trail entry would be 
> deeply appreciated.
> 
> Thanks Dee

0
Utf
2/12/2008 6:47:03 PM
Thanks Karl

"KARL DEWEY" wrote:

> Select query left join to ---
>        Group by query with these fields --
>        Audit_Identifier     Max(audit trail entry date)  left join both 
> fields --
>                   Child table fields
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Dee" wrote:
> 
> > Hi
> > I have a parent table holding review dates and a child table with audit 
> > trail entries made with dates.
> > I want to extract all documents with a review date within a range (select 
> > query doing this) and would also like to see only the latest audit trail 
> > entry in the the child table. 
> > At the moment my select query shows a document with all audit trail entries.
> > 
> > Any suggestions on how I can get only the latest audit trail entry would be 
> > deeply appreciated.
> > 
> > Thanks Dee
0
Utf
2/13/2008 8:55:01 AM
This definately worked though I am not very good with SQL so took me very 
long to understand but great stuff - thanks a lot Ken

"Ken Sheridan" wrote:

> Dee:
> 
> You can restrict the results by means of a correlated subquery which 
> identifies the latest (MAX) review date for the current value of the columns 
> which join the tables, e.g.
> 
> PARAMETERS [Enter start date:] DATETIME,
> [Enter end date:] DATETIME;
> SELECT *
> FROM Parent INNER JOIN Child AS C1
> ON Parent.MyID = C1.MyID
> WHERE C1.ReviewDate =
>     (SELECT MAX(ReviewDate)
>      FROM Child AS C2
>      WHERE C2.MyID = C1.MyID
>      AND ReviewDate >= [Enter start date:]
>      AND ReviewDate < [Enter end date:] +1);
> 
> A couple of things to note:
> 
> 1.  The two instances of the table Child are given aliases C1 and C2 to 
> differentiate them.
> 
> 2.  If date/time parameters are used, as in the above example, it’s a good 
> idea to declare them as such.  Otherwise they could be interpreted as 
> arithmetic expressions if entered in short date format and give the wrong 
> results.
> 
> 3.  The method for determining the date range on the basis of all rows on or 
> after the start date and before the day following the end date ensures that 
> any rows with dates on the final day of the range which might inadvertently 
> contain a non-zero time of day (which can easily happen if steps have not 
> been taken in the table definition to prevent this) are returned, whereas a 
> BETWEEN….AND operation would exclude such rows.
> 
> 4.  In the above example MyID is the name of the primary key of Parent and 
> the corresponding foreign key column in Child.
> 
> 5.  I've assumed that by " latest audit trail entry" you mean the latest 
> review date within the specified range, even if there should be a later 
> review date outside of the range.
> 
> Ken Sheridan
> Stafford, England 
> 
> "Dee" wrote:
> 
> > Hi
> > I have a parent table holding review dates and a child table with audit 
> > trail entries made with dates.
> > I want to extract all documents with a review date within a range (select 
> > query doing this) and would also like to see only the latest audit trail 
> > entry in the the child table. 
> > At the moment my select query shows a document with all audit trail entries.
> > 
> > Any suggestions on how I can get only the latest audit trail entry would be 
> > deeply appreciated.
> > 
> > Thanks Dee
> 
0
Utf
2/13/2008 8:56:01 AM
Reply:

Similar Artilces:

dates in formulas
I would like my spreadsheet to add a month to a cell based upon the value of the date in another cell. In other words, I would enter the date in A1 and be formatted as Oct-2009, and I would like to put a formula in A2 that would result in the display of Nov-2009. I've tried =A1+1 but that just won't get it as it still returns Oct -09. "Russ" <Rusty@alwaysathome.net> wrote: > I've tried =A1+1 but that just won't get it as it > still returns Oct -09. Because you added one __day__, not one month. Try one of the following: =date(year(A1),1+month(A1),da...

analytical accounting lookups should only show valid codes
When entering AA transaction distributions, the code lookup window should only show valid code combinations. It's not intuitive for the user to have to select a code and get an error message to discover which codes are valid. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the mess...

Outlook shows offline during dial up
Having a problem with outlook 2002. Over the WAN everything works fine. Dial up and connect to the network (VPN) internet work fine. Outlook will sync up however, a red x shows up in the bottom right hand corner. Outlook shows offline, I can send and receive but must hit send and receive. I have reloaded the computer from scratch and still have the same problem. Could it be a Excange setting? Any Ideas? Thanks for the help, If you hit File | Connect to Exchange Server what happens? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize ...

Show a blank result in a cell when there is no value in the "Lookup" cell
I apologize if this question has been asked, but I have been unable to find an answer searching the topics. I'm using the following formula in cell C3: =INDEX(LastName,MATCH(A3,EmpID,0)) When I type in an employee ID in A3, his/her last name shows in C3. However, when there is no value in A3, C3 shows error "#N/A". Is there a way to show a blank cell in C3 until a value is entered into A3? Thanks in advance! Mike On Aug 5, 10:45 am, "Michael Slater" <mslater...@comcast.net> wrote: > I'm using the following formula in cell C3: > =INDEX(LastNam...

Show Window
I am currently developing a MFC SDI application. I want to integrate a tray icon with it. When the application is minimized it hides by ShowWindow(SW_HIDE) and then when double clicked on tray icon shown by ShowWindow(SW_SHOWNORMAL). But the problem is if some other application is activated after my application is minimized, ShowWindow shows it but it's behind some screen. I have tried with SetFocus didn't work. Please help me to solve this problem. Thank you. Varuna Try BringWindowToTop. ---- Ajay Kalra ajaykalra@yahoo.com In addition to Ajay's idea you can also use SetFor...

Information Stores show dismounted yet mail is still flowing...
Background: Windows AD Mixed moded. Exchange 2003 installed on to a 2 node Microsoft cluster. 1 Front End server...connecting to the primary cluster node. Windows 2003 Standard on, FE Windows 2003 Enterprise Server on BEs (<---cluster) no service packs on either. Exchange has SP1 installed on both nodes and the FE. There are still Exchange 5.5 servers in the environment although they are not being used. The Issue: .....the information stores and public folders all show that they are dismounted...yet email is still flowing into and out of the server without any issues. I just...

Manupilate Query Results
Hi all. I need to change the way data is diplayed after a SQL query. What I am tying to do is organise data in such a way that MS project will open the file with minimum mapping. See below what I have got and what I need: I have got: Cust|CallType|Priority|CallID|Asignee|Date ect ect A | HR | X | 12 | Me | 0/0/2003 A | HR | X | 12 | John | 0/0/2003 B | Deve | Y | 14 | Stan | 0/1/2003 What I need the data to look like is: (the 1-4 is for MS Project Indents) 1 |A | 2 |HR| 3 |X | 4 |12 | Me | 0/0/2003 4 |12 | Joh...

reminder still shows up
when I run Outlook.exe /cleanreminders it simply opens Outlook, I am running Vista "Toppro" <Toppro@discussions.microsoft.com> wrote in message news:7AE8DBF7-6BE3-454A-82F9-5A040566026C@microsoft.com... > when I run Outlook.exe /cleanreminders it simply opens Outlook, I am > running > Vista Outlook version? The Windows version isn't as important and that of Outlook. -- Brian Tillman [MVP-Outlook] ...

Dates to days/months/years
Hi folks, I have around 40 years worth of data and I need to convert the dates into days, months and years. Tried several things but they don't seem to work. I calculated there are 13200 days in total - how can you convert this to days/months/years? Big thanks in advance! Ian Ian, Do you mean you want to "break down" dates in a column to three separate columns with year, month and date? If yes, functions: YEAR(somedate) MONTH(somedate) DAY(somedate) will give you what you want. If you meant something else, please clarify. HTH, Nikos "Ian" <ian_rules@yahoo...

difficulty with queries
i've been struggling with this for a couple weeks now and haven't been able to find aything in the foums on it. k, i am try to make a query that will sort by date (only displaying the parameter date), then count the results to show on a weekly report, forcasting for next week (which i am also haveing probs with) table 1 last name || fist name|| vacation start date|| vacation end date and i want the result to be query 1 date || count of ppl on vaction on that date report 1 monday||tuesday||.........so on 10 12 the prob i have is that if i do the query on a specific...

Using ajax call to fetch multiple results from multiple queries and showing them 1 by 1 as the results comes.
Hello, I've 20 labels in a 2x10 table on a page. for each label, data comes by individual queries. (total 20 queries for the page.) and it takes about 30-40 seconds for the page to load, and it's not comfortable for the user to see blank page. I want that each cell will show loading.gif images. As and when data comes from query, the images should go visible=false, and the label should display the values one by one. How do I do this task using ajax? Some ASP.NET code example will be helpful for me, as I'm a kind of beginner. Thanks ...

Results show as a minus. WHY?
I have an application that was developed by another developer using VB.net which reside in a SQL table. I need to use some of the data from this table, however the data shows as -0.5 or - 0.99 or -1.70 etc in my Access tables. How can I get those numbers to show as a percentage with only one decimal point to the left? Example: 3.6% or 31.4 %. Any help would be greatly appreciated. No matter what I do it shows that dang "-" at the beginning. You can use the Abs function to strip off the negative Abs([YourTable].[YourField]) And you can format that as percentage either using th...

Date Displays as 1/1/1904
When changing the date using 3/14/1998 format, I get 1/1/1904 displaye in the cell. The task bar appears as =3/1/2004. The date I'm tryin to enter is 3/1/2004. When I try to get rid of the = sign, it always returns. Any thoughts -- Message posted from http://www.ExcelForum.com If the "=" sign is necessary, use this format: use the MONTH, DAY, and YEAR functions. For example... I use "=MONTH($AG7)&"/"&DAY($AG7)" for a formula in on of my spreadsheets. You're not getting the date that you want because you are basicall telling Excel to calcul...

How to create multiple calendar date choices in a meeting request
Dear Experts, I need to create a calendar appointment that requires the participant to select from a list of available dates that the meeting will be offered. When the participant selects the desired date, that meeting wil be placed on his/her respective calendar, based on his/her choice. How can I create this, or is there a feature built in that will allow me to set this up? I do know some VBA. Thank you so much. Create calendar events/appointments for each of the events and attach them to an e-mail instructing the recipients to select one of the dates. -- Milly ...

show comments when cell is highlighted Vs when cursor runs over? #2
How do you make the comments appear when the cell is highlighted opposed to when the mouse cursor runs over the cell? You can press Shift/F2, or Insert/Edit Comment. If you wanted to see it "automatically", when you select the cell, you'd need a macro, but I don't think this is what you're asking. Bob Umlas Excel MVP "Don" wrote: > How do you make the comments appear when the cell is highlighted opposed to > when the mouse cursor runs over the cell? To at least Excel 2000, if you have the Tools, Options, Comment Indicator Only you will see the co...

how to query a dataset with xml tables inside.
This is a multi-part message in MIME format. ------=_NextPart_000_0030_01C3DB37.4468A9E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I have two xml docs, authors & articles. Both have authorID as a common = node <?xml version=3D"1.0" encoding=3D"utf-8"?> <!--This is a comment--> <Authors> <Author> <AuthorID>0</AuthorID> <Name>Mark</Name> <Login>mark</Login> <Pwd>markpwd</Pwd> </Author> <Author> <AuthorI...

how to show numbers that begin with 0 in excel
I need to type numbers into an excel spreadsheet some of them begin with a zero some do not. How can I have excel show the 0 in the numbers that begin with 0. If I type 0236547 the number shows as 236547. I don't want all the numbers I type to begin with zero. I am using Excel 2003. Thanks very much for your help. Best regards, Dee use a custom format with how ever many digits you need, 000000 -- Gary Keramidas Excel 2003 "Dee" <Dee@discussions.microsoft.com> wrote in message news:EA60FC8A-C7CB-432A-B2B2-EEEBF86D2646@microsoft.com... >...

Date Format In A Report
I am writng a report to show assessments due on a weekly basis. I created the report but in the Header of the Report where it will havve the assessment referencedate group together by the week it is showing up as a number to represent which week of the year it is. I want it to display the week as a Sunday date to show the beginning of the week rather than a number to display which week it is in the year. Can someone give me the correct formula to put in there to make it show this? Thanks Julie Julie Gilliam wrote: >I am writng a report to show assessments due on a weekl...

query tables for incomplete orders
I have an ordering database. I would like the ordering staff to be able to see if there are new orders from the front screen (switchboard). When the ordering staff sign off an order they tick a box which removes it from the list of items on order. Does anyone have any ideas or advice as to how I look up the orderdata tables and flag the ones which have not been signed off to the switchboard? I know what to do once the information is there but I am having trouble getting it there. Many thanks in advance for any help. Just an idea !! Create a query with the order information (just the...

Report Server shows 'Service Unavailable'
Hi, before install, the SRS setup, send me a warning, some like "the Windows Sharepoint Services are install and not permit initialize a Reportserver component" i skip wiht the installation, so.. I can't run http://<localhost>/Reports and http://<localhost>ReportServer show a message "Service Unavailabe", but the ReportServer service is running How initialize that or what do i do? ------=_NextPart_0001_09E699C9 Content-Type: text/plain Content-Transfer-Encoding: 7bit <Miguel@discussions.microsoft.com> wrote: > before install, the SRS set...

Comments not showing
When a SharePoint workflow is edited and a comment is added. The comment does not display in the workflow status screen under workflow history. It shows as follows: Task assigned to Andre Wessels was completed by System Account. Comments: Instead of: Task assigned to Andre Wessels was completed by System Account. Comments: Please update to the new company logo. This happens for all standard SharePoint workflows (Approval, Collect Feedback). Any custom workflows does show the comment. The comment field is update in the task item. It just does not show. There is no error in t...

run a macro via a query
I have a series of queries that are tied together in a macro. Example: The macro first deletes the data in an existing maketable. It then appends new data from a linked table, changing the formatting from Number to text. It then runs a final query that pulls data based on the appended table. I would like to have one query that a person could run that would run the macro. Is this possible? Thanks, Perry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1 Macros can run queries. Queries (as far as I know) do not run Macros --...

Show more time in weekly view?
Outlook 2007 On default the weekly view is only showing until 4 PM without scrolling down. I know there is a way to let you see into the evening hours also without scrolling. Help please. Thanks... Bob ...

out of date file
I am trying to open an invoice spreadsheet. I have used it many times before but now I get a microsoft visual basic message "the file VBA332.dll is out of date. This program requires a newer version" It wont open. Any known resolutions??? ...

TransparentBlt doesn't show the bitmap in ME?
The following doesn't print the bitmap on ME while it works on 2k/xp, can someone help me? Though the status returns TRUE. Thanks. Jiac. CDC *hdc, bghdc; CBitmap bgBitmap; BITMAP bmpInfo; //loads bitmap into memory bgBitmap.LoadBitmap(IDB_BITMAP5); // this bitmap has no needles // get bitmap info bgBitmap.GetBitmap(&bmpInfo); hdc = m_obj->GetDC(); bghdc.CreateCompatibleDC(hdc); bghdc.SelectObject(&bgBitmap); BOOL status = hdc->TransparentBlt(0, 0, 80, 80, &bghdc, 0, 0, 80, 80, TRANSPARENT_KEY_COLOR); //TRANSPARENT_KEY_COLOR if (st...