Lookup Fields

G

Guest

I need Access to lookup "First Name" and "Last Name" in a Table (Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can use this
for sorting and drop in boxes.
 
A

Allen Browne

Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name] AS
FullName
FROM Students ORDER BY [Last Name], [First Name];
 
G

Guest

Bob THanks again. THis does a fantastic Job dropping the value in the form at
the time of entry what is just what I was looking for, however it does not
seem to be putting it into the 'Student' table. I use this data for drop
boxes in the form. Can you help me correct this?

Bob Miller said:
I wouldn't do this in a table. Use a query and base your sorting and
drop boxes on the query. Place a field in your query:
Name:[Last Name] & ", " & [First Name]
Also, consider changing your field names to FirstName and LastName. It
is jsut easier in the long run to not have spaces in field names for a
number of reasons, one being you don't have to place brackets around
them as I did in the above.
Pastor said:
I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe
-
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use this
for sorting and drop in boxes.
 
G

Guest

Hi

I have a similar problem. But my LastName, FirstName lists are on a seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Allen Browne said:
Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name] AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Pastor Rod said:
I need Access to lookup "First Name" and "Last Name" in a Table (Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can use
this
for sorting and drop in boxes.
 
A

Allen Browne

Create a query that does what you want.

Then switch it to SQL View (View menu, from query design.)

You are now looking at an example of the query statement you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rigby said:
I have a similar problem. But my LastName, FirstName lists are on a
seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Allen Browne said:
Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts
the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name]
AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

Pastor Rod said:
I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use
this for sorting and drop in boxes.
 
G

Guest

Allen, Thank you

I managed to get the query to work fine (thanks to your help), BUT now i
need to implement it into a field on a form, so once i have typed in an ID
number in the one field, the next field will produce the lastname, firstname
relevant to the unique ID number entered.

i keep getting an error message in the field saying: #Name?
any ideas??
Thanks

Allen Browne said:
Create a query that does what you want.

Then switch it to SQL View (View menu, from query design.)

You are now looking at an example of the query statement you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rigby said:
I have a similar problem. But my LastName, FirstName lists are on a
seperate
table. I tried using your criteria in a new query but it gives me an error
message regarding the syntax 'SELECT'. any ideas??

Rigby
Thanks

Allen Browne said:
Don't. Too much maintenance.

Instead, use a query as the RowSource for the query. This example puts
the
StudentID in the first column, and then the combined names in the visible
column:

SELECT [StudentID], Students.[First Name] & " " & Students.[Last Name] AS
FullName
FROM Students ORDER BY [First Name], [Last Name];

For surname first, use:

SELECT [StudentID], Students.[Last Name] & ", " + Students.[First Name]
AS
FullName
FROM Students ORDER BY [Last Name], [First Name];

I need Access to lookup "First Name" and "Last Name" in a Table
(Students)
and insert the result into the field "Name" in the same table (ie Joe -
'First Name', Bloggs - 'Last Name' = 'Name' - Bloggs, Joe ) So I can
use
this for sorting and drop in boxes.
 
A

Allen Browne

There are too many unknowns to be able to give you an answer to this
question.

#Name means that Access can't make sense of what you are referring to in the
Control Source of your control. If it is a field, it may be misspelled. If
it is an expression, it must start with =, and all the names have to make
sense to Access. Square brackets are needed if the names contain spaces, or
other strange characters.

It is also unclear whether you are trying to navigate to record (find a
match), or copy the fields from somewhere into your form. In general, you
don't want to copy the fields from one table to another.
 
G

Guest

firstly, thank you for your time and help.

here it is in full...
Status (table): 'Surname' field; 'First Name' field; 'ID no' primary index
field

M/V Alaska (table): 'ID no' field; 'Security Officer' field.

I want my form (also named M/V Alaska) to in produce the 'Security Officer'
field, the 'Surname, First Name' answer relevant to the unique ID no which i
entre in the previous field. so i want it to extract the details from the
'status' table.
Does that make sense? I am new and so confused by this problem. if you need
more info, please ask.
Thank you
Rigby
 
A

Allen Browne

I see you have posted this as a separate thread in other groups, so will
leave the discussion to there.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top