I am writing a sales and commission report in Crystal 10. I have 2 databases. One in SQL 2000 and the other in Access.
SQL contains all the sales numbers, order date, items, sales, cost etc.
Access contains all the goals, sales, new business goals
The dbs are linked based on Salesrep last name (we are small, they are all still unique). So, if the account manager for an account in SQL is useridmaster.lastname it is linked in Access to Labelmatchers.lastname. This works fine.
My report groups are as follows:
G1: useridmaster.lastname (SQL and last name of Salesrep)
G2: orderheader.activationdate (SQL and date the order was billed (is commissionable))
GROUPED BY MONTH
G2: orderheader.headordernumber (SQL and order number)
As mentioned, Access carries all the goals. I want to compare the monthly sales goal in access to the actual sales results from SQL.
My problem: I can not figure out how to pull the sales goal for the month in group footer 2. Simply pulling sales_goal.month from table Sales_Goal in access pulls the current month goal only. I did extract the month for GF2 from the orderheader.activation date as follows:
Formula Name “Month” MONTH({order_header.ActivationDate})
This returns the month number properly in group footer 2.
Now it need to somehow reference that month number back to the table that contains the monthly sales goal. Fields in this table are as follows:
Table Name: Sales_Goal
ID
Employee ID
Month (integer)
Sales Goal
Year
I am a novice and hope I am providing all the detail that is required. Any help is certainly appreciated.
Thank you.
SQL contains all the sales numbers, order date, items, sales, cost etc.
Access contains all the goals, sales, new business goals
The dbs are linked based on Salesrep last name (we are small, they are all still unique). So, if the account manager for an account in SQL is useridmaster.lastname it is linked in Access to Labelmatchers.lastname. This works fine.
My report groups are as follows:
G1: useridmaster.lastname (SQL and last name of Salesrep)
G2: orderheader.activationdate (SQL and date the order was billed (is commissionable))
GROUPED BY MONTH
G2: orderheader.headordernumber (SQL and order number)
As mentioned, Access carries all the goals. I want to compare the monthly sales goal in access to the actual sales results from SQL.
My problem: I can not figure out how to pull the sales goal for the month in group footer 2. Simply pulling sales_goal.month from table Sales_Goal in access pulls the current month goal only. I did extract the month for GF2 from the orderheader.activation date as follows:
Formula Name “Month” MONTH({order_header.ActivationDate})
This returns the month number properly in group footer 2.
Now it need to somehow reference that month number back to the table that contains the monthly sales goal. Fields in this table are as follows:
Table Name: Sales_Goal
ID
Employee ID
Month (integer)
Sales Goal
Year
I am a novice and hope I am providing all the detail that is required. Any help is certainly appreciated.
Thank you.