Showing two fields from one table in one field on another

[weircolin]

Hi There

I have a database which has the means to record people's attendance at
events. I have set this us, through guidance, by having three tables,
Main person list, Events and Attended.

It is set up intiially that the Attended Table has got two "Lookups"
from it, one getting the people's names from the main table (well
query of this to sort them) and the other is the events table.

I have queries and forms controlling this data.

When the field on the "Attended" table that stores the person's name
is used, it'll only store theirfirst name. I have set it up so people
can go back and review who attended what event and this would then be
more difficult to do when there is only the person's first name
showing. Therefore I set the following in the Row Source of the table

SELECT [Members Names].FirstName+' '+[Members Names].LastName,
[Members Names].LastName, [Members Names].PersonID FROM [Members
Names];

This then works fine and both names now apprear in the field.
However, I have set it that there is a subform on each person's record
that displays a list of events that they have attended. When I leave
it that the Person field only displays one name, this works, but when
I change it to the above, the subform doesn't work when I select a new
event for the person.

Does anyone have any thoughts on what could be wrong here?

Thanks

Colin

 

[weircolin]

I have now tried driving it off the table rather than the query so the
names are slightly different from the above expression, now when I try
to open the main form for view records I get the following message:

"The expressing is typed incorrectly, or it is too complex to be
evaluated. For example, a numeric expression may contain too many
complicated elements. Try simplifying the expression by assigning
part of the expression to variables."

My expression is

SELECT [Lanarkshire Links members].PersonID, [Lanarkshire Links
members].FirstName & ' ' & [Lanarkshire Links Members].LastName,
[Lanarkshire Links members].LastName FROM [Lanarkshire Links
members];

I'm assuming from the above that it doesn't like the expression, but
don't know how to fix it. Is there a way that both names could be
stored in two collumns but the user would only need to select one of
their names?

Cheers

Colin (Confused)

 

[tina]

if you truly have "Lookups" in a *table*, i strongly urge you to get rid of
them. for more information, see http://www.mvps.org/access/lookupfields.htm.

you can use a combo box control at the *form* level to show the names of
your attendees; just make sure that the bound column of the control is the
PersonID - and you can hide that column, so only the names show, by setting
the column width to zero (0"). ditto events. if you're not familiar with
the properties of combo box controls, suggest you read up on them in Access
Help, so you'll understand how to get the most out of using them.

hth

 

[Bob Quintal]

@v57g2000hse.googlegroups.co
m:

Hi There

I have a database which has the means to record people's
attendance at events. I have set this us, through guidance, by
having three tables, Main person list, Events and Attended.

It is set up intiially that the Attended Table has got two
"Lookups" from it, one getting the people's names from the main
table (well query of this to sort them) and the other is the
events table.

I have queries and forms controlling this data.

When the field on the "Attended" table that stores the person's
name is used, it'll only store theirfirst name. I have set it up
so people can go back and review who attended what event and this
would then be more difficult to do when there is only the person's
first name showing. Therefore I set the following in the Row
Source of the table

SELECT [Members Names].FirstName+' '+[Members Names].LastName,
[Members Names].LastName, [Members Names].PersonID FROM [Members
Names];

This then works fine and both names now apprear in the field.
However, I have set it that there is a subform on each person's
record that displays a list of events that they have attended.
When I leave it that the Person field only displays one name, this
works, but when I change it to the above, the subform doesn't work
when I select a new event for the person.

Does anyone have any thoughts on what could be wrong here?

Thanks

Colin

The three tables concept is good, but the People and Events tables
should have a PersonID and EventID column, respectively, usually an
autonumber. The Attended table then is built to accept the IDs from
hidden fields on the comboboxes.

For data entry and review you'd use combo-boxes on hte forms for
each of those properties, and on reports you would use textboxes
that contained a dLookup function that takes the id and returns your
first, last or concatenated name.