I have a query that searches an SQL database for drugs recently 
prescribed. How do I structure a query parameter to show only the most 
recent entry. This needs to be the most recent entry irrespective of date. 
The drug could have been prescribed yesterday or as long as a year ago. Is 
this possible and if so how do I do it?

Thanks


Craig

Craig,

Firstly make sure the table with the data has an Autonumber field (often
included as ID) - this will increment each time a new entry is added to
the table.

In you query select the fields you require (drug type etc) and also
include the ID field, switch on the Totals filter (this is on the
toolbar and is like a letter E but the greek symbol I think) - you will
see a new line appear in the query column that says Group By - change
this to Max and run the query.

It should now show the entry with the greatest ID value which is the
last one entered in the table.

Any problems let me know.

Regards

Paul




-----Original Message-----
From: Dr Craig A Thompson
[mailto:Craig.Thompson@d...] 
Sent: 20 March 2002 23:25
To: Access
Subject: [access] Most recent date in Microsoft Access Query

I have a query that searches an SQL database for drugs recently 
prescribed. How do I structure a query parameter to show only the most 
recent entry. This needs to be the most recent entry irrespective of
date. 
The drug could have been prescribed yesterday or as long as a year ago.
Is 
this possible and if so how do I do it?

Thanks


Craig

Create a aggregate query that uses GROUP BY and for the date use Max(Date)

|-----Original Message-----
|From: Dr Craig A Thompson
|[mailto:Craig.Thompson@d...]
|Sent: Wednesday, March 20, 2002 11:25 PM
|To: Access
|Subject: [access] Most recent date in Microsoft Access Query
|
|
|I have a query that searches an SQL database for drugs recently
|prescribed. How do I structure a query parameter to show only the most
|recent entry. This needs to be the most recent entry irrespective of date.
|The drug could have been prescribed yesterday or as long as a year ago. Is
|this possible and if so how do I do it?
|
|Thanks
|
|
|Craig
|

If you are selecting just one drug, construct a TOP n query with a date
field sorted in ascending order and show only the first record.

e.g.

SELECT TOP 1 tblDrugsPrescribed.Date_Prescribed,
tblDrugsPrescribed.Drug_Name
FROM tblDrugsPrescribed
ORDER BY tblDrugsPrescribed.Date_Prescribed ASC;

The TOP n query can be selected from the Query Properties dialog box and
you can amend how many to show by amending the property to 1, 2, 5 or
whatever.


If you are selecting ALL drugs and want to know the latest of EACH drug,
make it a totals query and group on the drug name but select the MAX value
for each of the dates for each drug.

Joe Dunn




                                                                                                               
                    "Dr Craig A Thompson"                                                                      
                    <Craig.Thompson@d...        To:     "Access" <access@p...>          
                    ot.nhs.uk>                                 cc:                                             
                                                               Subject:     [access] Most recent date in       
                    20/03/2002 23:25                           Microsoft Access Query                          
                    Please respond to "Access"                                                                 
                                                                                                               
                                                                                                               




I have a query that searches an SQL database for drugs recently
prescribed. How do I structure a query parameter to show only the most
recent entry. This needs to be the most recent entry irrespective of date.
The drug could have been prescribed yesterday or as long as a year ago. Is
this possible and if so how do I do it?

Thanks


Craig



*************************************************************************

This e-mail may contain confidential information or be privileged. It is intended to be read and used only by the named
recipient(s). If you are not the intended recipient(s) please notify us immediately so that we can make arrangements for its return:
you should not disclose the contents of this e-mail to any other person, or take any copies. Unless stated otherwise by an
authorised individual, nothing contained in this e-mail is intended to create binding legal obligations between us and opinions
expressed are those of the individual author.

The CIS marketing group, which is regulated for Investment Business by the Financial Services Authority, includes:
Co-operative Insurance Society Limited Registered in England number 3615R - for life assurance and pensions
CIS Unit Managers Limited Registered in England and Wales number 2369965  - for unit trusts and PEPs
CIS Policyholder Services Limited Registered in England and Wales number 3390839 - for ISAs and investment products bearing the CIS
name
Registered offices: Miller Street, Manchester M60 0AL   Telephone  0161-832-8686   Internet  http://www.cis.co.uk   E-mail
cis@c...

CIS Deposit and Instant Access Savings Accounts are held with The Co-operative Bank p.l.c., registered in England and Wales number
990937, P.O. Box 101, 1 Balloon Street, Manchester M60 4EP, and administered by CIS Policyholder Services Limited as agent of the
Bank.

CIS is a member of the General Insurance Standards Council

CIS & the CIS logo (R) Co-operative Insurance Society Limited

********************************************************************************