Figuring out sequential month and year for 60 fields.

[TerryHogarth21]

Bit stumped on this example below – was wondering if anyone has a workaround or easier way of doing this.

I have 2 tables in an Access DB

1 table entitled ‘Temp_Import’ that has 61fields (Invoice Date, Month 01, Month 02 all the way to Month 60) Yes this is not normalized, so I’m trying to do so.
In the second table entitled ‘Param_Tbl’ with 4 fields (Sequence_Num, Data_Type, Recognized_Month, Recognized_Year)

I have a select query that I can utilize to Insert in the first record for fields in the ‘Param_Tbl’

 SELECT "Month 01 Recognized Amt" AS Type, IIf(MAX(MONTH([Temp_Import].[Invoice Date])+1)>12,1,MAX(MONTH([Temp_Import].[Invoice Date])+1)) AS Recognized_Month, IIf(MAX(MONTH(Temp_Import.[Invoice Date])+1)>12,MAX(Year(Temp_Import.[Invoice Date])+1),MAX(Year(Temp_Import.[Invoice Date]))) AS Recognized_Year
FROM Temp_Import;

Basically in the temp table, the max month of invoice date +1 will be used unless it goes above 12 which then it would go back to month 1. The year would be the year of the max invoice date unless the month of the max invoice date +1 then it would be max year invoice date +1. The starting point is easy but the subsequent 59 is difficult.

Param_Tbl
Data_Type	Recognized_Month	Recognized_Year	Sequence_Num
Month 01	5	2015	1
Month 02	6	2015	2
Month 03	7	2015	3
Month 04	8	2015	4
Month 05	9	2015	5
Month 06	10	2015	6
Month 07	11	2015	7
Month 08	12	2015	8
Month 09	1	2016	9
Month 10	2	2016	10

<tbody>
</tbody>

I was thinking maybe just do a bunch of union’s from 1-60 but I am having trouble writing the logic sequentially. Last case result is to just write the formula in Excel and just import that as the table.

 

[sheetspread]

Maybe add a where clause with the right function to exclude earlier months from each union query select?

If this is a one time cleanup to get the data in a normal structure, then maybe Excel is the best way. Or you could write some VBA code in either Excel or Access to build the query string once you've found the pattern, which seems to be the main challenge.

 

[TerryHogarth21]

Maybe add a where clause with the right function to exclude earlier months from each union query select?

If this is a one time cleanup to get the data in a normal structure, then maybe Excel is the best way. Or you could write some VBA code in either Excel or Access to build the query string once you've found the pattern, which seems to be the main challenge.

Yea it's just a simple formula in Excel that can create that table - but it will have to be monthly because those 60 fields will change depending upon the report being run. I'm not positive on the SQL equivalent. I'll try some DAO to see if that will work but it's in essence creating a record based on the last record and repeating it 59 more times.

 

[sheetspread]

it's in essence creating a record based on the last record and repeating it 59 more times.

Yes Excel does that much better than Access. Can you use the sequence number (or primary key) ?

 

[TerryHogarth21]

Ok I got it to work by utilizing DAO and some dlookups based on the sequence number.

Option Compare Database

Sub NewParameters()
'Update the necessary fields in the Def_Rev_MY table utilizing DAO recordset

Dim dbsDefRev As DAO.Database
Dim rstMY As DAO.Recordset
Dim x As Long, y As Long, z As Long

   Set dbsDefRev = CurrentDb
   Set rstMY = dbsDefRev.OpenRecordset("Def_Rev_MY") 'set the table
   
   'record set begin
    x = 1
    
   'starting month
    y = DLookup("Recognized_Month", "Def_Rev_MY", "Sequence_Num = " & x)
   'starting year
   z = DLookup("Recognized_Year", "Def_Rev_MY", "Sequence_Num = " & x)
   
   rstMY.MoveFirst
   Do Until rstMY!sequence_num = 60
      'You must always figure out the last sequence number and record set
      'in order to set the new sequence number and record set fields
      
      'When the sequence number is x, the month is less than 12
      'This implies that you must set the next record to month +1 and keep year the same
      If rstMY!sequence_num = x And rstMY!Recognized_Month < 12 Then
            
            'move onto the next record set
            rstMY.MoveNext
            
            rstMY.Edit 'Edit the record set
         
            'Recognized Month and Recognized Year
            rstMY!Recognized_Month = y + 1
            rstMY!recognized_year = z
            rstMY.Update
         
            'increment variables
            x = x + 1
         
            'starting month
            y = DLookup("Recognized_Month", "Def_Rev_MY", "Sequence_Num = " & x)
            'starting year
            z = DLookup("Recognized_Year", "Def_Rev_MY", "Sequence_Num = " & x)
                
                Else 'sequence number is x, the month is 12
                'implies that month must be 1 and year is year +1
                
                'move onto the next record set
                rstMY.MoveNext
                   
                rstMY.Edit 'Edit the record set
                
                'Recognized Month and Recognized Year
                rstMY!Recognized_Month = 1
                rstMY!recognized_year = z + 1
                rstMY.Update
                
                'increment variables
                x = x + 1
                
                'starting month
                y = DLookup("Recognized_Month", "Def_Rev_MY", "Sequence_Num = " & x)
                'starting year
                z = DLookup("Recognized_Year", "Def_Rev_MY", "Sequence_Num = " & x)
        
      End If
   Loop
   
End Sub

 

[sheetspread]