State FY Qtrs converted to Fed FY Qtrs got to be a better way.

[iowabuckmaster]

Fed Qtrs run 1 qtr later then State Qtrs.

In a table. I have a field [Reporting Period] from data entry that contains the State Qtrs. In a query, I have a field for Fed Qtrs that I want filled automatically from the State Qtrs value. I have a rather large expression that works but is there a better way. Here is my new field called Fed Reporting Period.

Fed Reporting Period: IIf([Reporting Period]="Qtr1 Exp","Qtr4 Exp",IIf([Reporting Period]="Qtr2 Exp","Qtr1 Exp",IIf([Reporting Period]="Qtr3 Exp","Qtr2 Exp",IIf([Reporting Period]="Qtr4 Exp","Qtr3 Exp",IIf([Reporting Period]="Budget","Budget",IIf([Reporting Period]="Qtr1 Rev","Qtr4 Rev",IIf([Reporting Period]="Qtr2 Rev","Qtr1 Rev",IIf([Reporting Period]="Qtr3 Rev","Qtr2 Rev",IIf([Reporting Period]="Qtr4 Rev","Qtr3 Rev&quot))))))))

I had built a 2 column table with with the first column filled in with the State qtr and then in the 2nd column the matching Fed Qtr. Thinking I could use something similar to the vlookup in excel. Couldn't find anything. I just feel like I am missing something simple. Any advise? Please.

After this I have to figure out the correct Fed FY after user enters the State FY. ? Any advise? Please.

 

[Golom]

Your approach is correct. You need to do an INNER JOIN between this table and the one containing the State-Federal budget correlation. Something like this

   Select ..., SFBudgets.[Fed Reporting Period], ...
   From tbl INNER JOIN SFBudgets 
        ON tbl1.[Reporting Period] = SFBudgets.[Reporting Period]

where "SFBudgets" is the name of the table that contains the State to Federal correlation. "tbl" is the current table. Your long, messy IIF statement is replaced by SFBudgets.[Fed Reporting Period]

 

[iowabuckmaster]

Here is what worked. I struggled a while. I added the new two column table(called tblReporting Periods)that has the State Reporting period in the first column and the Fed Report Period Equivelant in the 2nd column) to the query. Then I added the field to the query Fed Reporting Period from the tblReporting Periods table. Then in the criteria for this field I added this select statement to the criteria.

(Select[Fed Reporting Period] FROM [tblReporting Periods]Where[tblAccounting].[Reporting Period]=[tblReporting Periods].[State Reporting Period])

Thanks for getting me going in the right direction. Not sure if this is the way you intended me to go but I now have two ways to get the same result.

Now I have to figure out the Fiscal Year thing. Qtr1, Qtr2 and Qtr3 will be easy. Because the State Fiscal Year for these qtrs will be the same for Qtr2, Qtr3, and Qtr4 of the Feds. But Qtr1 of the State Fiscal Year will be Qtr4 for for the previous Fed Fiscal Year. I think I will create another Table and use a select statement again. What do you think?