Create a column based on values in another table (VLOOKUP kind of thing)

[andreas_udby]

OK, I'll set this up for you. I'm using Access to create a set of reports that previously took days and days of Excel pivot tables. The reports are fed by a single gargantuan spreadsheet downloaded once a month from our HR database, so my Access tool is purposely not normalized.

There is a column called Org Name that contains the name of the sub-organization a person belongs to -- an example is "IT Sales Application Development", "CBA Sales West Team 1", or "CBA Risk Underwriting South". Based on these Org Names, I have to manually create a column I call Business Segment that tells the people reading these reports what higher-level business segment these employees belong to -- again, as an example, the first Org Name I mentioned would fall under the "IT" bucket, while both of the other Org Names would fall under the "CBA" bucket.

I've been using a VLOOKUP in an Excel spreadsheet to add the Business Segment column, but I'd like to dispense with that step. I'm hoping, once I build a table in Access that equates Org Names with Business Segments, that there is some way to run a query or macro that automatically creates a new column in the master table, calls it Business Segment, and fills in the appropriate values based on each record's Org Name. Can someone point me in the right direction to go with this?

Thanks much!
Andreas

(My ultimate intent is to hand this tool off to someone once I leave this role, and I want to make it as automated for them as possible. Few of my coworkers have any understanding of Access.)

 

[dcx693]

There are a few ways you can proceed with this.

First, I assume there are a finite (or manageable) number of distinct business segments. In that case, you can create a calculated field in your queries that does not alter the original table by storing the "calculated" business segment. You can calculate that field either by using some long IIF function, or by writing a custom function. Then use that query as the source for your reports.

Second, you can add a column to your table using code like this:

Private Sub AppendField(strDataTable as String, _
strField As String, varType)
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'- Set a reference to the current database
Set dbs = CurrentDb()
'- Set a reference to the large fleet raw data table
Set tdf = dbs.TableDefs(strDataTable)

For Each fld In tdf.Fields
If fld.Name = strField Then
MsgBox "The " & strField & " field already exists", vbCritical
Exit Function
End If
Next fld

'- Set a reference to a field to be created
Set fld = tdf.CreateField(strField, varType)

'- Append the field to the existing fields,
'- thereby creating it
tdf.Fields.Append fld

'- Clean up
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Sub

Use it by passing the name of the table to which you'd like to add your field, the name of the field you'd like to add, and the type of field it is (in your case, use dbText). Sample call:
Call AppendField "tblPersonnel","BusinessSegment",dbText

Once you've got the column in your data table, you can run update queries to find instances of particular business units and update your new column with the values you want.

 

[Pat Hartman]

Create a table with the OrgName and BusinessSegment columns similar to your VLookup table. Then add that table to any query where you would like to output BusinessSegment. Join the two tables on OrgName.

This will be the easiest setup for your user to maintain. If the relationship between OrgName and BusinessSegment changes, the user simply updates the lookup table or adds a new entry if necessary. You won't need to maintain the data. If you used a function or added a calculated column to the main table, you would be stuck fixing the problem if the relationships changed. So, in the long run, it is best to do it right.