Looking for a option between Lookup Field and others.

[huntersoasis]

currently i was playing around with a lookup combobox field



The issue is all works except the data shows up as

x-small, small, medium, large

The data should show up as

x-small|small|medium|large

How to i get the Comma and the space to be removed.

and change the Comma to |



let me clarify what I have in simple form and what is needed and lets see what suggestions we get.


Table 1
ID, Name, Size

Table 2
ID, Sizes

From1

I need the Size to be check boxes of the size opens avaiable in Table2; Sizes when option is check it is added to Table1;Size but in a format that may be
If all check boxes clicked
Small|Medium|Large
or
If only small and medium are checked
Small|Medium
If only small and medium are checked
Small|Medium



The real one has almost 50 options so im doing this as a simple example.
Thanks for the help

[ItsMe]

I suggest you work on your table structure first. Then, the rest will come a lot easier. Have you been able to find any tutorials on relational databases?

[huntersoasis]

can you give me a example what what the structure should be on something this simple?

[ssanfu]

lookup combobox field

when option is check

It sounds like you are trying to use a multi-value field and/or a lookup FIELD in a table.

I never use lookup FIELDS (different than a look up TABLE).
Nor do I EVER use MFVs. They are a large pain in the backside!

As ItsMe said, you really need to work on the table structure.
Use pencil and paper to design your tables. Try to design a normalized structure.

[ItsMe]

can you give me a example what what the structure should be on something this simple?

I am having are hard time understanding what your entities are and what your business rules are. Perhaps you can explain to us what your entities are and what you are trying to accomplish, from a business perspective. Here is a 15 min tutorial on establishing an Entity Relationship Diagram.
http://www.youtube.com/watch?v=-fQ-bRllhXc

[huntersoasis]

Sorry guys, Trying to clear up a few things in a database that is full of Calculated fields and its just a mess.

The issue they are having right now is again back to the "data normalization" problem

They currently have 10 Size Fields that display the current size of a Item or Product the issue is

they have

Size1
Size2
Size3
Size4
Size5
Size6
Size7
Size8
Size9

Now not all are filled alot are null. and Size1 might have a size in it and Size2 is null but yet Size3 has a size OBVIOUSLY total Garbage........


What im trying to do is a little fix on getting all these fields to 1 Field that is populated by a series of check boxes. so that so many null field are in the export to the mysql server... Right now at the avg of 3 fields not being null they have 140,000 null fields and when the import hits the web server it is killing it.

[huntersoasis]

here is just a quick look at the File file cut down at what im looking at

"PRODUCT_SKU","Product","Size1","Size2","Size3","S ize4","Size5","Size6","Size7","Size8","Size9","Siz e10"
"40466-1s","Hawaii Beach Dress","Small",,,,,,"Medium","Large",,"X-Large",
"40446-4","Peach Beach Dress",,,,,,"Small,,"Large",,,
"41465-2","Red Beach Dress",,,"X-small",,,"Small,,"Large","X-Large",,


Here is what i want to atleast get the field too until a total redo of the thing can be done.........


"PRODUCT_SKU","Product","Size"
"40466-1s","Hawaii Beach Dress","Small|Medium|Large|X-Large",
"40446-4","Peach Beach Dress","Small|Large",
"41465-2","Red Beach Dress","X-small|Small|Large|X-Large",

but I have to keep the "Check boxes of the sizes in the form they use Help please lol

[huntersoasis]

I get that this still does not get anywhere to a correct point but it does hold it over until this can be totally rebuilt

[huntersoasis]

I just want to Clarify my number of Null fields in Sizes is wrong its 10 fields of sizes but for 5 sites so they have 50 Fields for sizes with the average of 3 field filled out on each so we are talking about 15 of 50 fields are not null making almost 700,000 Null fields

[ssanfu]

Repeating fields is a sure sign of a non-normalized structure. I am afraid they will continue to struggle until someone redesigns the dB.

What im trying to do is a little fix on getting all these fields to 1 Field that is populated by a series of check boxes

IMO, not a good idea.

I still don't understand your requirements or structure, but I would have

autonumber PK field
FK field for products
a field for the size 1 to 10
a FK field for the letter size (x-small, small, medium, large. The x-small, small, medium, large would be in a look up TABLE, )


EDIT: I just saw

the mysql server

and I know nothing about mysql . Can it handle multi-value fields?

[ItsMe]

I am not at my desk right now so I will keep my response short. You can use VBA and DAO to literate named fields across a row. With the variables you could concatenate a hard coded comma, along with the named field value.

It seems you need to evaluate a control on a form to determine if a variable is to be concatanated to the string variable that is your result. So there will be some nesting. Helper methods could make your code more manageable.