I have 2 tables with same fields.One table are actual numbers one table are goals. These are tracked by yearly quarters. one table tracks the quarters in a numeric formula field and the other in two drop down text box (one for quarter and one for year). I need a report that compares the like fields, if they fall into the same quarter and date time frame.There is probably a simple answer but i am drawing a blank.
Make a new table called "Budget vs Actual" and load it up with the quarters for the next 10 years on the format, perhaps YYYY-Qx
or else, you said that one of your tables already has a "numeric formula field", so maybe that formula calculates to something like 2016-1.
Anyways.... come up with a coding for the YYYYQ and load up a table and make that field the Key field.
Then on each of your other tow tales, make a formula field to calculate to that Key field values for the YYYYQ.
Then make relationships back to each of your details table and then roll up the totals you need to the new table.
You need to build a text string by a formula text field in each of the two tables to calculate a result like 2016Q3. Do you know how to do that based in how the data is stored in each table.
For example if you have drop downs in one table for the year and another for the quarter, then it would just be
[Year] & "Q" & [Quarter]
I do not know the fields type you are using, so if they are numeric them you will need to use
ToText([Year]) & "Q" & ToText([Quarter])