Turn of the year

[djeeten]

Hi,

I have created this database, allowing users to make orders etc. There are several tables in the database, such as tblCustomers, tblProducts, tblOrders,...

Every user (from the table tblCustomers) is allowed to place orders for certain product groups. So a certain user may not be allowed to place an order for certain products.
Therefore I made a table tblBudget. In this table, there's a joining between the customer and a product group.
A record in the table could be:

Customer ProductGroup StartBudget CurrentBudget
-------- ------------ ----------- -------------
customer1 food 10000 9000

-------------------------------------------

A user has a budget for 1 year. When he places an order, the total amount of the order is substracted from his budget(CurrentBudget). The user can also, at all times have a look at what he has already spent for a certain product productgroup (so how much there's left of his budget(StartBudget-CurrentBudget)), and on which products he spent his budget (I can easily find this using the table tblOrders).

Now, with the turn of the year, the current budgets should be set to their original values. The overviews of the expenditures(how much and on which products) should still be available, but of course the expenditures from the new year have to be seperated from the expenditures of last year.

Every order a customer makes has of course an order date (including the year), so I guess I will have to use that, to seperate the orders from different years. So I should probably base the form where the customer gets the overview of his expenditures on the current year. Still, I'm not really sure and I don't know how I can test this. On the other hand a user should also be able to see his expenditures of last year. Maybe it should be better to create a new table tblOrders for the new year as well, but again I'm not very experienced with this.

Has anyone had experiences with a problem like this? I would realy appreciate any help!

Thanks in advance,

dj.

 

[WildHare]

I haVe not done a lot of budgeting type systems such as this, but I think you need to be careful about maintaining historical data - thus you don't want to just "start over with the original amount". You might need to create an archive table with the prior year's budget expenditures, and start a new table each year for the customers current budget expenditures.



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[djeeten]

Hi,

Thanks WildHare for your answer. You're right, budgets can change from year to year.

I'm still not so sure about what I should do though... You see, the table tblOrders (together with the table tblOrderlines) will be huge after one year. So I think it will be better to create a new table on the 1st of January.

This would be an ideal scenario: on the first of January, three new tables are created. These tables get a name like: tblOrders2002, tblOrderlines2002 and tblBudget2002. Everything from the tables tblOrders, tblOrderlines and tblBudget gets inserted into the new tables. Also everything in the tables tblOrders and tblOrderlines should be deleted and all current budgets in the table tblBudget are updated to the value of their start budget.

I don't think much of what I said here is possible? Maybe it would be better to just copy the whole database on the 1st of January and then delete all records from the tables tblOrders and tblOrderlines and set the budgets in tblBudget to their original value?

But then there would be another problem, as the customers have to be able to have a look at the expenditures of last year. I could let them use last year's database, but they shouldn't be able to place orders using that database...

I'm not getting anywhere here...

Please help me! Thanks a lot in advance,

dj.

 

[koresnordic]

depends on how many years you want to keep. You could have another set of tables called

minus1,

minus2 etc. Then have a delete query that deletes the data in minus2 tables. Then an append query copies all of minus1 into minus2. delete query to clear minus1, append query to move current into minus1. Then whatever you use to recreate this year. Then you could have a set of historical forms, where the users can enquire, but make all the data fields locked, so they cannot change.



Graham

 

[djeeten]

Hi,

I still don't know how to solve my problem. In the table tblOrderlines there will be inserted about 20.000 records a year (in the table tblOrders about 2.000 a year). Will this give serious speed & performance problems after a few years???

Thanks in advance,

dj.