A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Show One Record For Each Patient ID.



 
 
Thread Tools Display Modes
  #1  
Old March 2nd, 2010, 07:16 PM posted to microsoft.public.access.queries
BMC
external usenet poster
 
Posts: 18
Default Show One Record For Each Patient ID.

Hi
I have a table that store data when a Patient visits a Medical Centre.

I have setup a form to take information (Parameters) for a query to run and
all works well except I don’t know how to show only 1 record per Patients ID
(conpid), I don’t want all records to show for each Patient only 1 (last
would be ideal)

Any help would be appreciated.

Regards
Barry

Existing SQL (so far):-

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi

FROM tbl_consultations

WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*"))

ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

  #2  
Old March 2nd, 2010, 07:30 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Show One Record For Each Patient ID.

I am assuming that conpid is the patient ID, if not then you need to change
in the subquery.

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi
FROM tbl_consultations
WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*")) AND
tbl_consultations.conts = (SELECT Max([XX].[conts]) FROM tbl_consultations AS
[XX] WHERE [XX].[conpid] = tbl_consultations.conpid)
ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

--
Build a little, test a little.


"BMC" wrote:

Hi
I have a table that store data when a Patient visits a Medical Centre.

I have setup a form to take information (Parameters) for a query to run and
all works well except I don’t know how to show only 1 record per Patients ID
(conpid), I don’t want all records to show for each Patient only 1 (last
would be ideal)

Any help would be appreciated.

Regards
Barry

Existing SQL (so far):-

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi

FROM tbl_consultations

WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*"))

ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

  #3  
Old March 3rd, 2010, 03:09 PM posted to microsoft.public.access.queries
BMC
external usenet poster
 
Posts: 18
Default Show One Record For Each Patient ID.

Hi Karl

Many thanks, the script works perfect.

I will try and learn/inderstand how the Select Max.... works.

Regards
Barry

"KARL DEWEY" wrote:

I am assuming that conpid is the patient ID, if not then you need to change
in the subquery.

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi
FROM tbl_consultations
WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*")) AND
tbl_consultations.conts = (SELECT Max([XX].[conts]) FROM tbl_consultations AS
[XX] WHERE [XX].[conpid] = tbl_consultations.conpid)
ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

--
Build a little, test a little.


"BMC" wrote:

Hi
I have a table that store data when a Patient visits a Medical Centre.

I have setup a form to take information (Parameters) for a query to run and
all works well except I don’t know how to show only 1 record per Patients ID
(conpid), I don’t want all records to show for each Patient only 1 (last
would be ideal)

Any help would be appreciated.

Regards
Barry

Existing SQL (so far):-

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi

FROM tbl_consultations

WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*"))

ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

  #4  
Old March 3rd, 2010, 04:57 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Show One Record For Each Patient ID.

I will try and learn/inderstand how the Select Max.... works.
It is a subquery. The same thing could be done with a query joined to your
table in a query.

--
Build a little, test a little.


"BMC" wrote:

Hi Karl

Many thanks, the script works perfect.

I will try and learn/inderstand how the Select Max.... works.

Regards
Barry

"KARL DEWEY" wrote:

I am assuming that conpid is the patient ID, if not then you need to change
in the subquery.

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi
FROM tbl_consultations
WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*")) AND
tbl_consultations.conts = (SELECT Max([XX].[conts]) FROM tbl_consultations AS
[XX] WHERE [XX].[conpid] = tbl_consultations.conpid)
ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

--
Build a little, test a little.


"BMC" wrote:

Hi
I have a table that store data when a Patient visits a Medical Centre.

I have setup a form to take information (Parameters) for a query to run and
all works well except I don’t know how to show only 1 record per Patients ID
(conpid), I don’t want all records to show for each Patient only 1 (last
would be ideal)

Any help would be appreciated.

Regards
Barry

Existing SQL (so far):-

SELECT tbl_consultations.conid, tbl_consultations.conpid,
tbl_consultations.conpnf, tbl_consultations.conpnl,
tbl_consultations.ConpDOB, tbl_consultations.conpfn, tbl_consultations.conts,
tbl_consultations.condi

FROM tbl_consultations

WHERE (((tbl_consultations.conts) Between
Forms!frm_ParameterCollector!BeginningDate And
Forms!frm_ParameterCollector!EndingDate) And ((tbl_consultations.condi) Like
"*" & Forms!frm_ParameterCollector!SearchWord & "*"))

ORDER BY tbl_consultations.conpnf, tbl_consultations.conpnl;

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:10 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.