add marked up target sales against all customers per month

[Ali29J]

Hi All

I have table showing previous years sales based, against month, format as below:

PartnerID Type Jan Feb Mar
01113 YT-1 23 55 34
01113 YTD 33 45 33
01123 YT-1 44 55 43
01123 YTD 42 51 48

partner id is customer identification
type YT-1 = last years sales
type YTD = actual sales this year

What i would like to do is to add a complete new line for each partnerID with a target, which is based on YT-1 marked up by a percentage entered into a text box on a form i.e YT-1 + 10% markup for example

PartnerID Type Jan Feb Mar
01113 YT-1 23 55 34
01113 YTD 33 45 33
01113 Target 36.3 49.5 36.3
01123 YT-1 44 55 43
01123 YTD 42 51 48
01123 Target 46.2 56.1 52.8

all this would happen on a form open, and each partner id would need to have the new line added, i really dont know where to start....

could anyone offer some help?

Ali

 

[Pampers]

So your table looks like:

tblYearSales
------------
PartnerID
(Year)Type
Jan
Feb
March
...
Dec

If you want to add a target, based on last years sales plus 10%, one could use a calculated field for that. Since calculated fields are (normally) not stored in any table, a query with that calculated field would be appriopriate.

If the mark up % is a variable (TargetPerc), add this field to your table, and use it in the form. In your query you could then put:
SalesTarget = [TargetPerc]*[YT-1]



Pampers
Keeping it simple can be complicated