Join Query Expression

[GMD]

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[Jerry Whittle]

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.

 

[GMD]

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[Jerry Whittle]

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[GMD]

I get an invalid procedure call

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[GMD]

Anbody have any other ideas?

I get an invalid procedure call

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

:

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[John Spencer]

INVALID Procedure call could be cause by there being no comma in the
name field or the name field being blank.

You might experiment with either the Comments.Name field or the
Sample.Debtor field in a separate query and see if you can get the name
field in one or the other to match the structure of its equivalent field.

See if this query gives you an error or if it works.
SELECT Comments.Name,
Mid(Comments.Name, Instr(1,Comments.Name & ",",",")+1) & " " as FNMI
FROM Comments

Then Try this one to see if it works or if it errors.
SELECT Comments.Name
, Left(Comments.Name,Instr(1,Comments.Name & ",",",")-1) as LN
FROM Comments

If they work then you can build a query along the lines of Jerry
Whittle's suggestion. If they fail, you can play with them until the
expression(s) work and then build a query like Jerry Whittle's.

Otherwise you might consider building a VBA function to allows you to
pull out the parts of the names as separate entities. Take a look at
the split function.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Anbody have any other ideas?

I get an invalid procedure call

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

:

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.

 

[GMD]

Thanks to the both of you, Jerry and John, with your help the query is now
working.

INVALID Procedure call could be cause by there being no comma in the
name field or the name field being blank.

You might experiment with either the Comments.Name field or the
Sample.Debtor field in a separate query and see if you can get the name
field in one or the other to match the structure of its equivalent field.

See if this query gives you an error or if it works.
SELECT Comments.Name,
Mid(Comments.Name, Instr(1,Comments.Name & ",",",")+1) & " " as FNMI
FROM Comments

Then Try this one to see if it works or if it errors.
SELECT Comments.Name
, Left(Comments.Name,Instr(1,Comments.Name & ",",",")-1) as LN
FROM Comments

If they work then you can build a query along the lines of Jerry
Whittle's suggestion. If they fail, you can play with them until the
expression(s) work and then build a query like Jerry Whittle's.

Otherwise you might consider building a VBA function to allows you to
pull out the parts of the names as separate entities. Take a look at
the split function.
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Anbody have any other ideas?

I get an invalid procedure call

:

Try this:

SELECT [COMMENTS].[name],
[SAMPLE].[DEBTOR]
FROM [COMMENTS], [SAMPLE]
WHERE [SAMPLE].[DEBTOR]
=Mid([name],(InStr([name],",")+1)) & " "
& Left([name],(InStr([name],",")-1)) ;
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Jerry, thanks for the response. I am having trouble with the syntax though.
the first table is COMMENTS, the field Name is in the order of Last,FirstMI.
The second table is SAMPLE, the field DEBTOR is in the order of FirstMILast.

:

SELECT EmpNameLastFirstMI.txtEmpNameLastFirstMI,
EmpNameFirstMiLast.txtEmpNameFirstMiLast
FROM EmpNameLastFirstMI, EmpNameFirstMiLast
WHERE EmpNameFirstMiLast.txtEmpNameFirstMiLast
=Mid([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")+1)) & " "
& Left([txtEmpNameLastFirstMI],(InStr([txtEmpNameLastFirstMI],",")-1));

Basically you need to rearrange the name in one table to match the other.
However if the layout of the name in either table is different, such as a
period after the middle initial or a space after the comma, the above won't
work. You may need to use code to get as many matches as possible then
manually fix the problem records. Then there's the problem of having more
than one Jim J Jones in either table....

If you are getting this data from another system, consider talking to them
to see if they can reformat their export to you so that both tables match.
--
Jerry Whittle - MS Access MVP 2007 - 2009
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder



:

Trying to join employee data from two tables with the employee names in
different order. Table 1 has employee name in this order: Brown,John A;
Table 2 has employee name in this order: John A Brown. Any help would be
greatly appreciated.