find the latest date in access 2007

hi there!

I'm a bit stressed trying to get this sorted by I just cant!

My question is the following. I have a database of three tables: Dis_cat, admission and patient. The Dis_cate table has only two categories : dead and alive. I just have to find out who was the last patient to die. 

I have this code in access:

SELECT Dis_cat.category, Admision.outdate, Patient.pname
FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno = Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
WHERE (((Dis_cat.category)="dead"));

But I cant find the latest date. I tried using the DMAX but it didnt work. ANY SUGGESTION?

Thanks!

my tables information is: 

category outdate	pname
Dead	15/12/2009	Gureney
Dead	29/12/2009	Twyman
Dead	22/09/2008	Swaddling
Dead	18/10/2009	Royce
Dead	03/11/2008	Perry
Dead	03/06/2009	Miles
Dead	29/09/2009	Lloyd
Dead	07/01/2010	Kahn
Dead	28/08/2009	Hawkes
Dead	28/10/2009	Frederick
Dead	29/08/2008	Drake
Dead	18/05/2009	Cambridge
Dead	10/01/2009	holloway




Submitted via EggHeadCafe - Software Developer Portal of Choice 
C# Threading Http Requests
http://www.eggheadcafe.com/tutorials/aspnet/985b1bd6-3dee-48ce-8769-7010659bd748/c-threading-http-request.aspx
0
roch
1/7/2010 11:23:05 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1905 Views

Similar Articles

[PageSpeed] 45

Try this:

SELECT category, outdate, pname
FROM Patient INNER JOIN (Admision 
INNER JOIN Dis_cat 
ON Admision.dischno = Dis_cat.dischno) 
ON Patient.nhsno = Admision.nhsno
WHERE outdate =
    (SELECT MAX(outdate)
     FROM Admision INNER JOIN Dis_cat 
     ON Admision.dischno = Dis_cat.dischno
     WHERE category = "dead");

The subquery returns the latest (MAX) outdate where the category is 'dead'.
This is then used to restrict the outer query to the row or rows where the
outdate equals that date, i.e. the patient or patients who where discharged
dead on the latest discharge date recorded in the database.

Ken Sheridan
Stafford, England

roch bell wrote:
>hi there!
>
>I'm a bit stressed trying to get this sorted by I just cant!
>
>My question is the following. I have a database of three tables: Dis_cat, admission and patient. The Dis_cate table has only two categories : dead and alive. I just have to find out who was the last patient to die. 
>
>I have this code in access:
>
>SELECT Dis_cat.category, Admision.outdate, Patient.pname
>FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno = Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
>WHERE (((Dis_cat.category)="dead"));
>
>But I cant find the latest date. I tried using the DMAX but it didnt work. ANY SUGGESTION?
>
>Thanks!
>
>my tables information is: 
>
>category outdate	pname
>Dead	15/12/2009	Gureney
>Dead	29/12/2009	Twyman
>Dead	22/09/2008	Swaddling
>Dead	18/10/2009	Royce
>Dead	03/11/2008	Perry
>Dead	03/06/2009	Miles
>Dead	29/09/2009	Lloyd
>Dead	07/01/2010	Kahn
>Dead	28/08/2009	Hawkes
>Dead	28/10/2009	Frederick
>Dead	29/08/2008	Drake
>Dead	18/05/2009	Cambridge
>Dead	10/01/2009	holloway
>
>Submitted via EggHeadCafe - Software Developer Portal of Choice 
>C# Threading Http Requests
>http://www.eggheadcafe.com/tutorials/aspnet/985b1bd6-3dee-48ce-8769-7010659bd748/c-threading-http-request.aspx

-- 
Message posted via http://www.accessmonster.com

0
KenSheridan
1/8/2010 12:04:25 AM
If your query works otherwise then this should do it for you --
SELECT TOP 1 Dis_cat.category, Admision.outdate, Patient.pname
FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno = 
Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
WHERE (((Dis_cat.category)="dead"))
ORDER BY Admision.outdate DESC;

-- 
Build a little, test a little.


"roch bell" wrote:

> hi there!
> 
> I'm a bit stressed trying to get this sorted by I just cant!
> 
> My question is the following. I have a database of three tables: Dis_cat, admission and patient. The Dis_cate table has only two categories : dead and alive. I just have to find out who was the last patient to die. 
> 
> I have this code in access:
> 
> SELECT Dis_cat.category, Admision.outdate, Patient.pname
> FROM Patient INNER JOIN (Admision INNER JOIN Dis_cat ON Admision.dischno = Dis_cat.dischno) ON Patient.nhsno = Admision.nhsno
> WHERE (((Dis_cat.category)="dead"));
> 
> But I cant find the latest date. I tried using the DMAX but it didnt work. ANY SUGGESTION?
> 
> Thanks!
> 
> my tables information is: 
> 
> category outdate	pname
> Dead	15/12/2009	Gureney
> Dead	29/12/2009	Twyman
> Dead	22/09/2008	Swaddling
> Dead	18/10/2009	Royce
> Dead	03/11/2008	Perry
> Dead	03/06/2009	Miles
> Dead	29/09/2009	Lloyd
> Dead	07/01/2010	Kahn
> Dead	28/08/2009	Hawkes
> Dead	28/10/2009	Frederick
> Dead	29/08/2008	Drake
> Dead	18/05/2009	Cambridge
> Dead	10/01/2009	holloway
> 
> 
> 
> 
> Submitted via EggHeadCafe - Software Developer Portal of Choice 
> C# Threading Http Requests
> http://www.eggheadcafe.com/tutorials/aspnet/985b1bd6-3dee-48ce-8769-7010659bd748/c-threading-http-request.aspx
> .
> 
0
Utf
1/8/2010 12:39:01 AM
Reply:

Similar Artilces:

Printing problem from Excel 2007
Greetings, I am having an issue with printing from Excel 2007. Many users when printing from Excel with get several pages of non-sensical "junk" printed out on the pages. If it actually prints what is on the page, none of the items are in the cells, but look like they are just randomly thrown about the page. I have found that this most often is related to the Calibri font somehow, because I instruct them to change the font to Arial and all prints fine. That being said, if the user converts the Excel sheet into a PDF and print, it prints perfect. This is a great workaround, but kind ...

Excel 2007 Need to permanently change Normal settings for gridlines
I am using Excel 2007 for the first time and find the gridlines delineating the cells are so faint as to be nearly indistinguishable. I can go into the cell formatting and modify the normal style, and it is just the way I want it. But I can't find a way to make Excel remember this and treat it as the new definition of the Normal style. I don't want to have to redefine Normal every time I open a new spreadsheet. Someone please help! Regards Leonard Priestley The changes you describe are changing the Border color and NOT gridlines. Go into Excel Options and cl...

Cannot access by OWA
Hi there, we�ve two ex2k servers. First in domain 1 with internet connection. Second in subdomain, connected by a dedicated line. Both servers are in same org and site. When I try to access the second server by owa I can�t access the mailboxes there. That�s only possible on the first server (SSL security is enabled). Do I�ve missed to configure something? Thanks for any advice. JK Sorry, found it myself. Just have to configure the server as a frontendserver! "J. Kuenzel" <kuenzelj@yahoo.com> schrieb im Newsbeitrag news:OT3swrqLEHA.3696@TK2MSFTNGP09.phx.gbl... > Hi the...

Access attachments don't work anymore
Outlook 2000's security update makes it impossible to receive MS Access .mdb or .mde files. But I need to do this to earn a living as an Access developer! Other than have the sender rename the file there must be a simpler way to override the security patch. Outlook Express let's you turn off this feature but OL 2000 has no such setting. (The feature is basically worthless anyway - any hacker would know you just rename the file, put your hacker code in it and send it. Access will open ANY file, regardless of what you name it!!) See if the information on the following page help...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

frustrations with Access 2006
is anyone else frustrated with the number of time, when editing a project..., Access 2006 crashes, leaving behind a corrupt, un-repairable file (or somtimes just a corrupt form, never-the-less: frustrating)? short of going back to an earlier version of the program, does any one have ANY suggestions? thanks in advance... mark "Mark Kubicki" <mark.kubicki@verizon.net> wrote in message news:u7Pv4lHDIHA.4880@TK2MSFTNGP03.phx.gbl... > is anyone else frustrated with the number of time, when editing a > project..., Access 2007 crashes, leaving behind a corrupt, un-re...

Do you need Access to use an Access Database for data entry only?
We have an Access database completed. Now we will use it for reading as well as for data entry. Do we need to install Access for every workstation needing the database for data entry or reading? No you don't necessarily need Access on every computer. You CAN use the Access Runtime and if it is in Access 2007 you can use it for free. If you are using another version then you would need to purchase the developer's edition to be able to legally distribute the associated Access runtime. If you do have 2007 and want to use the runtime, make sure your Full version of Access doe...

Access, average several fields in one row
I have several rows of data in a field, I need to average all the entries in one row I have 12 fields for 12 months of data, I need the average of the sum of all non blank entries. For example 3 months completed, the solution in Excel is (field1+field2+field3)/3 I am looking for method to average the sum in Access One way if you can't change your data is to use a VBA function. I've posted one below. You would call it in a calculated field in a query. Assuming your field names are the abbreviated month names the expression might look like the following. Field: fRow...

Date Range Formula Question
Hello, I'm having trouble with a formula and I'm hoping someone can help. :confused: Sample Data Includes the following: Pay Period Start Pay Period End Pay Period # 12/16/01 12/29/01 26 12/30/01 01/12/02 25 01/13/01 01/26/02 24 01/27/02 02/09/02 23 02/10/02 02/23/02 22 The pay periods continue until there are 26 pay periods for the entire year....

Access 2007 Reports
I'm using an Access 2000 mdb (split ends) in Access 2007. I cannot up- convert because other terminals don't have Office 2007. It works as a database just fine except: When I try to change a (front end) report from the default printer to another specific printer in design view, page setup, it all LOOKS good except it does not save my changes upon return and goes back to the default printer. I never had this problem with earlier versions of Access and the specific printer in questions works fine with other apps and terminals. Any ideas people? Many thanks. JM This is a known prob...

Question Regarding Excel 2007 Formatting Corruption
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...

IE8 can't access Microsoft Office site when all other browsers can
When trying to access the Microsoft Office page using IE8 I keep getting the "Internet Explorer cannot display the webpage" message. If I use any other browser (Firefox, Opera, Safari, Chrome) I have no problem accessing the page. This happens whether running in normal or No Add-on's mode. Despite how many people keep complaining about all the problems with IE8, neither the MVP's or Microsoft acknowledge that they exist. Asus P5E Intel E8400 Core2Duo 3.0GHz 4 GB PC2-6400 DDR2 Windows 7 Ultimate (with all the latest updates installed) NIS 2010 (all up to date)...

Unable to open Access 95 mdb in Access 97
Hi I am trying to open an old Access 95 mdb in Access 97...I didn't create it but someone who knew the database way back when said that a system.mda file was created for that particular database because it had a lot of personal info in it. (I am not familiar with mda files...the database folder also had a system.ldb file there as well...I deleted that file...but am unsure why it wasn't closed out to begin with) I was able to open Access 97 and repair it...but when I go to open it in Access 97...I get an Automation Error...Cannot find VEENLR3.hlp file. Do you think it may ha...

Is store procedure always fast than Access linked table via ODBC?
I was assigned to upgrade one program from Access(using ODBC to connect to SQL 2000) to ASP.NET(using store procedure in SQL 2000). Finally, I tested them and found that ASP.NET is slower than Access. The mojority job of program is select some data from SQL 2000 tables, modify and then insert into some tables. Is store procedure always fast than Access linked table via ODBC? -- Message posted via http://www.sqlmonster.com Stored procedures don’t add any overhead and they can save compile time. It's the code in the stored procedure and the underlying tables / indexes that ...

Backup error Access Denied
I have been recieving the following error message for a while and can't figure out how to fix it. It is only happening on one mailbox. "Backup - \\MBMAIL\Microsoft Exchange Mailboxes Access denied to file Jhon Doe [jdoe]Top of Information StoreSync IssuesServer FailuresMail Delivery (failure jdoe@xyz.com)." Help Please Can you access the mailbox from a mail client? If so, you can use mdbvu to look at the Top of the Information store folder, and delete the message in there. If you cannot access the mailbox, then I have seen it where some users think they are ...

Time Zone / Incorrect Dates Displayed to User
When a user in the Central Time zone closes an opportunity and someone in the Mountain Time zone views it, the Actual Close Date is 1 day before the date the person in the Central Time zone selected. For instance... If someone in the Central Time zone closes an opportunity and selects the Actual Close Date as 6/2/2007: -- Users in the Central Time zone see: 6/2/2007 -- But users in the Mountain Time zone see: 6/1/2007 This is a big issue when running monthly reports as it makes some opportunities show in the wrong month... and will put that revenue in the wrong month. It would make mo...

2007 B2 MSOO has encountered a problem
More often then not when I click to open an email in my inbox I get this MSOO pop-up with a checkbox, checked telling me MSOO has encountered a problem and needs to close. Has this been an issue for others and is there a fix. If you have Adobe Acrobat Pro 6/7 (not the reader), go into Control Panel, Add/Remove Programs, click change for Adobe Acrobat Pro and remove the Office & Outlook plugins. Patrick Schmid -------------- http://pschmid.net "Jax" <Jax@discussions.microsoft.com> wrote in message news:A74147F4-CD75-4FF8-98A7-5A18B01A8FDD@microsoft.com: > More of...

Insert Page Numbers on Worksheet in Excel 2007
In Excel 2003, if you wanted to put page numbers on multiple worksheets in a workbook, you grouped the worksheets and then added a header or footer, using the page number function. All of your grouped worksheets would shows its own page number. But in Excel 2007, only my first worksheet is numbered when I do this (as Page 1). What's the problem? Do I actually have to put a page number, one by one, on each worksheet? I cannot replicate your problem with 2007. Grouped sheets behave exactly as 2003 did. After grouping and adding a header of Page 1 did you do a print p...

Outlook 2007: When i Save a New Rule outlook Change it automatically...
Hi people! I have this problem with Outlook 2007. I have 15 e-mails addresses in my outlook, but i can=B4t create the rules for send all the received mails to each emails folder. Example: I want to create a simple rule that move all the e-mail that i get from "example1@example1.com" to the folder "Inbox/ example1@example1.com". When i create the rule, all works fine, i select the correct e-mail account name, the correct destination folder, etc... but when I hit the SAVE button in rules window, Outlook 2007 changes the account automatically to another one. Not automatically...

adding date/time picker to inputbox control
Hi all,is there a way to add a date/time picker control to inputbox?...

Why can't I send email messages with Trial Outlook 2007?
I have a new HP laptop that came with the 60 day trial of Office'07. I receive email but the send/reply/compose functions are grayed out. I am certain my settings are correct. Why can't I send email? KK What happens when you use the Test Account Settings? What is the exact error message you get? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, KK asked: | I have a new HP laptop that came with the 60 day...

Outlook Today
On the Outlook Today page the "Options" button does nothing. We're still runnning Win 98 SE2 with no immediate hopes of upgrading. Any help would be appreciated. Thank you. david <anonymous@discussions.microsoft.com> wrote: > On the Outlook Today page the "Options" button does > nothing. We're still runnning Win 98 SE2 with no immediate > hopes of upgrading. Any help would be appreciated. Thank > you. This question has been asked and answered 100,000 times (literally). SEARCH for the answer. -- Brian Tillman Smiths Aerospace 3290 Patterson ...

Emails don't find contacts in restored Outlook
My hard drive burned up, so I replaced it, reinstalled, and imported everything into Outlook. The contacts are there if you click contacts. However, when sending emails, there is no way to connect to my contacts. It's like they are invisible. You never import Outlook data to restore it. You just open the Outlook data file. When you do, you will be able to configure your address book view to display your Contacts the same way as always: http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 -- Russ Valentine "Mike Row Soft" <MikeR...

Access Outlook Add-in for Data Collection & Publishing won't load
I am trying to utilize the Collect Data via e-mail function and keep receiving the following error: "The Microsoft Office Access Outlook Add-in is disabled in Microsoft Office Outlook 2007. To collect data by using e-mail messages in Microsoft Access 2007, verify that this add-in is installed and enabled from within Office Outlook 2007." I have tried to remediate via the Trust Center/Add-in area in Outlook and don't see an add-in for Access (even though the error message says it is "disabled" it is nowhere to be found). I have tried to add it (I believe it is...

Date & Time file was last saved
Is there away to display in a cell the date & time the file was last saved? I know you can do this in word but is it possible in Excel 2002? Here is an UDF Function DocProps(prop As String) application.volatile On Error GoTo err_value DocProps = ActiveWorkbook.BuiltinDocumentProperties(prop) Exit Function err_value: DocProps = CVErr(xlErrValue) End Function Now you can use the following formula in one of your cells: =DocProps("Last save time") Obviously, the file has to have been saved. -- HTH Bob Phillips ... looking out across Poole Harbour to ...