multiple fields from a combo box in a single field in related tabl

[Guest]

I am trying to construct a table using other tables as source data. What I can't figure out is how to get all fields from one record in another table to display in a single field in the table I am working on. For example. I have one table for powders. The fields are Brand and Type. In another table, with a field named powder, I am unable to get the results from the combo box to display both fields from the table named "Powder". Ie., under Powder, there is a record with Hodgon as the Brand, and 4350 as the type. In another table called "Loading Data", there is a field called powder. How do I get "Hodgon 4350" to display in the "powder" field of the "Loading Data" table. I have attempted to use the lookup wizzard, and it appears to access the right information, but it will not display multiple fields into one in my "Loading Data" Table.

Thanks,

John

 

[Colin Halliday]

You would only do this for a foreign key to establish the link to the
primary key record, but assuming this is what you are doing:

The drop-down combo box in the table (or form) will only display the first
non-zero width column of the combo-box source. If you want more data to
display in this column you must concatenate the multiple columns into one.
In the source query or Select Statement for the combo box, just concatenate
the required fields. Something like:

Product: [Brand] & " " & [Type]

The word Product is the name given to the derived column (otherwise Access
will call it something like Expr1), the & is the concatenation operator that
strings the text together, and you probably want to leave a space in between
the brand and type so you include a concatenated blank between the other two
fields.

Hope this helps.

Colin

I am trying to construct a table using other tables as source data. What

I can't figure out is how to get all fields from one record in another table
to display in a single field in the table I am working on. For example. I
have one table for powders. The fields are Brand and Type. In another
table, with a field named powder, I am unable to get the results from the
combo box to display both fields from the table named "Powder". Ie., under
Powder, there is a record with Hodgon as the Brand, and 4350 as the type.
In another table called "Loading Data", there is a field called powder. How
do I get "Hodgon 4350" to display in the "powder" field of the "Loading
Data" table. I have attempted to use the lookup wizzard, and it appears to
access the right information, but it will not display multiple fields into
one in my "Loading Data" Table.

 

[Guest]

ColiSELECT [Powder].[ID] & [Powder].[Brand] & [Powder].[Type] FROM Powder; n,

I attempted to try what you suggested, but unsuccessfully. Now it shows nothing in the combo box. Here is the row source syntax:

SELECT [Powder].[ID] & [Powder].[Brand] & [Powder].[Type] FROM Powder;

Any ideas?

Thanks,

John

You would only do this for a foreign key to establish the link to the
primary key record, but assuming this is what you are doing:

The drop-down combo box in the table (or form) will only display the first
non-zero width column of the combo-box source. If you want more data to
display in this column you must concatenate the multiple columns into one.
In the source query or Select Statement for the combo box, just concatenate
the required fields. Something like:

Product: [Brand] & " " & [Type]

The word Product is the name given to the derived column (otherwise Access
will call it something like Expr1), the & is the concatenation operator that
strings the text together, and you probably want to leave a space in between
the brand and type so you include a concatenated blank between the other two
fields.

Hope this helps.

Colin

I am trying to construct a table using other tables as source data. What

I can't figure out is how to get all fields from one record in another table
to display in a single field in the table I am working on. For example. I
have one table for powders. The fields are Brand and Type. In another
table, with a field named powder, I am unable to get the results from the
combo box to display both fields from the table named "Powder". Ie., under
Powder, there is a record with Hodgon as the Brand, and 4350 as the type.
In another table called "Loading Data", there is a field called powder. How
do I get "Hodgon 4350" to display in the "powder" field of the "Loading
Data" table. I have attempted to use the lookup wizzard, and it appears to
access the right information, but it will not display multiple fields into
one in my "Loading Data" Table.

Thanks,

John