 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / November 2005Querie to list Patient Details
Hi I have a table that lists all patient visits (Patient names & ID duplicated). Can anybody advise how I can:- 1) Remove all the records where the field name "ChartNumber" begins with the letter "R" 2) Then list the patient name once only Regards Barry Remove all records where ChartNumber begins with "R" - you don't want these records. SELECT Distinct [PatientName] FROM [TableVisits] WHERE [ChartNumber] NOT Like "R*" If you are using the query grid Field: PatientName Show: Checked Field: ChartNumber Show: Not Checked Criteria: NOT Like "R*" Double Click on the gray area at the top and in the properties box set Unique Values to Yes > Hi > [quoted text clipped - 9 lines] > Regards > Barry John Thanks for replying. I am struggling with the unique records part. Can you help. I need only one record per "tbl_consultations.conpid", Here is my SQL:- SELECT tbl_consultations.conid, tbl_consultations.conpid, Demographic.[Name First], Demographic.[Name Last], Demographic.[Home Phone], Demographic.[File #], Demographic.[Search Key] FROM tbl_consultations INNER JOIN Demographic ON tbl_consultations.conid = Demographic.[System ID] WHERE (((Demographic.[Search Key]) Not Like "R*")); Regards Barry > Remove all records where ChartNumber begins with "R" - you don't want > these records. [quoted text clipped - 27 lines] >> Regards >> Barry Hi John Cancel this request. I think I have sorted, I followed your instructions to "Double click the grey......" Regards Barry > John > [quoted text clipped - 49 lines] >>> Regards >>> Barry John Help I am not able to get unique records relating to the field "tbl_consultations.conpfn" This field stores a common file number and I only want one record to appear for each file number. Here is the SQL I have at this time:- SELECT tbl_consultations.conid, tbl_consultations.conpid, tbl_consultations.conpfn, tbl_consultations.conpcn, Demographic.[Name First], Demographic.[Name Last], Demographic.[Search Key], Demographic.[Home Phone], Demographic.[Birth Date] FROM tbl_consultations INNER JOIN Demographic ON tbl_consultations.conpfn = Demographic.[File #] WHERE (((Demographic.[Search Key]) Not Like "R*")); Regards Barry > Hi John > [quoted text clipped - 59 lines] >>>> Regards >>>> Barry Well, since I can't see your table structure I am at a disadvantage on helping you with this. SELECT DISTINCT tbl_consultations.conid, tbl_consultations.conpid, tbl_consultations.conpfn, tbl_consultations.conpcn, Demographic.[Name First], Demographic.[Name Last], Demographic.[Search Key], Demographic.[Home Phone], Demographic.[Birth Date] FROM tbl_consultations INNER JOIN Demographic ON tbl_consultations.conpfn = Demographic.[File #] WHERE (((Demographic.[Search Key]) Not Like "R*")); This will return one row for each combination of values. But if ConID for example has two values for a value of ConPFN then you will get two rows returned one for each combination of ConID and ConPFN. You might post a copy of two rows that you consider duplicates and a row of what you expect to see instead of the two rows. That might help to understand your problem. > John > [quoted text clipped - 82 lines] >>>>> Regards >>>>> Barry John Thanks for your help. I used your SQL code and removed the fields "conid" and "conpid" (I did not require this data for my report) and all worked fine. Again many thanks for your help. Regards Barry > Well, since I can't see your table structure I am at a disadvantage on > helping you with this. [quoted text clipped - 102 lines] >>>>>> Regards >>>>>> Barry |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.