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