By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,163 Members | 944 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,163 IT Pros & Developers. It's quick & easy.


P: 92
I have a problem in calculation.
i have a access table that have entry for differnt dates.
the data entry into this table is cumaltive and weekly and i hav eto get a daily report.
i mean it shows todays sell 2500$
tomorow the sell is 5600$ but actually it is 3100$
the day after 7600 but actually it is 1000$

i dont know how can i say minus today from yesterday for each record?
the sell is cumelative for each week . at the begining of the week it will start from 0

i would appreciate any help

Dec 10 '07 #1
Share this Question
Share on Google+
5 Replies

P: 167
Hi Voorojak!

Do you keep the info about the sales for only one week long? That would be strange. You might need this information for monthly and yearly reports.
Could you enter sales separately for only one day/date in your Sales table. Then you could make a query to select from Sales table only sales for the week (between Start date and End date) you choose. This way you could make monthly reports too (just enter: start date = 1st of January 2007, and End date = 31st of January 2007)

Ofcourse you must have a Total on that report that shows total amount of sales for specified period.

Other solution would be to have another cell containing cumulative value one day behind (total from yesterday). The difference between these two cells would be amount of sales for today.

I'll be here if you have more questions on this.
Dec 10 '07 #2

P: 92
thanks for your reply
i do not just keep the sale report just for one week.
i meant that the sale is cumaltive for each week.
the table is some think loolk like this

run_date week_ending Sup sale_ID Sale#
10/12/2007 15/12/2007 MNA BHJ 3
10/12/2007 15/12/2007 MNA KLP 5
10/12/2007 15/12/2007 GTF SUP 6
10/12/2007 15/12/2007 DFO SUP 5
11/12/2007 15/12/2007 MNA BHJ 7
11/12/2007 15/12/2007 MNA KLP 8
11/12/2007 15/12/2007 GTF SUP 7
11/12/2007 15/12/2007 DFO SUP 9

but actualluy tthe sale BHJ for 11/12/2007 is 4 not 7. because he made 3 sale on 10th. but the report is showing the cumaltive value
each week is start from monday to sunday
What i have to do is that i add another column or query or what wver that i can say that this person on this date that will end by this week and supervisor is this person made 4 sales on 11th

thanks alot for ur help
Dec 10 '07 #3

P: 167
Hi Voroojak!
Is this problem occuring only with reports, and not with forms?
Check the Sales# control on the report. The running sum property should be set to none.
If the database is not too big you could attach it here so I can have a detailed look (or you can make a similar database just for that purpose).

Please explain how exactly do you enter info into the Sales# control.
If you use VBA code maybe Sales# variable is not empty before the next entry?

To attach the database make a zip file and after replying to this thread hit the Edit/Delete button on your lower right. Then you will have an option to add attachments!
Dec 11 '07 #4

P: 92
Thanks alot for your answer
let me explain it a little bit more . i hopei can explain it good.

the data that i pulled the information from is actually from IBM terminal 3270.
First I made a macro inside theapplication and copy all the pages that i wanted becaus ei had to sned so many keys. for example f6 to get this information then do somany page down and again f5f for other information and so on. after i copy and select all the data i wanted then i paste it in to a text file through Access abnd then read the lines through access.
the text file had so many information because when i was doing the f6 button i was getting all the sales order and then when i was doing f5 i was getting aall the refund and f6 again for all thise who did extra activity. these sales person have an supervisor and their productivity is calculated weekly.

the only way that i could understand which one is sales which one is refund was the unique number that was showing that the data below is sale or refund or ....
i had so many lines in between that i didnt need it.
at the end i was successful to read the line i want and put the sales in sales table and then refund in refund table and extra activity in extra table.
than i joined all three tables in one table as showing the productivity for each sales person

hopefully till here everything is workin ( i was close to hang my self because it was not working before). but the system in th emain application is in a way that do not show the daily productivity and it is cumulative
i meann if today the sale man sales 3 product and tomorow sales 2 but for tomorrow it is showing 5 not 2.
what i am doing now is that daily this report should be run and it will store in a table.
because in teh main application there was no date that shows that the report is run today and i needed to keep the information when i store in a table so i add another column that will show the run_date. run date is today's date .
if i run th ereport today that i wil get that for today's date(12/12/2007) that the week end is 15/12/2007 this person with this supervisor has made 3 sales.
tomorow (13/12/2007) that week end 15/12/2007 this person with this supervisor made 5 sales. when tomorow i run this report i have to do somethin that said ok now that the difference between today and yesterday is 1 then minus today's sale from yesterdays sale for this person.
but i dont know how to data
i made so many if condition
iif(run_date-run_date=1, confirmations-confirmations, confirmations)

that if the diiference was one day then subtract it other wise put todays confirmation( that is for monday that there i sno day before that).
or even i put iif(max(run_date)-run_date=1, confirmations-confirmations, confirmations)

but non of them is working.
i dont know how can i make it.

i used my brain alot but it seems it is useless and my brain is not working at all or does not working probably any more.:)

thanks alot
Dec 12 '07 #5

P: 167
Please attach the database her, because I don't understand how it works and how I can make it work the way you want it.
Feb 11 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.