I want data from two tables on the same control.

[Nigel1500]

Hello,

I'm new to MS Access 2010. I have a situation where I have a table of names and a table of aliases that I'd like to display in one continuous form. They are linked as follows and one name can have many aliases:

tbl_Names:


--------------
ID as AutoNumber (primary key)
Name as Text

Sample data:

1 Alan
2 Ben Smith
3 Charlie L
4 Darren C Langworthy
5 Ed Liu

tbl_Aliases:
--------------
ID as AutoNumber
Name_ID (tbl_Names one to many relationship)
Alias as Text

Sample data:

1 3 Charlie
2 1 Big Al
3 5 The Chinaman
4 2 The Terminator
5 1 All Over It
6 4 Dags
7 2 Afterglow
8 5 On Your Case
9 3 Charles

In a Text Box, I would like to display the merged fields of tbl_Name(Name) and tbl_Alias(Alias) sorted alphabetically, with Names formatted differently to Aliases as follows:

Afterglow
Alan
All Over It
Ben Smith
Big Al
Charles
Charlie
Charlie L
Dags
Darren C Langworthy
Ed Liu
On Your Case
The Chinaman
The Terminator

I'm just not sure how to tackle this. Any advice appreciated.

[burrina]

You can Concatenate the two fields together. In your query grid this would be an example: Of course you need both tables in your query.

FullName: [FirstName]&" "& [LastName]

HTH

EDIT: Unsure why you would do this. Or of your form setup. Is this just for display purposes? Here is a screenshot of an example.
I used a Cartesian and a continuous subform for the example.

[ssanfu]

Another option is to use a Union query. Can't have special formatting for certain words and not others.
Maybe a 2nd field with a "+" for the alias.

Note that "Name" is a reserved word and shouldn't be used as object names.
Here is a list of reserved words : http://allenbrowne.com/AppIssueBadWord.html

[Nigel1500]

EDIT: Unsure why you would do this. Or of your form setup. Is this just for display purposes?

Hi burrina,

Thanks for the prompt reply. The aim is to be able to select a person, or alias, from the same list and open a sub-form to enter data. Such a situation could be linked to the management of a forum ... illegal bikie gang ... or a secret spy agency. So, I'd like to be able to select, 007, Bond, James Bond, <other alias> ... etc.

[Nigel1500]

Another option is to use a Union query. Can't have special formatting for certain words and not others.
Maybe a 2nd field with a "+" for the alias.

Note that "Name" is a reserved word and shouldn't be used as object names.
Here is a list of reserved words : http://allenbrowne.com/AppIssueBadWord.html

Hi ssanfu,

Thanks for the prompt reply. I will try a Union query. Something like this:
http://stackoverflow.com/questions/4...ss-union-query

But do I build the query under Form/Data/Record Source, or do I use an event?

Also, your idea of an extra field is an excellent idea. I might be able to use conditional formatting. Will get back to you with my results soon.

[ssanfu]

Yep, something like that. You will have to create a new query and type in the SQL..... query builder can't handle it.

Really fuzzy on what you are trying to do, but...
That query is bound to a list box. Then use the list box double click to open another form...?????