Query for unmatched records within field values?

[sueshe]

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[KARL DEWEY]

Use one of the expressions as criteria for the other with <> for not equal.

 

[sueshe]

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[KARL DEWEY]

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[sueshe]

ok, did that, but still giving me all records, not unmatched.


--
Tasha

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little


:

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[KARL DEWEY]

I did not follow your Left(Left(xxxxx
Why not just Left([PATNAME],5)
--
KARL DEWEY
Build a little - Test a little

ok, did that, but still giving me all records, not unmatched.


--
Tasha

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha


:

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little


:

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[sueshe]

Maybe I'm doing it the wrong way? Let me explain what I have and see if you
can tell me what I need to do...

I have two tables, one with a surgery log(that is keyed by hand in another
dept), and one from our system that generates all patients, with patient
number, name, admit date, etc. The surgery log, being keyed by hand has
errors in it, such as patient account numbers not corresponding to the
correct patient name, or misspelled names. Here is a hypothetical example:

patno surglogname sysname proceddt
5293 BASS RICK BRASS RICK 05/01/07
4802 HUNTER GINA HART DONALD 05/01/07
7088 DILL KIM DILLEN KIM 05/07/07

So, I set up the query, patno, and procedure date from the surgery log, then
created two expressions, one for SURGNAME and one for SYSNAME as follows to
give me the first 6 characters of the last name to tell me which patients
have the wrong patient name entered in the surgery log: (I was told the
Left(Left would give me names that that had less than 6 characters also????)

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),6)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

In datasheet view, this gives me all pts listed with first 6 characters of
their name. What I need is only the patients that don't match(misspellings,
wrong name etc.). I know I can create a table out of this query and then
create another query using unmatched to give me what I need, but am trying to
get it all into one query....so looking for how to set that up all within one
creary. Sorry to be so long....have spent hours trying to set this up and
looked through my Access book and have not found it yet. Thanks for your
help!




--
Tasha

I did not follow your Left(Left(xxxxx
Why not just Left([PATNAME],5)
--
KARL DEWEY
Build a little - Test a little

ok, did that, but still giving me all records, not unmatched.


--
Tasha

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little


:

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha


:

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little


:

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[KARL DEWEY]

Post your SQL statement. Open the query in design view, click on menu VIEW
- SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

Maybe I'm doing it the wrong way? Let me explain what I have and see if you
can tell me what I need to do...

I have two tables, one with a surgery log(that is keyed by hand in another
dept), and one from our system that generates all patients, with patient
number, name, admit date, etc. The surgery log, being keyed by hand has
errors in it, such as patient account numbers not corresponding to the
correct patient name, or misspelled names. Here is a hypothetical example:

patno surglogname sysname proceddt
5293 BASS RICK BRASS RICK 05/01/07
4802 HUNTER GINA HART DONALD 05/01/07
7088 DILL KIM DILLEN KIM 05/07/07

So, I set up the query, patno, and procedure date from the surgery log, then
created two expressions, one for SURGNAME and one for SYSNAME as follows to
give me the first 6 characters of the last name to tell me which patients
have the wrong patient name entered in the surgery log: (I was told the
Left(Left would give me names that that had less than 6 characters also????)

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),6)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

In datasheet view, this gives me all pts listed with first 6 characters of
their name. What I need is only the patients that don't match(misspellings,
wrong name etc.). I know I can create a table out of this query and then
create another query using unmatched to give me what I need, but am trying to
get it all into one query....so looking for how to set that up all within one
creary. Sorry to be so long....have spent hours trying to set this up and
looked through my Access book and have not found it yet. Thanks for your
help!




--
Tasha

I did not follow your Left(Left(xxxxx
Why not just Left([PATNAME],5)
--
KARL DEWEY
Build a little - Test a little

ok, did that, but still giving me all records, not unmatched.


--
Tasha


:

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little


:

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha


:

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little


:

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!

 

[sueshe]

I figured it out. I created a third expression, and noticed it was showing a
-1 on all pts that were unmatched, so I put in criteria -1 and it gave me
exactly what I needed, then changed that expression to not show in datasheet
view.

Here is my SQL statement:

SELECT [SURGERY LOG].[PATNO #], Left([Patient Name],6)<>Left([PATNAME],6) AS
NAMENOTEQ, Left(Left([Patient Name],InStr([Patient Name]," ")-1),6) AS
SURGNAME, Left(Left([PATNAME],InStr([PATNAME]," ")-1),6) AS SYSName, [SURGERY
LOG].[ProcedureDt], ALLPTS.[F/C], ALLPTS.HSV
FROM [SURGERY LOG] LEFT JOIN ALLPTS ON [SURGERY LOG].[PATNO]= ALLPTS.PATNO
WHERE (((Left([Patient Name],6)<>Left([PATNAME],6))=-1));

--
Tasha

Post your SQL statement. Open the query in design view, click on menu VIEW
- SQL View, highlight all, copy, and paste in a post.
--
KARL DEWEY
Build a little - Test a little

Maybe I'm doing it the wrong way? Let me explain what I have and see if you
can tell me what I need to do...

I have two tables, one with a surgery log(that is keyed by hand in another
dept), and one from our system that generates all patients, with patient
number, name, admit date, etc. The surgery log, being keyed by hand has
errors in it, such as patient account numbers not corresponding to the
correct patient name, or misspelled names. Here is a hypothetical example:

patno surglogname sysname proceddt
5293 BASS RICK BRASS RICK 05/01/07
4802 HUNTER GINA HART DONALD 05/01/07
7088 DILL KIM DILLEN KIM 05/07/07

So, I set up the query, patno, and procedure date from the surgery log, then
created two expressions, one for SURGNAME and one for SYSNAME as follows to
give me the first 6 characters of the last name to tell me which patients
have the wrong patient name entered in the surgery log: (I was told the
Left(Left would give me names that that had less than 6 characters also????)

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),6)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

In datasheet view, this gives me all pts listed with first 6 characters of
their name. What I need is only the patients that don't match(misspellings,
wrong name etc.). I know I can create a table out of this query and then
create another query using unmatched to give me what I need, but am trying to
get it all into one query....so looking for how to set that up all within one
creary. Sorry to be so long....have spent hours trying to set this up and
looked through my Access book and have not found it yet. Thanks for your
help!




--
Tasha

I did not follow your Left(Left(xxxxx
Why not just Left([PATNAME],5)
--
KARL DEWEY
Build a little - Test a little


:

ok, did that, but still giving me all records, not unmatched.


--
Tasha


:

Use this as criteria for the SURGNAME field ---
Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

--
KARL DEWEY
Build a little - Test a little


:

I tried using as criteria for the SURGNAME field <> SYSName
but gives me all records, what am I doing wrong?



--
Tasha


:

Use one of the expressions as criteria for the other with <> for not equal.
--
KARL DEWEY
Build a little - Test a little


:

Just to preface this, I am a very very big beginner, so in case I don't make
sense, I apologize ahead of time.

I have a query set up that includes the following fields

Patient Number and Patient Name, joined by patient name from two seperate
tables[SURGERY LOG] and [ALLPTS]. I set up two fields in the query to give
me the first 5 letters of the last name to compare for unmatched names in a
record (patient account numbers listed with wrong patient name).

I added the following two fields to give me this information:

SURGNAME: Left(Left([Patient Name],InStr([Patient Name]," ")-1),5)
SYSName: Left(Left([PATNAME],InStr([PATNAME]," ")-1),5)

This part of the query works fine, now I need to find out how to give me
only the records that don't match in the two patient name fields. I have
looked in the forum for hours and have yet to find out how to do this.....or
if it is even possible and I'm just chasing my tail!! Please help!