Normalize Series of Variables into One Variable

[cam2era]

I have the common problem of a table with a couple of key variables, then a series of strung-out variables that I would like to 'normalize'. Namely, "Budget", "Role" as keys; then "Mon1", Mon2", "Mon3, etc. for many months, which list the mon/year that a particular role is funded in a particular budget.



Is it possible to design some simple code that would create new records in a new table so that I have only "Budget", "Role", and "Mon"? I would love to collapse the Mon1, Mon2, ... series into one variable: "Mon"!

Thanks,
Ernie
(Helping out the financial manager at our unit.)

Budget	Role	Mon1	Mon2	Mon3	Mon4	Mon5
DKD	Investigator	03/2015	04/2015	05/2015	06/2015	07/2015
DKD	Coordinator	03/2014	04/2014	05/2014	06/2014	07/2014
ADA	Investigator	03/2013	04/2013	05/2013	06/2013	07/2013
ADA	Assistant	03/2013	04/2013	05/2013	06/2013	07/2013
NKC	Statistician	10/2014	11/2014	12/2014	01/2015	02/2015

[ranman256]

you will need to set the vSrcTabl name and tTargTbl

     'normalize the sourc tbl by appending 1 field at a time
Public Function NormalizeTbl()
     'vSrcTbl = the table to normalize
     'iStartFld = starting col# of the fields to append 1 at a time
Dim fld As Field
Dim sTxt As String
Dim vSrcTbl
Dim rst
Dim tdf As TableDef
Dim iStartFld As Integer, iFldCnt As Integer, f As Integer

iStartFld = 3    'append starting at field# 3
vSrcTbl = "table"
Set tdf = CurrentDb.TableDefs(vSrcTbl)

f = 1
DoCmd.SetWarnings False
For Each fld In tdf.Fields
   If f >= iStartFld Then
        sSql = "insert into tTargTbl (Budget, Role, MON) SELECT [Budget],[Role], [" & fld.Name & "] from " & vSrcTbl
        DoCmd.RunSQL sSql
   End If
   f = f + 1
Next

DoCmd.SetWarnings True
Set rst = Nothing
Set fld = Nothing
End Function

[June7]

Or use a UNION query to rearrange the data into normalized structure. Then either use the UNION like a table in subsequent queries or save those records to a new table.

Then no VBA needed.

How many MON fields are there? UNION has a limit of 50 SELECT lines.

Your data is a string value for MM/YYYY. This is a poor way to store date data. At least should have YYYY/MM to facilitate chronological sort. Otherwise use string manipulation functions in queries to separate the MONTH and YEAR parts when needed.

[ranman256]

ahh true.
my touting NO VB just bit me.

[Missinglinq]

Your thread title and post is confusing because you keep referring to 'variables,' when you're actually referring to Fields! And you are correct in wanting to 'collapse' the Fields named Mon1, Mon2, Mon3, etc. into a single Field! A string of Fields named in this manner is almost always a sign of a non-normalized database. And while you haven't responded, as yet, to June7's question, it would appear that the number of possible months is infinite, another sign of non-normalization! What you have, here, in essence, is a spreadsheet, not a database. You should simply have a single Field named Mon, and enter the appropriate numerical designation for that month. With a little code you could actually have this number incremented automatically anytime a new record is entered for a given Budget/Role.

Linq ;0)>

[IrogSinta]

Having a record for each budget/role combination each month doesn't really make sense. Taking your first combination of Budget 1/Role 2, you would end up having 12 records; however, you can have a single record instead if you set up your table in a manner similar to either of the following:

Budget	Role	StartMonth	EndMonth
1	2	2015-03	2016-02

Budget	Role	StartMonth	NumOfMonths
1	2	2015-03	12

I also agree with June7 about the formatting for your months. Using yyyy-mm format would make querying and sorting much easier.

Ron

[cam2era]

Hi All,

Sorry for the delay in getting back. The Union Qry seems to work very well. The financial team is looking out two years, so the max Month fields they're asking for is 24. However, the grants they administer often go for 5 years, so it's possible that they could come back one day with 6o months, but that's just me guessing. For now I'll work with the Union Qry. I'm not a coder, but can work easily with queries.

Thanks for the correction of Fields vs Variables. I've already formatted as yyyy-mm, so sorting works well.

I've chosen an underlying table structure as Ron suggests in the first example: Budget, Role, StartDate, EndDate. This is info that the financial team will have readily available and I can make forms for them to use. They want to see salary info in monthly increments, so I'm dividing the time range and salary into months based on the StartDate, EndDate. I've set up the Union Qry to then list the months into one field, "Month". Then I'm using PivotTable view to show them the monthly salary info. So far, so good. The resulting view is very similar in layout to the spread sheet they've been struggling with for several years!

Thanks for helping me get this going. I'll update if/when I run into any difficulties!
-Ernie