From: Lars Brownies on
I want users to be able to store personal preferences for paths, layout,
report dates, etc. I'm thinking to store the info in the following tables

tblUser
UserID*
UserLastnam
etc.

tblUserPreference
UserID*
PreferenceID*

tblPreference
PreferenceID*
PreferenceName
PreferenceGroup
PreferenceValue

Problem is that I need to store values of different data types in the field
PreferenceValue.

My questions are:
- How do I deal with the diffences in data type? Should I add a field for
every data type?
- If I enter a new user should I automatically add all Preferences to him or
should I only add a preference if he wants to differ from the default value?
- Or shouldn't I bother with normalisation in this case?

Thanks,

Lars





From: Arvin Meyer on
I would use text, then use IsNumeric() to determine if it's a number. I have
also written a custom function to determine alpha (it's in my book <g>)

Public Function fIsAlpha(varIn As Variant) As Boolean
On Error Resume Next

fIsAlpha = Not Asc(LCase(Left(varIn, 1))) = Asc(UCase(Left(varIn, 1)))

Exit_Here:
Exit Function

End Function
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access
Co-author: "Access 2010 Solutions", published by Wiley


"Lars Brownies" <Lars(a)Browniew.com> wrote in message
news:htpit3$2rhe$1(a)textnews.wanadoo.nl...
>I want users to be able to store personal preferences for paths, layout,
>report dates, etc. I'm thinking to store the info in the following tables
>
> tblUser
> UserID*
> UserLastnam
> etc.
>
> tblUserPreference
> UserID*
> PreferenceID*
>
> tblPreference
> PreferenceID*
> PreferenceName
> PreferenceGroup
> PreferenceValue
>
> Problem is that I need to store values of different data types in the
> field PreferenceValue.
>
> My questions are:
> - How do I deal with the diffences in data type? Should I add a field for
> every data type?
> - If I enter a new user should I automatically add all Preferences to him
> or should I only add a preference if he wants to differ from the default
> value?
> - Or shouldn't I bother with normalisation in this case?
>
> Thanks,
>
> Lars
>
>
>
>
>


From: Allen Browne on
Use a Text field, since you can store any data type there.
Add another field to indicate what data type it should be.
In Form_BeforeUpdate, test if the value matches the data type before you
write it to the table.

For this field, I use the vbVarType values. For a combo with a Value List,
the RowSource is:
2;"Integer";3;"Long";5;"Double";6;"Currency";7;"Date";8;"String";11;"Boolean";17;"Byte"

You can then match the values to the members of vbVarType.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Lars Brownies" <Lars(a)Browniew.com> wrote in message
news:htpit3$2rhe$1(a)textnews.wanadoo.nl...
> I want users to be able to store personal preferences for paths, layout,
> report dates, etc. I'm thinking to store the info in the following tables
>
> tblUser
> UserID*
> UserLastnam
> etc.
>
> tblUserPreference
> UserID*
> PreferenceID*
>
> tblPreference
> PreferenceID*
> PreferenceName
> PreferenceGroup
> PreferenceValue
>
> Problem is that I need to store values of different data types in the
> field PreferenceValue.
>
> My questions are:
> - How do I deal with the diffences in data type? Should I add a field for
> every data type?
> - If I enter a new user should I automatically add all Preferences to him
> or should I only add a preference if he wants to differ from the default
> value?
> - Or shouldn't I bother with normalisation in this case?
>
> Thanks,
>
> Lars
>
>
>
>
>
From: Lars Brownies on
Thanks Arvin, Allen,

I've been giving it some further thought. Since it is very unlikey that the
number of preferences will exceed 15, it seems more practical to store these
values in a personal .ini file and let the user fill the respective keys by
unbound form controls. That way:

- I can easily use unbound checkboxes in my preferences form
- I can easily show all preferences for the user to edit, also when a user
hasn't set a value for it
- I only have to store values that the user actually sets. The key in the
..ini file is created when a user hasn't set a value for it yet.
- Opposed to having a separate table field for every preference
(denormalized), with an .ini file a backend design change is not needed.
- The design will be simpler

Any thoughts?

Lars


"Arvin Meyer" <arvinm(a)invalid.org> schreef in bericht
news:nqydnX_ba79F8Z3RnZ2dnUVZ_j6dnZ2d(a)earthlink.com...
> I would use text, then use IsNumeric() to determine if it's a number. I
> have also written a custom function to determine alpha (it's in my book
> <g>)
>
> Public Function fIsAlpha(varIn As Variant) As Boolean
> On Error Resume Next
>
> fIsAlpha = Not Asc(LCase(Left(varIn, 1))) = Asc(UCase(Left(varIn, 1)))
>
> Exit_Here:
> Exit Function
>
> End Function
> --
> Arvin Meyer, MCP, MVP
> http://www.datastrat.com
> http://www.accessmvp.com
> http://www.mvps.org/access
> Co-author: "Access 2010 Solutions", published by Wiley
>
>
> "Lars Brownies" <Lars(a)Browniew.com> wrote in message
> news:htpit3$2rhe$1(a)textnews.wanadoo.nl...
>>I want users to be able to store personal preferences for paths, layout,
>>report dates, etc. I'm thinking to store the info in the following tables
>>
>> tblUser
>> UserID*
>> UserLastnam
>> etc.
>>
>> tblUserPreference
>> UserID*
>> PreferenceID*
>>
>> tblPreference
>> PreferenceID*
>> PreferenceName
>> PreferenceGroup
>> PreferenceValue
>>
>> Problem is that I need to store values of different data types in the
>> field PreferenceValue.
>>
>> My questions are:
>> - How do I deal with the diffences in data type? Should I add a field for
>> every data type?
>> - If I enter a new user should I automatically add all Preferences to him
>> or should I only add a preference if he wants to differ from the default
>> value?
>> - Or shouldn't I bother with normalisation in this case?
>>
>> Thanks,
>>
>> Lars
>>
>>
>>
>>
>>
>
>
From: Bob Quintal on
"Lars Brownies" <Lars(a)Browniew.com> wrote in
news:htpit3$2rhe$1(a)textnews.wanadoo.nl:

> I want users to be able to store personal preferences for paths,
> layout, report dates, etc. I'm thinking to store the info in the
> following tables
>
> tblUser
> UserID*
> UserLastnam
> etc.
>
> tblUserPreference
> UserID*
> PreferenceID*
>
> tblPreference
> PreferenceID*
> PreferenceName
> PreferenceGroup
> PreferenceValue
>
> Problem is that I need to store values of different data types in
> the field PreferenceValue.
>
> My questions are:
> - How do I deal with the diffences in data type? Should I add a
> field for every data type?
> - If I enter a new user should I automatically add all Preferences
> to him or should I only add a preference if he wants to differ
> from the default value? - Or shouldn't I bother with normalisation
> in this case?
>
> Thanks,
>
> Lars
>
you would be better with this structure
tblUserPreference
UserID*
PreferenceID*
PreferenceValue <- this is text, store numbers as text

tblPreference
PreferenceID*
PreferenceName
PreferenceType <-use this to store the value's type (number or text)
PreferenceGroup
PreferenceDefault <- see PreferenceValue

Only store non-default preferences in TblUserPreferences.
dLookup the user's preference value for a specified PreferenceId, if
null use the default from tblPreferences.
Use the PreferenceType to control which conversion function to apply
to the value (cInt, cBool, cDouble, etc.)