Querie to list Patient Details

  • Thread starter Thread starter Barry McConomy
  • Start date Start date
B

Barry McConomy

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




John Spencer said:
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


Barry McConomy said:
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
Click to expand...
Click to expand...
 
Hi John

Cancel this request.

I think I have sorted, I followed your instructions to "Double click the
grey......"

Regards
Barry


Barry McConomy said:
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




John Spencer said:
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


Barry McConomy said:
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
Click to expand...
Click to expand...
Click to expand...
 
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


Barry McConomy said:
Hi John

Cancel this request.

I think I have sorted, I followed your instructions to "Double click the
grey......"

Regards
Barry


Barry McConomy said:
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




John Spencer said:
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

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
Click to expand...
Click to expand...
Click to expand...
 
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.
Barry McConomy said:
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


Barry McConomy said:
Hi John

Cancel this request.

I think I have sorted, I followed your instructions to "Double click the
grey......"

Regards
Barry


Barry McConomy said:
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.

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

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
Click to expand...
Click to expand...
Click to expand...
 
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

John Spencer said:
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.
Barry McConomy said:
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


Barry McConomy said:
Hi John

Cancel this request.

I think I have sorted, I followed your instructions to "Double click the
grey......"

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.

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

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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top