Multiple valued fields

[TylerTand]

I need to have a field that is labeled zones. This field can have anywhere from 1 to 7 or so values. The Values are numbered 1-15. If I have a person that operates in zones 2,3, and 4 I need to be able to have the annotated in the record so that when I want to see all the people operating in zone 2 it will recognize that Joe Smith works in zone 2, even though he also operates in zones 3 and 4. I need to do this in Access 2003 since that is what they have at work. I know that 2007 has multivalued fields but I don't have that version at work. So if I understand this right I create a separate table with headings Zone and Autonumber as the PK. Then I link that to the main table in a one to many relationship between the zone table(many) and the main table(one)? Then when I create a query that searches for all records that have a 2 in the zone it will find Joe Smith's record? Is this right? Thanks for your help. If there is an easier way please explain it to me. Thanks again.

 

[Uncle Gizmo]

You are on the right track!

Create a look up table for your Zones

Create another table to record the operator number (ID) and the zone or zones the operator operates in.

Create a form based on this OppZone table "frmOppZone" displaying the information in datasheet view.

Add this form to your main form and it will display as a subform on your main form. you may wish to identify it as a subform by changing its name from "frmOppZone" to "sfrmOppZone".

I think from memory the process of adding the subform to the main form triggers a wizard which will ask you to identify the fields linking the two sets of data together, this will be the operator number on the main form and the feild that stores the operators number in the subform.

This is the beauty of MS Access, it handles the relationship between your main form and the subform automatically. You can add and delete records in the subform and everything is updated for you in the background! You will then have a list of zones that your operator can operate in and you can add and delete from the list as you require.

 

[Dennisk]

Doesn't a multivalue field go against the grain of normalisation?

 

[Simon_MT]

What you want is:

EmployeeZones

Employee / Zone (PK)
JS 2
JS 3
JS 4

Simon

 

[TylerTand]

I have to have a multivalued thread because I only want to have one record for each person. But each person works in different zones. For later query purposes I need to be able to find all the people that work in a specific zone. I am going to have a data entry form eventually. How will that work for entering a new person? Will I be able to check a box for all the zones that the person works in? Plus this is basic table linkage question but if I have in my main table a field named zone and the primary key is a field called FINS what do I need in the table that lists the different zone possibilities so that they can be linked. I know that it is a one to many relationship but I don't understand how that is set up. Your help is greatly appreciated. Thanks.

 

[Uncle Gizmo]

>>> I need to do this in Access 2003 <<<
>>> I have to have a multivalued thread <<< you mean field?

You cannot have a multi value field in access 2003.

 

[Uncle Gizmo]

This video shows you what I mean, this example uses regions.

 

[Rich]

Wouldn't want to appear picky but aren't The South West and The West Country both the same?

 

[Uncle Gizmo]

Hi Rich,
It depends if you are a Clod hopper or a carrot cruncher!

 

[Rabbie]

Wouldn't want to appear picky )

Why not. Picky can be good especially when programming.

 

[John_W]

It seems to me that what you have is a many-to-many relationship between people and zones. What you need then is a table of unique people, a table of unique zones and a third table to act as a junction table between the two. That way you can link a person to as many zones as you like.

 

[Simon_MT]

I agree.

If you go Zone > Employee_Zone > Employee, the Employee will only be stated once for each Zone.

If you go Employee > Zone_Employee > Zone, the Employee will only be stated once if you ask for a specific zone.

Simon

 

[Uncle Gizmo]

Doesn't a multivalue field go against the grain of normalisation?

yes it does but I think I read somewhere that there was a programming way round it to put it right. I'm not sure though......

 

[anski]

how can i convert a field into a multivalue field? my field is separated by commas.

example: this is the original entry:

field1......... field2
204 ............418,419,420
​

what i want:

field1......... field2
204 ...........418
204 ...........419
204 ...........420​

 

[JANR]

If this is a one-off then create a simple function and parse the fields to it. Use an array and split the multivalue field using the SplitFunction. You can use this array and insert it back into your new table.

See attached mdb, run the qryUpdate query and see the results in tbl2.

Function fMultiValue(ByVal RecID As Long, ByVal MultiField As String)
Dim VarArray As Variant
Dim i As Long
Dim strSQL As String
VarArray = Split(MultiField, ",")
    For i = 0 To UBound(VarArray)
        strSQL = " INSERT INTO tbl2 (ID, NewField) Values (" & RecID & ", " & VarArray(i) & ")"
        CurrentDb.Execute strSQL
    Next i
End Function

JR

 

[abtibs]

Can this code be used for text fields as well as number?

 

[JANR]

Sure, since a multivalue field is in itself a textfield,

1,2,3

Tom,Dick,Harry

Both are defined as textfield

JR

 

[abtibs]

Thanks for the responce JR. The reason I asked is because I ran the sample DB and it worked great until I input "text" (A,B,C) in the table. It appears that all the values need to be numeric.

 

[JANR]

Function fMultiValue(ByVal RecID As Long, ByVal MultiField As String)
Dim VarArray As Variant
Dim i As Long
Dim strSQL As String
VarArray = Split(MultiField, ",")
    For i = 0 To UBound(VarArray)
        strSQL = " INSERT INTO tbl2 (ID, NewField) Values (" & RecID & ", [COLOR=red][B]'[/B][/COLOR]" & VarArray(i) & "[COLOR=red][B]'[/B][/COLOR])"
        CurrentDb.Execute strSQL
    Next i
End Function

Just add the single quote around the VarArray marked in red to handle strings, also change the property of the "NewField" in the reciving table
(tbl2) from number to text

JR

 

[abtibs]

JR, That worked great, thanks! I can see where this is going to be extremely useful for me. I assume the same can be done for RecID? I may have non-numeric data there as well.

Update: I got it, just had to change RecID As Long to RecID As String

Thank you very much JR!