Show One Record For Each Patient ID.

  • Thread starter Thread starter BMC
  • Start date Start date
B

BMC

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;
 
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;
 
Hi Karl

Many thanks, the script works perfect.

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

Regards
Barry

KARL DEWEY said:
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 said:
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;
Click to expand...
Click to expand...
 
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 said:
Hi Karl

Many thanks, the script works perfect.

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

Regards
Barry

KARL DEWEY said:
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 said:
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;
Click to expand...
Click to expand...
Click to expand...
 
Back
Top