Multiple values in a single MEMO field
I have an existing application (DB has production data).
I have a need to add more fields, and wonder if I could avoid the hassle of getting the DB's sent to me.(There are DBs for each state, with same field design)
I have a spare Memo field (it is an Access 97 DB).
I suspect that it would be possible to 'delim' data into that Memo field.
That is probably not difficult, however I wish the stored data, to be easily 'tested' by SQL WHERE clauses using the LIKE keyword.
I will describe a possible solution, to help clarify my needs.
Say there are a few checkbox values, which could be '0' or '1'
I have the need for about 3, but figure I will build in the capacity for 10
I could test the contents of the Memo field in any existing record, and if '' (empty), then Update the field with a mask.
There would need to be a Delim for splitting and joining.
And there would need to be some bracketing within, so that the LIKE keyword can interrogate any checkbox
EG <0>0<0>$$<1>0<1>$$<2>0<2>$$<3>0<3>$$<4>0<4>$$<5>0<5>$$<6>0<6>$$<7>0<7>$$<8>0<8>$$<9>0<9>
If a record was 'read in' then I can use Split with '$$'.
Then each element of the array would hold 0 or 1 in the 4th position.
Later Saving of a record would tweak each element in the array and then Join and Save
Any SQL Query, that was just scanning for records with Checkbox 2 set to 'Checked' would use - LIKE '%<2>1<2>%'
There probably is a more elegant way to do this, but I am hoping my brainstorming(cough!) helps clarify what I am trying to achieve.
Ideally the solution would also allow me to store some other values on the end of that same MEMO field.
Perhaps some numbers, and strings.
The numbers could be 0 padded to make them the same length(eg 9) as each other.
The bracketing might be $$<a>000000000<a>$$<b>000000000<b>$$<c>000000000<c>
Now if I would like to store some strings on the end of the MEMO field, and cater for the LIKE keyword, that gets a bit more difficult.
I assume that I can forgo the luxury of varying length strings, and again force fixed length.
ZZ<a1> 50 spaces in here <a1>$$<b1> 50 spaces in here <b1>
If that appears wasteful of space, I could probably NOT USE fixed length for the strings.
I could just let them vary in length, and use the LIKE keyword, with an extra little check of returned records, to ensure the 'hit' was in the correct sub field.
Hopefully it is clear what I am trying to achieve.
I suspect that some others would have had a similar (unclean) desire in the past.
Anyone know of a better way of doing this ?
EDIT
With strings I will use ZZ<s1>Mary had a little lamb<s1>$$<s2>it's fleece was white<s2>
On reflection the strings don't need to be fixed length, as I can do this -
LIKE "%<s2>%fleece%<s2>%
I think that should work ?
I would only lump all of the above into a single MEMO field if I had no choice
For my current need I can find another two fields, so I will have one for each -
- CheckBoxes
<0>0<0>$$<1>0<1>$$<2>0<2>$$<3>0<3>$$<4>0<4>$$<5>0<5>$$<6>0<6>$$<7>0<7>$$<8>0<8>$$<9>0<9>
- Numbers
<0>0<0>$$<1>0<1>$$<2>0<2>$$<3>0<3>$$<4>0<4>$$<5>0<5>$$<6>0<6>$$<7>0<7>$$<8>0<8>$$<9>0<9>
- Strings
<0><0>$$<1><1>$$<2><2>$$<3><3>$$<4><4>$$<5><5>$$<6><6>$$<7><7>$$<8><8>$$<9><9>
Again I probably don't have to make the numbers fixed length as I can use -
LIKE "%<2>%980<>s2%
I think that should work ?
Probably won't be able to do Less Than and Greater Than scans, but such is life
Re: Multiple values in a single MEMO field
Your idea makes me laugh. It may work but that is a very bad idea because it will be very slow to query.
* Memo field is non-indexable.
* LIKE operator is much slower than other comparision operators, particular on long string and/or large table. Use it only when you have no other choice.
* Check box values are 0 and -1, need to be converted to 0 and 1 when stored.
If you think that is a smart idea then I would suggest:
* Reserve first 10 characters for 10 checkboxes and that may be something like : "1010011000", no need to use Split or Join when read and write.
* On query, use:
WHERE (Mid(YourMemo,2,1) = "1") '-- that is CheckBox2 is checked
Re: Multiple values in a single MEMO field
I was just updating my original post (see Edit on the end of post 1)
Quote:
If you think that is a smart idea then I would suggest:
* Reserve first 10 characters for 10 checkboxes and that may be something like : "1010011000", no need to use Split or Join when read and write.
* On query, use:
WHERE (Mid(YourMemo,2,1) = "1") '-- that is CheckBox2 is checked
I will check out your suggestion
Any thoughts on how I can do the LT and GT with my number fields ?
Thanks for responding.