Commas instead of semicolons in MS Access lookup column

I would not dare label myself as a programmer. But I am running a website with a storefront. I want to label some of my products with multiple categories. This involves creating one column in my MS Access database (called categories) that has categories separated by commas. i.e. "shirts,clothing,wearables,sports"

I've added a lookup column that does this fine and inserts the categories I choose for a particular product. The problem is that when I export my database into a txt file, Excel file, etc... this column shows up with semicolons! "shirts;clothing;wearable;sports"

Now, I could go and replace all semicolons with commas in that column, but I'm hoping there is an easier way to do this within MS Access开发者_C百科. Perhaps a setting I am not aware of?

Any assistance would be greatly appreciated!

Storing a list of items in a field is a design error.

Instead, you should have a table that is related to your main table, and that stores the ProductID (to link it back to a particular product record) and one category per record. Your example data would look like this, assuming ProductID=1:

ProductID   Category
    1       shirts
    1       clothing
    1       wearables
    1       sports

In a web app, you'd then display the results in a multiselect listbox (you add the "multiple" tag inside your SELECT tag). You'd have to iterate through the list to add the SELECTED tag to the items that have already been chosen.

I found the solution to my problem in the oddest place.

I wanted a TAB delimited file just as I created it in Access. But if I used commas in a field, Access replaced them with semi-colons. I exported to Excel and tried that, but although Excel was kind enough to leave my commas, it placed quotation marks around that field when exported to the TAB file.

SOLUTION: Google Documents. Simply upload an Excel file (exported from Access), then save to a TAB file. Google doesn't fool with your data and you get the file you want ready for import. In this case, it is being imported into MIVA Merchant. A horrible piece of... software used for e-commerce. But if anyone else out there is using it, don't bother purchasing all those modules and macros that enable you to do what Excel or Access should do in the first place until you've checked out this free and pretty easy work around.