Commas instead of semicolons in MS Access lookup column

Question

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. Perhaps a setting I am not aware of?

Any assistance would be greatly appreciated!

Answer 1

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.

Answer 2

Thanks David,

I think I understand what you're suggesting. That makes a lot more sense actually. Unfortunately I am confined by the types of files I can import into Miva Merchant (the store software.

I'm using Microsoft Access tables with some nice forms to help me arrange my products offline, then upload via a |pipe| delimited file that will update my entire store of 800+ items. In order for Miva to accept one product row and place it in several categories. (Like putting a t-shirt into the "shirts,clothing,wearables,sports" categories, the categories you list in that "CATEGORY_CODE" field must be seperated by commas.

MS Access's new lookup column feature works fine for this, allowing me to pull the categories I want from a separate table and they appear in the field as "shirts,clothing,wearables,sports". But when I go to export I get: PRODUCT_NAME COLUMN|shirts;clothing;wearables;sports|PRICE_COLUMN|etc.

And no cottsack, the rest of the database I'm uploaded is not comma delimited. I can use any delimiter I want, so I chose the pipe. This way I can add a META keywords column with comma seperators. I could do my categories by hand this way as well, but I'd love to find a way to use this lookup column feature in MS Access but with commas exporting instead of semicolons.

I hope that explains the situation a little better. Thank you so much for your help!

Answer 3

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.

Answer 4

I see no reason why I shouldn't have been able to do this export from Access either. But as far as I can tell, Access does not support exporting columns with commas "as-is" without perhaps, a complicated macro or some sort. I believe there are Access-based programs for purchase that can perform this function in some way, by either using macros or combining multiple fields, but this is the only "budget" solution I could find.