reg. running query

[bimal13]

In regards to my first thread, I am currently working in MS Access and running an SQL query in MS access.This is how my query is:

SELECT analname,member_id,member_name,dob,sex,max(dos),spec_num,smc,testname,dxcode1,loinc,ndec_low,ndec_hi,rslt_dec,abn_code,
rsltabrv,cpt_code,pay_acct,acctname,elig_sw,ord_acct,docname from
diabetics group by
analname,member_id,member_name,dob,sex,spec_num,smc,testname,dxcode1,loinc,ndec_low,ndec_hi,rslt_dec,abn_code,
rsltabrv,cpt_code,pay_acct,acctname,elig_sw,ord_acct,docname;

DOS means date of service for which I need the latest date of service in the event of a duplicate.

Any help is highly appreciated.

Thanks

Bimal

 

[lespaul]

and does this query not do that for you? Does it return an error? What is wrong with it?

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[bimal13]

Hi Leslie,

Thankyou for your reply. The query is not able to sort by the latest date of service(dos) and is not able to filter out the member_id field for unique records.It should retain the record with the latest date of service(dos)in the event of a duplicate member_id and duplicate member_name.

Thanks

Regards

Bimal

 

[lespaul]

and is this different in some way from thread181-1426975? did you try the query in that post? does it do what you want?

 

[bimal13]

Hi Leslie,

This is the same query as given in the thread181-1426975: reg. removing duplicates with latest date and unique member ID

No, I could not get an answer to this query with what I tried. What I tried is posted in my second thread.

Thanks

Bimal

 

[lespaul]

did you try the query I posted in the other thread?

 

[bimal13]

sorry Leslie. I am new to this site. I dont see any query which you posted in replu to my thread.Please let me know how to access it.

Bimal

 

[lespaul]

If you click on this link:

Thread181-1426975

you will be redirected to your other post. In that post is a query that should return for each distinct ID and max date all the other information you need.

Leslie