# I have 2 tables with same fields.One are actual numbers one are goals.I need a report that compares the like fields,if they fall into the same quarter and date time frame

• 0
• Question
• Updated 3 years ago

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.

• 10 Points

Posted 3 years ago

• 0
• 72,206 Points
No problem.

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.
• 10 Points
Thank you for your quick response. I have been out but I am getting stuck in making the formula field on the two other tables. What find of formula field? Numeric?
• 72,206 Points
It would be a formula text field and I will post an example of what that formula would look like today or tomorrow.
• 10 Points
Thank you so much!
• 72,206 Points
actuallyI stared to answer your post, but I don't have enough information.

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])
• 10 Points
Can I please invite you to the app? I am not following.
• 72,206 Points
You can contact me via the information in my Profile.    I'm at 30,000 feet  right now in a plane with wifi :)
• 10 Points
I sent you an invite. Whenever you have time to look, I need to pull Injury/Illness Record totals and SCA Occupational Survey table information together to compare. I have made the table OSHA/PSI Reporting table as a comparison table but I am stuck at this point.
• 72,206 Points
I made a relationship where 1 SCA Surveys have many Injuries, based on the calculated field in the format 3Q15.  You can then do any summary fields you like.  is that what you are after.  I could not understand the purpose of the middle table of the three tables, so I was not sure if there was information there which also needed to be summarized, since that table only has 1 record per quarter. ie I think that your gaols table only has  quarterly entries so the detail injuries can be summarized into the quarters, just not sure which table you want to use for your summaries.
• 10 Points
That is exactly what i am looking for. I was trying to make it too confusing by adding another table to compare. Thank you so much for your help and have a safe trip.