Multivalue Field Advice

[Leonidsg]

Hello,

I am working on my database and have created several multivalue fields, I currently use Access 2010. However I am going to move my database to SQL 2005, which I believe will convert my multivalue field into a memo field. I was wondering what is the best method to avoid the multivalue field. In my database one example where is use a multivalue field: when a patient visits the clinic they will indicate what if any fractures they made have had since the previous visit --> "Fracture" field, this field links to a lookup table "AllBonesLkUp", which has a list of all bones which the patient may have had broken. Since the patient could have broken multiple bones I want to be able to indicate that.

Hope that makes sense.

Thank you for your help,
Lenny

[June7]

I didn't think SQL could handle Access multi-value field at all. If it will convert to memo (or BLOB) I guess that is better than nothing.

The only alternative to multi-value is a related child table.

[orange]

I would opt for the related child table if it were my decision.
A memo field is much like a shoe-box for collecting photos. Just throw the next one in the shoe-box.
A memo is a catch all with a lot of idiosyncrasies.

Use a proper relational table.
Good luck.

[Leonidsg]

That makes sense. I have a little more difficult example that I needed help setting up a child table relationship.
Example:
My parent table: "OrthBoneSurgery" has a child table named "Bones", the "Bones" child table tells me which bones were worked on during a particular surgery, it is a 1-many relationship since many bones can be worked on during one surgery. Currently the "Bones" table has a few fields some are multivalue and some are single value. For Bones we want to know" Instrumentation" which is a single value field telling us which instrumentation was used to fix a fracture, "Size" this field relates to the size in mm for the instrumentation, and a few other single value fields. Where is becomes difficult for me is a field name "SurgicalIndications", surgical indications is a mulitvalue field which the user specifies from a look up table which are indications are relevant to that particular bone which the operation is being done. The problem is one bone has multiple surgical indications for example a database user may want to indicate "non-healing fracture" and "pain" as indications. Would the solution be to create an child table to the child table "Bones" which has a foreign that relates to the primary key of "Bones". That child table, call it "BonesIndications" which basically would just have a list of all the indications used.

Hope that rant makes sense. Please let me know what you think.

Thank you,
Lenny

I didn't think SQL could handle Access multi-value field at all. If it will convert to memo (or BLOB) I guess that is better than nothing.

The only alternative to multi-value is a related child table.

[orange]

I'm not sure how far along in your database project you are, but I would try to stick to traditional database concepts.
I think your multi-valued fields will get you into more trouble than they are worth. Moving something into a memo field and hoping to use or search it in a meaningful way -- picking individual entries from a list-- will be more than troublesome.
Many people will advise you to ignore multivalued fields , and lookups at the table field level. These were "provided" to users by M$oft, but are being bypassed by many.

Do you have a clear set of specifications? or Business rules?
Do you have a data model you could post as a jpg?

[Leonidsg]

Could you explain to me why lookup tables are bad. They seem like a nice feature that allows me to standardize string values I need and have a seperate table where I can add more values when needed. Is there another way to be able to standardize the string values.

Also I do not necessarily know what you mean by Business Rules or data model. This database is for research to keep track of patients in several different clinics allowing for better treatment and data accessibility for future treatment and research.

Thank you again for all your help I really appreciate it.

Lenny

[orange]

Be very cautious how you read/interpret what was said.

Many people will advise you to ignore multivalued fields , and lookups at the table field level.

I said avoid lookup fields at the table level -- not lookup tables are bad.

See Pat Hartman's decription of lookups at the table field level at Post #2 at
http://www.access-programmers.co.uk/...d.php?t=246214

Do you have a list of requirements? Specifications? Business Facts? ( I'm using these interchangeably here)

Here is a link to a database design approach http://www.databaseanswers.org/approach2db_design.htm

Here are links to a data model (sample info only)
http://www.databaseanswers.org/data_...ore_dezign.htm

and related facts
http://www.databaseanswers.org/data_...tore/facts.htm

Here is a tutorial that leads you through a process to identify tables, attributes, normalization, relationships.

[Leonidsg]

I think I am beginning to understand, kind of like the end of the matrix. I do have a model I can share with you if would like, I am open to any help. I did want to clarify something though about the lookup tables. It seems like I can make the table field be a text but when I create a form combobox which is bound to the text field I can make the source for the combobox be the lookup table, does that make sense?

Thank you again,
Lenny

Be very cautious how you read/interpret what was said.


I said avoid lookup fields at the table level -- not lookup tables are bad.

See Pat Hartman's decription of lookups at the table field level at Post #2 at
http://www.access-programmers.co.uk/...d.php?t=246214

Do you have a list of requirements? Specifications? Business Facts? ( I'm using these interchangeably here)

Here is a link to a database design approach http://www.databaseanswers.org/approach2db_design.htm

Here are links to a data model (sample info only)
http://www.databaseanswers.org/data_...ore_dezign.htm

and related facts
http://www.databaseanswers.org/data_...tore/facts.htm

Here is a tutorial that leads you through a process to identify tables, attributes, normalization, relationships.

[orange]

It seems like I can make the table field be a text but when I create a form combobox which is bound to the text field I can make the source for the combobox be the lookup table, does that make sense?

When you create a bound form - it means that when you open the form the data from the recordsource (the underlying table or query) will appear in the form.
If you have a combobox on the form, that is unbound, you can set its rowsource to a table or query. You can select a value from the combo, and use that value to "do something else( criteria to select a record etc)".

Here are 2 links to combo box video tutorial - you may find this useful concepts,
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html (Cascading combo)

Please post your model as jpg.

[June7]

Yes, the combobox RowSource can be a lookup table. Decision to be made is whether or not to save the autonumber ID or text descriptor. If the descriptor is short and simple (like 'Good', 'Bad', 'Poor', 'Excellent'), I favor saving it. This will eliminate the need for table join in queries and comboboxes are simpler.

[Leonidsg]

Hi June7,

Yes most my descriptions are short, but there are some spaces between words like "Non Ambulaotory". I kind of wanted to know if this is the best way to make sure people only pick a certain string to use, for example I dont want people to be able to use their own text because some one may say "Left Femur" while another person says "L Femur" this is a simple example. So if using the combo box with the rowsource being the lookup table , how do I get the string to populate in the table I am working on.

I have seen those videos, they were very helpful when I created some other combo boxes. I am trying to figure out the best way to make sure that text field receives only the strings that I have predetermined.

Thank you both for your help,
Lenny

Yes, the combobox RowSource can be a lookup table. Decision to be made is whether or not to save the autonumber ID or text descriptor. If the descriptor is short and simple (like 'Good', 'Bad', 'Poor', 'Excellent'), I favor saving it. This will eliminate the need for table join in queries and comboboxes are simpler.

[orange]

A lookup table is an excellent means of ensuring consistent terminology -- eg Left femur L femur

In addition a well structured Lookup Table will allow you to extend your list, if that becomes necessary.
In a lookup table, let's just mock up tblReasonLkup

tblReasonLkup
ReasonId PK (I'd make this autonumber if you have more than 3 values)
ReasonDesc

1 Fall
2 Car Accident
3 Household Accident
4 Sport Injury
5 Degenerative Calcification
6 Unknown

You use ReasonId in all usages, and use the description on Reports/Forms. You use the Lookup table to relate the ReasonID to the ReasonDescription.

[Leonidsg]

Just to clarify. When I make the related table field I should make it a lookup field but use the ID instead of the description. What is the reason behind using the ID? And when I make my forms and reports use the description. Correct.

[June7]

Combobox is bound to field, item selected from RowSource is saved to field. Decide if you want to save the ID or the text descriptor. If you save the autonumber ID the destination field must be a number field. Related primary and foreign keys must be the same datatype. Also, if you save the ID then retrieving the descriptor for display on report requires joining tables in query.

[Leonidsg]

Okay so the table field can be a lookup field in this case. Also do you either of you guys understand my previous example of eliminated multi value fields. I am still confused what do if the multi value field needs to be linked to a existing child table. Do I simply make the multivalued field its own child table where the parent able has a unique key to that specific thing.

Thank you
Lenny