Concatenation of 2 fields to produce a lookup table

[Graham A]

I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

 

[junito]

I have two separate fields in an employee records table for 'FirstName'
and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind
of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

 

[Douglas J. Steele]

I'd be curious to see the SQL associated with the query you've already got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

 

[MikeB]

I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

I'm not sure I fully understand your question. If you want to populate
(for instance) a Combo Box with correct names, then I have a Query
that does that in the Query field of the Drop-down list. It goes as
follows

SELECT PlayerID, [FirstName] & " " & [LastName] as [Name] From
[Employees]

You can then handle the "NotInList" event and parse the input the user
typed, then do an insert into your table and requery the combobox. I'm
not sure how you will handle the parsing if there are multiple parts
to a last name or first name (eg. Jan De Boer). Perhaps you can create
a pop-up form to ask specifically for the first name and last name in
two fields and then perform the insert.

Perhaps this will get you on the way until a more competent authority
responds.

M

 

[Graham A]

Thanks for the info Douglas - hopefully I will get the time to try this
tomorrow. I would gladly copy the query I was using before to you, to satisfy
your curiosity, but I deleted it (and its predecessors) when it didn't work
the way I wanted it to! I think I tried various versions of Make Table and
Update Table Queries....

I'd be curious to see the SQL associated with the query you've already got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind
of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

 

[Graham A]

Mike Thanks for the info - hopefully I will get the time to try this out
tomorrow. I'll let you know how I get on...

I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

I'm not sure I fully understand your question. If you want to populate
(for instance) a Combo Box with correct names, then I have a Query
that does that in the Query field of the Drop-down list. It goes as
follows

SELECT PlayerID, [FirstName] & " " & [LastName] as [Name] From
[Employees]

You can then handle the "NotInList" event and parse the input the user
typed, then do an insert into your table and requery the combobox. I'm
not sure how you will handle the parsing if there are multiple parts
to a last name or first name (eg. Jan De Boer). Perhaps you can create
a pop-up form to ask specifically for the first name and last name in
two fields and then perform the insert.

Perhaps this will get you on the way until a more competent authority
responds.

M

 

[Graham A]

Haven't had the chance to try this out until today and I can't get it to work
- maybe there is something I have misundertsood (I'm new at this!)

I am assuming that I am supposed to be enetering the expression in the
'Criteria' filed of a Select Query:

First I entered:
SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY
[FirstName], [LastName]
I got the error message "The syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parenthesis"

I then amended it to:
(SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
This gave the error message "order by CLAUSE ([FirstName]) conflicts with
DISTINCT"

So then I tried:
(SELECT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
Just to see if DISTINCT was the cause of the problem (as there aren't any
duplicates in the table yet) and the expression would work without it. I then
got the error message "At most one record can be returned by this subquery"
which didn't seem to make any sense at all...

There is obviously something fundamental that I am doing wrong - can you
explain what it is please?

Many thanks

Graham A

I'd be curious to see the SQL associated with the query you've already got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have two separate fields in an employee records table for 'FirstName' and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if the
query is run manually - this method also produces various error messages
concerning amending or deleting records (or the table) which would alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some kind
of
Event action to trigger the update but I cannot find out how to make this
work without being 'stalled' by the error messages which the user may not
respond to correctly.

Can anyone explain how this can be done please?

 

[Douglas J. Steele]

What you're showing is a SQL string. You can't use it as the criteria for
another Select query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Haven't had the chance to try this out until today and I can't get it to
work
- maybe there is something I have misundertsood (I'm new at this!)

I am assuming that I am supposed to be enetering the expression in the
'Criteria' filed of a Select Query:

First I entered:
SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY
[FirstName], [LastName]
I got the error message "The syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parenthesis"

I then amended it to:
(SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
This gave the error message "order by CLAUSE ([FirstName]) conflicts with
DISTINCT"

So then I tried:
(SELECT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
Just to see if DISTINCT was the cause of the problem (as there aren't any
duplicates in the table yet) and the expression would work without it. I
then
got the error message "At most one record can be returned by this
subquery"
which didn't seem to make any sense at all...

There is obviously something fundamental that I am doing wrong - can you
explain what it is please?

Many thanks

Graham A

I'd be curious to see the SQL associated with the query you've already
got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have two separate fields in an employee records table for 'FirstName'
and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use
on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if
the
query is run manually - this method also produces various error
messages
concerning amending or deleting records (or the table) which would
alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some
kind
of
Event action to trigger the update but I cannot find out how to make
this
work without being 'stalled' by the error messages which the user may
not
respond to correctly.

Can anyone explain how this can be done please?

 

[Graham A]

Oh dear, we are obiviously misunderstanding one another here! The SQL string
is basically the one you recommended, with the table name changed to suit my
database. I wrote in my alsy response that "I am assuming that I am supposed
to be entering the expression in the 'Criteria' filed of a Select Query",
which from your comment below is obviously wrong, but you didn't say where I
was supposed to put it - can you please explain where it should go?

Many thanks

Graham A

What you're showing is a SQL string. You can't use it as the criteria for
another Select query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Haven't had the chance to try this out until today and I can't get it to
work
- maybe there is something I have misundertsood (I'm new at this!)

I am assuming that I am supposed to be enetering the expression in the
'Criteria' filed of a Select Query:

First I entered:
SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY
[FirstName], [LastName]
I got the error message "The syntax of the subquery in this expression is
incorrect. Check the subquery's syntax and enclose the subquery in
parenthesis"

I then amended it to:
(SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
This gave the error message "order by CLAUSE ([FirstName]) conflicts with
DISTINCT"

So then I tried:
(SELECT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
Just to see if DISTINCT was the cause of the problem (as there aren't any
duplicates in the table yet) and the expression would work without it. I
then
got the error message "At most one record can be returned by this
subquery"
which didn't seem to make any sense at all...

There is obviously something fundamental that I am doing wrong - can you
explain what it is please?

Many thanks

Graham A

I'd be curious to see the SQL associated with the query you've already
got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two separate fields in an employee records table for 'FirstName'
and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for use
on
another area on the database to enable the user to select who has been
allocated a task.

I can generate such a table with an expression in a query but only if
the
query is run manually - this method also produces various error
messages
concerning amending or deleting records (or the table) which would
alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated atuomatically
anytime a new employee is added - I know that I will need to use some
kind
of
Event action to trigger the update but I cannot find out how to make
this
work without being 'stalled' by the error messages which the user may
not
respond to correctly.

Can anyone explain how this can be done please?

 

[Douglas J. Steele]

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM Auditors
ORDER BY [FirstName], [LastName]

should be the complete SQL statement you need (although you might want to
Alias the field, so that it's

SELECT DISTINCT [FirstName] & " " & [LastName] AS FullName
FROM Auditors
ORDER BY [FirstName], [LastName]

The data from the Auditors table should not be stored in any other table
(you mention you "want to combine them to appear in a lookup table").
Instead, that SQL should be the RowSource for the combo (or list) box.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Oh dear, we are obiviously misunderstanding one another here! The SQL
string
is basically the one you recommended, with the table name changed to suit
my
database. I wrote in my alsy response that "I am assuming that I am
supposed
to be entering the expression in the 'Criteria' filed of a Select Query",
which from your comment below is obviously wrong, but you didn't say where
I
was supposed to put it - can you please explain where it should go?

Many thanks

Graham A

What you're showing is a SQL string. You can't use it as the criteria for
another Select query.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Haven't had the chance to try this out until today and I can't get it
to
work
- maybe there is something I have misundertsood (I'm new at this!)

I am assuming that I am supposed to be enetering the expression in the
'Criteria' filed of a Select Query:

First I entered:
SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors ORDER BY
[FirstName], [LastName]
I got the error message "The syntax of the subquery in this expression
is
incorrect. Check the subquery's syntax and enclose the subquery in
parenthesis"

I then amended it to:
(SELECT DISTINCT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
This gave the error message "order by CLAUSE ([FirstName]) conflicts
with
DISTINCT"

So then I tried:
(SELECT [FirstName] & " " & [LastName] FROM Auditors
ORDER BY [FirstName], [LastName])
Just to see if DISTINCT was the cause of the problem (as there aren't
any
duplicates in the table yet) and the expression would work without it.
I
then
got the error message "At most one record can be returned by this
subquery"
which didn't seem to make any sense at all...

There is obviously something fundamental that I am doing wrong - can
you
explain what it is please?

Many thanks

Graham A




:

I'd be curious to see the SQL associated with the query you've already
got.

Using

SELECT DISTINCT [FirstName] & " " & [LastName]
FROM MyTable
ORDER BY [FirstName], [LastName]

or

SELECT DISTINCT [LastName] & ", " & [FirstName]
FROM MyTable
ORDER BY [LastName], [FirstName]

should work fine.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have two separate fields in an employee records table for
'FirstName'
and
'LastName' and I want to combine them to appear in a lookup table as
'Firstname Lastname' (e.g. 'John Smith', George Brown' etc) - for
use
on
another area on the database to enable the user to select who has
been
allocated a task.

I can generate such a table with an expression in a query but only
if
the
query is run manually - this method also produces various error
messages
concerning amending or deleting records (or the table) which would
alarm
and
/ or confuse the user.

The table used for the lookup source needs to be updated
atuomatically
anytime a new employee is added - I know that I will need to use
some
kind
of
Event action to trigger the update but I cannot find out how to make
this
work without being 'stalled' by the error messages which the user
may
not
respond to correctly.

Can anyone explain how this can be done please?