Help- Please I need all I can get setting up my form

[dywaynewatkins]

I am a sales rep and have built a database to keep up with all my customers and how much i should get paid, etc.

Ok, I have a form called CustomerInfo. which i enter in all my customers in. and on this form it gives calculates all my pay etc.. on this form i have a field that has to be entered in order to calculate pay properly.

The field is Month and it is a drop combo field.
i get paid on teirs. for example if i make 20 sales i may get paid 200 if i make 25 it may be 300 per sale
the levels are reset each month
What i want to happen is when i select the month i want it to tell me what number of the month this is, thats not a problem using the sum function of a query.

But here is my problem: lets say that i have 10 sales in the data base now.

here is an example of the levels
1- 3 sales 150 base pay
4- 7 sales 200
5- 9 sales 250
now i have entered 10 sales when i entered sale 1 it is bound to that field, when i enter sale 2 or more the total of sales for all the previous sales are not brought up to date they still have the old total.

How can i make all the sales in the month of november be the same number?
How Can i keep all the months together with out messing up other months? for example I sold 3 systems in november
and maybe 10 in december.
all sales in november need to be calculated at one one pay scale while december at another

is all this confusing?
is any one available for some one on one help?
THANKS IN ADVANCE

 

[KenReay]

Hi

IS your problem that you need to group your sales by month, but what you have is a date sold on?

If so the Month() function may help you, it returns a number 1-12 corresponding to the month of the date so Month(Date()) would return 11 (today being the 27 Nov 2002).

If you timescale extends over the calendar year end, you should perhaps also look at Year() which returns the year portion of a date.

I would have thought by grouping and counting on Month() and Year() of your Sales transaction date you can achieve your objective.

Hope this helps Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[dywaynewatkins]

Well There is alot of issues here i have to work with

Yes i do want to group my sales by month
That part is easy i have a combo box and i select the month, then after update or change i have the query reun and gives me a total of what the entered month is in a separate field and all my calculations are based on this field. here is my problem, on my "total field" which is controled by the query running, only chages the field on the current form.

Here is what i want to happen:
Say i enter a new sale for december and it is the 15th sale of December, then my old total would have been 14 now when i enter this new sale in it will say 15 sales for the month, on this form for this record only so all my other sales for the month of december are still calculated at the ool total, when i enter a new sale i want it to go back and update all the sales for that month and ONLY that month..I Would not want it to add another unit for the month of november's sales if this sale were for December. SO how do i do that ? My "total Sales for the month field" is unbound for right now

Please Help.
Thanks

 

[KenReay]

Hi

Is it not simply a case of rerunning the query which populates the TotalSales for the month (textbox) control?

It is not clear where you enter the individual Sales Transactions, is it on a seperate form to the one which is displaying your Total Sales?

If it is on the same form, then VBA code to rerun the totaling query in the after insert event (and after delete event) of the form will probably do it

If it is a seperate form, then VBA code to rerun the totaling query in the Gotfocus event of the form is probably appropriate Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[herdygerdy]

Hi
Reading thru the threads, I think I have some ideas about how to fix your problems.
Can you zip up and email me the db with some dummy data?
I can take a look and see what I come up with
Keep at it, and don't give up
Rgds/Tony