Forum Discussion

MattHartman's avatar
MattHartman
Qrew Trainee
7 years ago

Tables comparing goals with actuals using a count mechanism.

Hi All. I have a table in which each record represents a real estate transaction and I have two "Types" of transactions (Seller and Buyer) and various "Sources" of business (open house, referral, farm, etc). I have another table that has the goal for each real estate agent- number of each "Type" (seller or  buyer) by each "Source". Thus I want another table to compare their actual results (the transaction table) and their goals (goal table). I figure I need a way to have summary fields that count the type and source of each transaction but don't know the best way to achieve that. In the goal table, the source and type are represented such as Sellers - Open House =2 , Buyers - Open House = 1 and so on for every combination of type and source. So I'd like to have identical fields that are for actual results that reference the transaction table and count baed on the values in the Type and Source fields. Any help would be greatly appreciated!
Formulas and functions
Relationships
Tables and fields
  • DavidBrogdon's avatar
    DavidBrogdon
    Qrew Assistant Captain
    7 years ago
    If you want to compare values between the tables you will need a relationship between the two. If it was me I would create the Goals table as the parent and the transactions as the child. Then summarize up the transactions for each type to the goals table. Then create formula numeric fields on the goals table to calculate the diiference between the goal values(parent) and the transaction(child) values. You could even create a table above the Goals table to stand as a totals table for all agents goals vs transactions.