Comparing One Record to Another

[ahoneycutt]

I have a situation that I hope can be done without using VBA.

Let's say table looks like this.

Name Type Amount Percent Bonus
John DCP 5000 10
John Sales 4000 5

I need to look at record one and if John's DCP Amount is >=4000 AND his Sales are >=4000 his Bonus amount for DCP would be 5000 * 10 and his Sales Bonus would be 4000 *5 but if the DCP was not >=4000 he would get no bonus for DCP and no bonus for Sales because DCP drives his bonus.

So even if Sales were >=4000 because DCP was not the result would be 0 for both. Make since?

Is there any way to evaluate this without using VBA?

 

[plog]

How many types do you have? Just Sales & DCP? Or are there more?

 

[GohDiamond]

For your example:
Create a table called: SalesTracking
Fields called: SalesName, SalesAmount, SalesType,

Enter the 2 records as in your example:

Then paste the SQL statement into a Query:

SELECT SalesTracking.SalesName, SalesTracking.SalesAmount, SalesTracking.SalesType, IIf([SalesTracking_1].[SalesAmount]>=4000,IIf([SalesTracking].[SalesAmount]>=4000,0.05,0),0) AS SalesBonusIs, SalesTracking_1.SalesAmount, SalesTracking_1.SalesType, IIf([SalesTracking_1].[SalesAmount]>=4000,0.1,0) AS DCPBonusIs
FROM SalesTracking INNER JOIN SalesTracking AS SalesTracking_1 ON SalesTracking.SalesName = SalesTracking_1.SalesName
WHERE (((SalesTracking.SalesName)="john") AND ((SalesTracking.SalesType)="Sales") AND ((SalesTracking_1.SalesType)="DCP"));

The Query is an evaluation type and is not updateable, but if you want to play around with it, change the numbers in the table and the results will change dynamically.

Cheers
Goh

 

[ahoneycutt]

How many types do you have? Just Sales & DCP? Or are there more?

There are about 120 people and each one of those people can have at least 2 records listing Sales & DCP or they have another title however it contains DCP.

Each one of those people are measured on if they reach their DCP goal and if they do AND they reach their Sales goal then it is calculated.

However, some of the people have 4 records each and two of those records are not being measured only the *DCP* and *Sales*.

Does that make since?

 

[ahoneycutt]

Thanks so much.

However, there are about 120 people and each one of those people can have at least 2 records listing Sales & DCP or they have another title however it contains DCP.

Each one of those people are measured on if they reach their DCP goal and if they do AND they reach their Sales goal then it is calculated.

However, some of the people have 4 records each and two of those records are not being measured only the *DCP* and *Sales*.

Does that make since?

For your example:
Create a table called: SalesTracking
Fields called: SalesName, SalesAmount, SalesType,

Enter the 2 records as in your example:

Then paste the SQL statement into a Query:

SELECT SalesTracking.SalesName, SalesTracking.SalesAmount, SalesTracking.SalesType, IIf([SalesTracking_1].[SalesAmount]>=4000,IIf([SalesTracking].[SalesAmount]>=4000,0.05,0),0) AS SalesBonusIs, SalesTracking_1.SalesAmount, SalesTracking_1.SalesType, IIf([SalesTracking_1].[SalesAmount]>=4000,0.1,0) AS DCPBonusIs
FROM SalesTracking INNER JOIN SalesTracking AS SalesTracking_1 ON SalesTracking.SalesName = SalesTracking_1.SalesName
WHERE (((SalesTracking.SalesName)="john") AND ((SalesTracking.SalesType)="Sales") AND ((SalesTracking_1.SalesType)="DCP"));

The Query is an evaluation type and is not updateable, but if you want to play around with it, change the numbers in the table and the results will change dynamically.

Cheers
Goh

 

[plog]

However, some of the people have 4 records each and two of those records are not being measured only the *DCP* and *Sales*.

So for the purposes of this query you only care about DCP and Sales? I think I have it, but I need some sample data. Can you provide 2 sets:

A. Starting sample data. Give me data from the table itself--be sure to include enough to cover all cases.

B. Expected Results. Give me what you expect to be produced from this when you feed it data from A.

Be sure to include table and field names.

 

[ahoneycutt]

I put it in an Excel file so I could give you more information. It has 2 sheets. The first is what the data looks like and Sheet 2 is what I need it to look like.

Thanks so much.

So for the purposes of this query you only care about DCP and Sales? I think I have it, but I need some sample data. Can you provide 2 sets:

A. Starting sample data. Give me data from the table itself--be sure to include enough to cover all cases.

B. Expected Results. Give me what you expect to be produced from this when you feed it data from A.

Be sure to include table and field names.

 

[plog]

You moved the goalposts on me. Initially you wanted to peg your DCP bonus to 4000, which then made them eligible for a Sales bonus which was pegged at 4000 as well. Now you have this in your file:

*If *DCP* or Gross* or *EBIT* is >100000, Bonus * Bonus % & Sales Bonus * Bonus %, all other Metrics are not evaluated they automatically receive the bonus

What's this EBIT? No where have you mentioned it. In fact I explicitly asked if this only had to do with Types of DCP and Sales--you said yes. Now I see 8 different Types in your file.

Please give me a concise description of all the rules you want implemented.