sql aggregate query

My table

ID - Autonumber Field
Patientid - This can have a number of records with the same number
dDate is the date of the record and can be duplicated.

I need to create a new table with the max of date for each unique
patient id.  

So far not a problem.  

SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate)
AS MaxOfdDate
FROM TblCurrentEncounter
GROUP BY TblCurrentEncounter.Patientid;

But the kicker is that i need the id of the row containing the max of
date.

As soon as i try to add ID to the query I get error about not being
part of the aggregate function. 

Any ideas would be appreciated.
0
ras
2/21/2008 3:04:13 AM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
650 Views

Similar Articles

[PageSpeed] 9

Use a subquery to get the maximum date for each ID, and use it in the WHERE 
clause:

SELECT ID, dDate AS MaxOfdDate
FROM TblCurrentEncounter
WHERE dDate =
(SELECT Max(T.dDate) As Md
FROM TblCurrentEncounter AS T
WHERE T.ID = TblCurrentEncounter.ID);

-- 

        Ken Snell
<MS ACCESS MVP>


"ras" <ras@bellsouth.net> wrote in message 
news:i7ppr399vurtfs75pgpqfj4928gadrnd0l@4ax.com...
> My table
>
> ID - Autonumber Field
> Patientid - This can have a number of records with the same number
> dDate is the date of the record and can be duplicated.
>
> I need to create a new table with the max of date for each unique
> patient id.
>
> So far not a problem.
>
> SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate)
> AS MaxOfdDate
> FROM TblCurrentEncounter
> GROUP BY TblCurrentEncounter.Patientid;
>
> But the kicker is that i need the id of the row containing the max of
> date.
>
> As soon as i try to add ID to the query I get error about not being
> part of the aggregate function.
>
> Any ideas would be appreciated. 


0
Ken
2/21/2008 4:49:57 AM
Try:

SELECT ID, Patientid, Max(TblCurrentEncounter.dDate) AS MaxOfdDate
FROM TblCurrentEncounter
GROUP BY ID, Patientid;
-- 
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


"ras" <ras@bellsouth.net> wrote in message 
news:i7ppr399vurtfs75pgpqfj4928gadrnd0l@4ax.com...
> My table
>
> ID - Autonumber Field
> Patientid - This can have a number of records with the same number
> dDate is the date of the record and can be duplicated.
>
> I need to create a new table with the max of date for each unique
> patient id.
>
> So far not a problem.
>
> SELECT TblCurrentEncounter.Patientid, Max(TblCurrentEncounter.dDate)
> AS MaxOfdDate
> FROM TblCurrentEncounter
> GROUP BY TblCurrentEncounter.Patientid;
>
> But the kicker is that i need the id of the row containing the max of
> date.
>
> As soon as i try to add ID to the query I get error about not being
> part of the aggregate function.
>
> Any ideas would be appreciated. 


0
Arvin
2/21/2008 4:50:32 AM
"Ken Snell \(MVP\)" <kthsneisllis9@ncoomcastt.renaetl> wrote in
news:OHZi3UEdIHA.4968@TK2MSFTNGP02.phx.gbl: 

> Use a subquery to get the maximum date for each ID, and use it in
> the WHERE clause:
> 
> SELECT ID, dDate AS MaxOfdDate
> FROM TblCurrentEncounter
> WHERE dDate =
> (SELECT Max(T.dDate) As Md
> FROM TblCurrentEncounter AS T
> WHERE T.ID = TblCurrentEncounter.ID);

More correctly, that's called a *correlated* subquery, in that each
row is correlated with a subquery whose results are specific to that
row. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
2/22/2008 1:39:53 AM
Reply:

Similar Artilces:

Query help 11-28-07
I posted about part of this yesterday and got some great advice, but I just found out there are other issues that no one told me about. Here's the problem: I'm trying to create a query that will calculate how much someone has contributed year-to-date to their supplemental health care plan. Basically the employee agrees to contribute an amount of their choosing. This amount is then divided out so that the individual pays a certain portion of that out of each bi-weekly paycheck. We also need to stop calculating a running total for those employees who terminate, and instead c...

Sql to find record in a hierarchical chain
Hi, lets say I have a table with 2 fields: myTable Field 1 - ref Field 2 - parentRef Now in the structure there could be numerous chains until you get to the top of the hieratchy (where the ref field = parentRef field). ie ref parentRef 111 222 222 333 333 444 444 444 so in this 444 is top of the chain (there will be many other records as well that are nothing to with 444 and are part of their own hierarchy). So how do I easily relate 111 to 444. Ie how do i find the ultimate top parent for a given 'ref' field. Hope anyone can give me some pointers ...

RPC over HTTP/S on Exchange 2003
I have been configuring my single server with exchange to use RPC over https I have followed the instructions in MS guide and another simplified guide at http://www.petri.co.il/configure_rpc_over_https_on_a_single_server.htm Server spec is: Server 2003 standard SP1, Exchange 2003 SP1, XP client SP2 with outlook 2003 sp2 The bottom line is that when testing from the WAN, the outlook client will not connect and say that the exchange server is unavailable. I have a lot of experience configuring rpc over http/s with sbs2003 but this is the first time for server 2003 standard. I have outlook ...

how SIN, COS and TAN works on sql server
I ran this: declare @f float set @f = 30 select sin(@f) and got -0.988031624092862 What is this? "Roy Goldhammer" <royg@yahoo.com> wrote in message news:eRKvnY0#KHA.5476@TK2MSFTNGP06.phx.gbl... > I ran this: > > declare @f float > > set @f = 30 > > select sin(@f) > > and got -0.988031624092862 > > What is this? > > The values for SIN, COS, and TAN are in radians, not degrees. You are seeing the value of SIN(30 radians), which is correct. -- Dan "Dan" <news@worldofspack.com...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

CRM Queries
Hi, I have a few queries that one of our customers has put to us. Can someone let me know their thoughts. Our customer wants to know whether it is possible to have something where within CRM you can view others users calendars? They would like to view it like you would in MS Outlook, the customer is going to be using the CRM Browser Client not Outlook for Sales. Also, within MS CRM I arn't sure whether this is a fault but I have managed to create 2 identical accounts, same name, contact numbers and account number. Is this what I should expect surely its something that needs fixing? D...

Address query
I would like to create a spreedsheet that will check street adresses entered in an Excel spreadsheet against a master street index and then produce a new result in a new column. As an example if Post Street is in the master strret index then the result in the new column is Zone 4. If Birchwood Drive is in the master street index then the result in the new coloum is Zone 3. Any suggestions? Thanks, -- Bob On Sat, 12 Nov 2005 12:06:01 -0800, Bob <Bob@discussions.microsoft.com> wrote: >I would like to create a spreedsheet that will check street adresses entered >in an Exc...

Summing in A Query
Hello, I have a database which fuel records are stored in. The data is stored in two tables. The first records the daily logs that operators use each time they fuel up. It stores their name, the key they used (keylock fuel system - it's ancient) the unit number of the equipment using the fuel, and the amount of fuel they took. The second table stores the month end information retrieved from the key lock print out. It keeps a running total of the amount of fuel taken with each key, and the operator using that key. We have problems making sure all of the fuel is accounted for each mon...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

QBF/Query on report
Hi folks, I have numerous reports that are based on different queries. What I am looking for is a way that if a user wants to pull a certain report, that when this report is selected from a form, the user has the option based on different fields to drill down the information being returned in the report. For example, one report would give details of all invoices to be paid (considered incomplete). I want to give the option to the user to select the from and to dates that the invoice was received for example. Or again, the user may want to search for all unpaid invoices that belong t...

Simple Access counting queries
Hi, hoping someone can help a relative newbie with a pretty simple query. My database (Access 2007) has three tables: Customers Products Purchases (many-to-one links to both of the other tables, this is basically a linking table) I have two simple queries I'd like to get out of this database, but I'm a bit stuck on how to construct the SQL. Any direction you can give me would be helpful. 1. List of all customers who have purchased 2 or more products (or 3 or more products, or 4+, etc.) 2. List of all customers who have purchased both Product A and Product B (or A, B, and C, or B an...

exchange 2003 query.
Dear support team. I have installed windows 2003 server with exchange 2003 server. I have configured active directoty with the name called as domain_abc.com When i am creating users in my active directory, it is asking to create exchange mailbox. After creating the mail box i have modified the users email address in active directory. (e:g nilesh@domain_abc.com to nilesh@home.com I have another domain in my lan as home.com when i send mail from my mail idm mail should go as nilesh@home.com I am using third party pop connector .There i have mentioned the domain name domain_abc.com. ...

my sql
hi, i am just wondering is MySQL any good as a user friendly programme, i know this is access butam just curious is it worth trying or is access as good paul -- paul@partydj4hire.com "Paul (OMAC HX)" <paul@www.omachx.com> wrote in message news:O6PIiggYIHA.5472@TK2MSFTNGP06.phx.gbl... > hi, > > i am just wondering is MySQL any good as a user friendly programme, i know > this is access butam just curious is it worth trying or is access as good > > paul We talking about apples and oranges. Mysql and ms-access have no relation to each other, and are n...

query ar remedy
I am trying to use the parameters to query ar remedy. When i run th query i get a error message Driver parameter missing. then it stops Any help is appreciated. I am using the AR odbc driver -- Message posted from http://www.ExcelForum.com I am also experiencing the same issue when running a parameter query using the AR ODBC driver. The parameter query returns results perfectly when run in MsQuery, but when the query attempts to return those same results to Excel, the Driver]parameter missing error is received. I have not been able to find anything online regarding this specific problem ...

Query to hide duplicate records
I recall that this used to easy in previous versions (Unique values only ??), but in 2007, I can't get this to work at all. I have a table with our companie's job numbers in it. The job numbers show up multiple times because of different phases of the project: ProjNum ProjDescription 3077 Univ. of Vermont/UC/LEED 3077 Univ. of Vermont/University Commons: Building Fee 3077 Univ. of Vermont/University Commons: Excess Professiona... I need to jut have a single listing of each project number, otherwise, I get repeated records in the query that looks at this information (which ...

"Expr 1014" if field of query ?
In attempting to troubleshoot some problems in a query, I noticed that three of the fields (design mode of Access 2000) showed "Expr 1014", "Expr 1015","Expr 1016". Data exists in the fields when I execute the query. What does this mean? Correction: three of the fields (Run mode of Access 2000) BobC wrote: > In attempting to troubleshoot some problems in a query, I noticed that > three of the fields (design mode of Access 2000) showed "Expr 1014", > "Expr 1015","Expr 1016". Data exists in the fields when I execute t...

Problem with vba code to export query result in excel
Hi, I have a access report that exports to excel with click of a button after choosing parameters. This works well. However I have to modify couple of fields to utilize formula in the export module. I am not sure how to do this. I am writing the above code which seems to cause problem. I appreciate any help to resolve this issue. Thanks. Code: If lngColumn = 12 Then xlc.Offset(0, lngColumn).Value = =([UnitPrice]*[OriginalShippedQty])/1000 End If It seems the fields UnitPrice and OrigianalShippedQty are not being recognized here Jack wrote: >Hi, >I have a acces...

How to create an "and" rule in Query Based Distribution Groups
Hi, With Exchange 2003 Query Based Distribution groups, is it possible to create an "and" rule? ie, all users who are based in "London" "and" have the first name "John"? Thanks, Curtis. -- Please reply to news group only. Thank you. Sure. (&(attribute1=blah)(attribute2=blah)) http://msdn.microsoft.com/library/en-us/adsi/adsi/search_filter_syntax.asp?frame=true -- Bharat Suneja MCSE, MCT www.zenprise.com blog: www.suneja.com/blog ----------------------------------- "Curtis Fray" <xxx@xxx.com> wrote in message news:OjVc...

sql command error
i have the below code to insert a file in a filestream table in sql server 2008, but when i run it i get an error that there is a error near the From word so maybe my sql statment have some syntext error ? connObject = New SqlConnection(My.Settings.AlTaybatConnectionString) connObject.Open() str = "DECLARE @img AS VARBINARY(MAX)" + "SELECT @img = CAST(bulkcolumn AS VARBINARY(MAX)" + _ "FROM OPENROWSET(BULK 'C:\Users\Ammar\Offer.doc',SINGLE_BLOB ) AS x" + _ "INSERT INTO Attachments (ID, SvrFi...

How can I time long running queries?
Hello all, Is it possible to time long running select queries? I was looking at creating some code to do this....something like: 1. Store current time in a time variable 2. Try to programmatically open a query ("open" meaning just like manually opening a select query from MS Access to view data) using "Application.CurrentDb.QueryDefs("Query3").OpenRecordset dbOpenTable". 3. Check the current time and compare it to the start time saved at the beginning But, I get "Run-time error '3219': Invalid operation." when I run "...OpenRecord...

SQL Inventory View
Does anyone have a SQL view that can show the following inventory information grouped at the date level with totals on the QTY and DOL fields? DATE LOCNCODE ITMCLSCD ITEMDESC ITEMNMBR BASEUOFM QTYPURCH QTYSOLD QTYADJ QTYTRANSFERED DOLPURCH DOLSOLD DOLADJ DOLTRANSFERED -- Christian S South Carolina Below is a view that helps to some extent of your req but not whole. I hope you can modify accordingly. USE [DYNAMICS] GO /****** Object: View [dbo].[san_ViewInventoryAgeing] Script Date: 04/01/2008 12:23:21 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO Create View [dbo].[san_Vi...

Excel / VBA / SQL DB
Anybody done any work with Excel / VBA / SQL DB? Can you give me some pointers on how I could do the following all in one VBA routine: 1) From a cell variable value (ie, user enters a customer code), I query table A and put the data into a worksheet starting from say cell A1. 2) A blank row is created after the last data line in point 1 above. 3) Using the same cell variable, query table B and put the data into the worksheet starting from the row after the blank row in point 2 above. and so on. Like I've said before I work with ASP all day and know about ADO connections, commands and...

Report on 3 Queries by Date
Hi I have 3 queries which I would like to join into one based on the servicedate/taskdate so that for any given date I can see the totals of each type of task we log for work. I would have joined these using a simple query however my issue is that not everyday I log an event in any one table (Each query based on only one table). Query1 ServiceDate CleanTimeTotal QATimeTotal ESTTimeTotal Query2 ServiceDate RepairTimeTotal Query3 TaskDate TaskTimeTotal Now I suspect I need to rename TaskDate to ServiceDate in Query3 above which I am okay to do but have no idea how to set up the...

Can I set SQL mirroring across WAN?
We have a 100 Mb dedicated DR link to another data center. Want to set up a SQL 2008 database to use mirroring from primary to a standby server at the remote data center. I did a ping test and here is the result. ping -n 100 -l 5120 <target IP> Ping statistics for <target IP> Packets: Sent = 100, Received = 99, Lost = 1 (1% loss), Approximate round trip times in milli-seconds: Minimum = 8ms, Maximum = 19ms, Average = 11ms is the network latency good enough for database mirroring? Does anyone know the min. requirement? Field experience will be fine too....

ODBC database query
I am working with an Access database to keep track of everyone's jobs at work. I have Excel files with macros that pull information from the database and display it in a report form. My problem is that I seem to be running out of space within the macros. I get to the database using an ODBC link. Once I filter the data and complete the macro, the filtered data displays perfectly, but if I save and close the file, then open it again, the macro contains errors. When I open the macro and look at the coding, it looks like it cuts off at a certain point as if I had filled my character quot...