Query Question to Lookup

[Anthony]

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[KARL DEWEY]

Left([YourNameField], InStr([YourNameField], ",")-1)

 

[Anthony]

I'm having some trouble with that, it still shows no records. Under what
record do I place this under?

Left([YourNameField], InStr([YourNameField], ",")-1)
--
KARL DEWEY
Build a little - Test a little

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[KARL DEWEY]

Under what record do I place this under?
What I posted is a calculated field.

Post your query SQL and I will edit it for you.

--
KARL DEWEY
Build a little - Test a little

I'm having some trouble with that, it still shows no records. Under what
record do I place this under?

Left([YourNameField], InStr([YourNameField], ",")-1)
--
KARL DEWEY
Build a little - Test a little

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[John W. Vinson]

I'm having some trouble with that, it still shows no records. Under what
record do I place this under?

Not *under* any record (or field) - instead put it in a vacant Field cell in
your query design window, or (with an = sign in front of it) in the Control
Source of a form or report textbox.

 

[Anthony]

SELECT DBA_USERS.userName, [term-week].Last_Name, [term-week].First_name
FROM DBA_USERS INNER JOIN [term-week] ON DBA_USERS.userName =
[term-week].Last_Name
GROUP BY DBA_USERS.userName, [term-week].Last_Name, [term-week].First_name
HAVING
(((DBA_USERS.userName)=Left("DBA_USERS.userName",InStr([term-week.Last_Name],",")-1)));

What I posted is a calculated field.

Post your query SQL and I will edit it for you.

--
KARL DEWEY
Build a little - Test a little

I'm having some trouble with that, it still shows no records. Under what
record do I place this under?

Left([YourNameField], InStr([YourNameField], ",")-1)
--
KARL DEWEY
Build a little - Test a little


:

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[John Spencer]

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

You added quotes where there should not have been and you looked for the
comma in the wrong table.
This is one way to do it.
SELECT DBA_USERS.userName, [term-week].Last_Name, [term-week].First_name
FROM DBA_USERS, [term-week]
WHERE
((([term-week].Last_Name)=Left([DBA_USERS].[userName],InStr([DBA_USERS].[userName],",")-1)));

Another way is to use a first query to extract the last name and then use
that query joined in the second query.

--
KARL DEWEY
Build a little - Test a little

SELECT DBA_USERS.userName, [term-week].Last_Name, [term-week].First_name
FROM DBA_USERS INNER JOIN [term-week] ON DBA_USERS.userName =
[term-week].Last_Name
GROUP BY DBA_USERS.userName, [term-week].Last_Name, [term-week].First_name
HAVING
(((DBA_USERS.userName)=Left("DBA_USERS.userName",InStr([term-week.Last_Name],",")-1)));

Under what record do I place this under?

What I posted is a calculated field.

Post your query SQL and I will edit it for you.

--
KARL DEWEY
Build a little - Test a little

I'm having some trouble with that, it still shows no records. Under what
record do I place this under?

:

Left([YourNameField], InStr([YourNameField], ",")-1)
--
KARL DEWEY
Build a little - Test a little


:

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[Anthony]

Thanks, that worked. I have one more complex design, how can I only look at
the first letter after the comma. So for example, test, john. I want to
look for test, j.
Here is my SQL.
SELECT *
FROM DBA_USERS INNER JOIN [term-week] ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*";

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[John Spencer]

I'm sorry, but I can't make sense out of your request. Are you saying that
the last_Name field contains last name and a comma and the initial letter of
the first name? Or perhaps there is a first name field that has that information?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, that worked. I have one more complex design, how can I only look at
the first letter after the comma. So for example, test, john. I want to
look for test, j.
Here is my SQL.
SELECT *
FROM DBA_USERS INNER JOIN [term-week] ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*";

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[Anthony]

I do have a field that has the first name also, it;s just in the field that I
want to query is in the field that has the lastname, firstname under the
field UserName and then I have another table that has the firstname under a
field called First_Name and a last name under a field called Last_Name. I
want to query the first name also like you sohowed me now to do the last name
together. Where it could lookup the at the last name and then just the first
inital or first name of that field that has both. I hope that it helps, my
problem is with a last name smith it pulls everyone with the last name smith.
If it could look at the whole last name which it is doing now and look after
that at the first name after the comma. I would get less results and know
who to remove.

Thanks

I'm sorry, but I can't make sense out of your request. Are you saying that
the last_Name field contains last name and a comma and the initial letter of
the first name? Or perhaps there is a first name field that has that information?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, that worked. I have one more complex design, how can I only look at
the first letter after the comma. So for example, test, john. I want to
look for test, j.
Here is my SQL.
SELECT *
FROM DBA_USERS INNER JOIN [term-week] ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*";

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Anthony wrote:
I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[John Spencer]

PERHAPS the following.
Assumption: UserName has a comma and a space followed by the first name.
This attempts to match on the first two characters in the FirstName.


SELECT *
FROM DBA_USERS INNER JOIN [term-week]
ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*" AND
DBA_Users.UserName Like "*, " & Left([Term-Week].[FirstName],2) & "*"

To attempt to match the entire first name try

SELECT *
FROM DBA_USERS INNER JOIN [term-week]
ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*" AND
DBA_Users.UserName Like "*, " & [Term-Week].[FirstName]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I do have a field that has the first name also, it;s just in the field that I
want to query is in the field that has the lastname, firstname under the
field UserName and then I have another table that has the firstname under a
field called First_Name and a last name under a field called Last_Name. I
want to query the first name also like you sohowed me now to do the last name
together. Where it could lookup the at the last name and then just the first
inital or first name of that field that has both. I hope that it helps, my
problem is with a last name smith it pulls everyone with the last name smith.
If it could look at the whole last name which it is doing now and look after
that at the first name after the comma. I would get less results and know
who to remove.

Thanks

I'm sorry, but I can't make sense out of your request. Are you saying that
the last_Name field contains last name and a comma and the initial letter of
the first name? Or perhaps there is a first name field that has that information?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks, that worked. I have one more complex design, how can I only look at
the first letter after the comma. So for example, test, john. I want to
look for test, j.
Here is my SQL.
SELECT *
FROM DBA_USERS INNER JOIN [term-week] ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*";


:

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Anthony wrote:
I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony

 

[Anthony]

Thanks, that works great. Just what I needed. Thanks again, Anthony

PERHAPS the following.
Assumption: UserName has a comma and a space followed by the first name.
This attempts to match on the first two characters in the FirstName.


SELECT *
FROM DBA_USERS INNER JOIN [term-week]
ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*" AND
DBA_Users.UserName Like "*, " & Left([Term-Week].[FirstName],2) & "*"

To attempt to match the entire first name try

SELECT *
FROM DBA_USERS INNER JOIN [term-week]
ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*" AND
DBA_Users.UserName Like "*, " & [Term-Week].[FirstName]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I do have a field that has the first name also, it;s just in the field that I
want to query is in the field that has the lastname, firstname under the
field UserName and then I have another table that has the firstname under a
field called First_Name and a last name under a field called Last_Name. I
want to query the first name also like you sohowed me now to do the last name
together. Where it could lookup the at the last name and then just the first
inital or first name of that field that has both. I hope that it helps, my
problem is with a last name smith it pulls everyone with the last name smith.
If it could look at the whole last name which it is doing now and look after
that at the first name after the comma. I would get less results and know
who to remove.

Thanks

I'm sorry, but I can't make sense out of your request. Are you saying that
the last_Name field contains last name and a comma and the initial letter of
the first name? Or perhaps there is a first name field that has that information?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Anthony wrote:
Thanks, that worked. I have one more complex design, how can I only look at
the first letter after the comma. So for example, test, john. I want to
look for test, j.
Here is my SQL.
SELECT *
FROM DBA_USERS INNER JOIN [term-week] ON [DBA_USERS].[userName] LIKE
[term-week].[Last_Name] & ",*";


:

The SQL for such a query might look like the following.
TableOne is table with LastName + Comma + FirstName
TableTwo is table with LastName only.

SELECT *
FROM [TableOne] Inner JOIN [TableTwo]
ON [TableOne].[CommaName] LIKE [TableTwo].[LastName] & ",*"

You can build most of this with the design view.
-- add both tables
-- join CommaName to LastName by dragging from one to the other
-- select the fields you want to see
-- Select View: SQL from the menu
-- Edit the on clause of the SQL statement so it looks like the On clause above.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Anthony wrote:
I have a two table one that has users listed as test, john in one cell and I
have a list the just list the last name. I was wondering how can I setup a
query that is the last name is in table one that it only looks at test, and
after the comma stops looking? Any ideas?

Thanks, Anthony