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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|