How to change item source for a multi-valued field from a linked table to a list?

[lookingforK]

Hi,


I am using MS Access 2007.


I have created a multi-valued field "Product Category" that lookups data items from a linked table. So, the Data Type for the multi-valued field "Product Category" is Number.


Now I want to change the Data Type of "Product Category" from Number to Text, and make a value list that I can type values in and can provide the same data items as the linked table.


How to change item source for the multi-valued field from a linked table to a list that I can type in values?


Is there a feature provided by MS Access 2007 can enable such a conversion?:banghead:



Thank you in advance.

 

[John Big Booty]

I'm a little confused, if you have a working solution using a linked table, why would you want to take the retrograde step of changing that to Value list

 

[lookingforK]

Thank you John,

The reason why I want to change the item source for the multi-valued field from a linked table to a list is:
* I am using MS Access 2007 for data entry, data storage, and exporting desired data sets to MS Excel workbooks.
* When I use VBA (i.e. {DoCmd.TransferSpreadsheet acExport} ) to export 3 data sets (e.g. from 3 queries) from MS Access 2007 to 3 tabs of MS Excel 2007, I find that: the multi-valued field looking up a linked table only displays numbers; but I need text strings in Excel.
* If the multi-valued field looks up a list that I can type values in, it can show text strings in Excel.
.

I'm a little confused, if you have a working solution using a linked table, why would you want to take the retrograde step of changing that to Value list

 

[John Big Booty]

You should be able to create a query that collects all the relevant data i.e. the text field associated with each of the ID's, and then tranfer that to your Excel workbook.

 

[lookingforK]

Yes. But first I need to change item source for a multi-valued field from a linked table to a list so that its Data Type can be changed from Number to Text.

This is my question for the post ...

You should be able to create a query that collects all the relevant data i.e. the text field associated with each of the ID's, and then tranfer that to your Excel workbook.

 

[John Big Booty]

You should be able to do that using a properly constructed query.

 

[lookingforK]

Thank you Pat,

How to write the query to concatenate the strings from the lookup table in a multi-valued field?:banghead:

Could you please give me an example?

The problem is the lookup field defined in the table. What you are experiencing is one of the many problems caused by them. The best way to solve the problem would be to simply remove the table-level lookup but since you have also used the multi-value abomination, that may cause too many changes for you to handle easily.

To solve the problem without making any schema changes, add the lookup table to your query. You can select the text field from the lookup table rather than the numeric field from the main table.