Parsing data

[kilt]

Hi People!

Small problem of how to parse some data from our old database into our new one. The old database had 6 seperate fields that represented the Regional sales areas of Britain. I now want to have these as a drop down list box from the new form to the relevant table. How can I parse the old data into the new single field as I need all the previous data to be in the new database?

Thanks in advance..

 

[LarryDeLaruelle]

Kilt:

Without more information, it's difficult to provide an exact solution.

What type of data do the current fields contain? Text, check box, etc?

What I would do is the following:

1. Create a lookup table that contains a record number and name for each of the six regions (RegionID as Key field & RegionName). Use Autonumber for RegionID.

2. Add a new field to the existing table for RegionID with type as Number.

3. Create a sub routine to read in each record, test the value/s of the current six fields and then write the appropriate value to the new RegionID field.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[kilt]

Larry:

Thanks for getting back to me. The 6 fields were all Check Boxes so the person inputting data would tick on the relevant region which could then be pulled out via a query to the correct sales team. I want to have a simple list box with the 6 regions in it instead of 6 messy tick boxes. I tried what you suggested but got mighty confused! The problem is that each record has a Primary Key identifier which I'm using in the new database. I've been thinking about it & think I can pull out the regions with the key identifier through a query & then update the new table using the Update Query. I think this will work - nice & simple (like myself!!)

Again thanks for your time.

Kilt..

 

[LarryDeLaruelle]

Kilt:

That will work but will entail more effort.

Using a Sub routine will step through each record, test the values and assign the appropriate RegionID. It does, however, require some VBA coding. If you are game to try, here is how I would set it up.

Public Sub ParseRegion()
Dim dbTemp as Database
Dim rsTemp as Recordset
Dim intRegion as Integer

Set dbTemp = CurrentDb()
set rsTemp = dbTemp.OpenRecordset("tblName&quot
rsTemp.MoveFirst

Do Until rsTemp.EOF
If rsTemp!chkRegion1 = True Then
intRegion = 1
ElseIf rsTemp!chkRegion2 = True Then
intRegion = 2
ElseIf rsTemp!chkRegion3 = True then
intRegion = 3
ElseIf rsTemp!chkRegion4 = True Then
intRegion = 4
ElseIf rsTemp!chkRegion5 = True Then
intRegion = 5
ElseIf rsTemp!chkRegion6 = True Then
intRegion = 6
End If

With rsTemp
.Edit
!RegionID = intRegion
.Update
End With
rsTemp.MoveNext
Loop

End Sub

This assumes two conditions: 1, no record will have more than one box checked; 2, no record will have no boxes checked.

Simply type (or copy/paste) it into a new module and replace the table and field names as appropriate. Then click on the Run/Go-Continue from the menu bar.

I would strongly recommend that you make a back up copy of the table for insurance (never hurts).

If you want to try this and have questions, feel free to post again.



Larry De Laruelle
ldelaruelle@familychildrenscenter.org