program to increase data by a percentage

[hellboy_ga]

I have a database that stores plant capacity information (tblCapacity)
and region sales information (tblSales). The Table that stores plant
capacity info includes the following fields; regionCode, plantCode,
recordedYear and plantCapacity. The table that stores the sales in the
region includes fields such as regionCode, recordedYear and Sales.

I have created a program using VB that copy the data from last year and
put it in the same table under current year and this program works for
both of these tables. The program runs a query and make a table of old
year data, run another query on that new table and change the old year
to current year, then it runs another append query to put the new
table's data into the original table (tblCapacity or tblSales). This
program works for both tables even though they contain different number
of columns.

However I want the program to increase the last year data (only year
dependent fields - plantCapacity and Sales) by a certain percentage
(that is picked by the user) and then paste it back to the current
table. How would I make a common program that can increase the year
dependent data by a certain percentage that can work for both tables?
This is because the number of tables may change so the program has to
be flexible. It is complicated and hard to describe so please ask for
any clarification.

Thanks in advance, any help is appreciated.

 

[hellboy_ga]

anybody?

 

[hellboy_ga]

can someone please help...really need to know this..

 

[Tim Ferguson]

However I want the program to increase the last year data (only year
dependent fields - plantCapacity and Sales) by a certain percentage
(that is picked by the user) and then paste it back to the current
table.

This sounds like a pretty straightforward UPDATE query:

UPDATE Sales
SET SomeField = 1.025 * SomeField
WHERE SomeCriterion = TRUE

How would I make a common program that can increase the year
dependent data by a certain percentage that can work for both tables?

Can't imagine why you'd want to do the same procedure to two tables. If
the things are in different tables it's because they are different
things. If they are the same things, then they should be in the same
table.

This is because the number of tables may change so the program has to
be flexible.

Yeccch: changing the numbers of tables is a bit like changing the number
of wheels on a car. At concept time it's fine; at design time it's a
headache but feasible; after the thing is on the road then it's a big big
big problem.

I think you need to think very hard about your design.

All the best


Tim F

 

[hellboy_ga]

What would you do if your boss keeps adding in stuff to the project?

 

[hellboy_ga]

"Yeccch: changing the numbers of tables is a bit like changing the
number
of wheels on a car. At concept time it's fine; at design time it's a
headache but feasible; after the thing is on the road then it's a big
big
big problem.
I think you need to think very hard about your design. "



What would you do if your boss keeps adding in stuff to the project?

 

[hellboy_ga]

"Yeccch: changing the numbers of tables is a bit like changing the
number
of wheels on a car. At concept time it's fine; at design time it's a
headache but feasible; after the thing is on the road then it's a big
big
big problem.
I think you need to think very hard about your design. "


What would you do if your boss keeps adding in stuff to the project?
Thanks for your reply.

 

[Tim Ferguson]

What would you do if your boss keeps adding in stuff to the project?

<g> you have my sympathy... you also have an education opportunity!

Seriously, your boss needs to give you a problem description based on the
needs of the _solution_, not the method -- unless, of course, he wants to
go away and do it himself. This applies to many aspects of work, of
course, not just databases. Nevertheless, you cannot be expected to hit a
moving target. Ask him to describe _what_ needs to be done, rather than
_how_, get him to sign it, and wave the paper in his face when he wants
to change it again.

In your turn, however, you need to be designing round things that are
obviously (or even just feasibly) going to change. You don't hardwire
names of warehouses into column names, because one day you'll have a new
warehouse. SalesProducts should be records in a table, not tables. Etc.
The things that you process are going to change; the entities that
describe the processes should not, as long as you have done the Systems
Analysis right.

Finally, the Archiving Problem is nearly always a paper tiger. There is
really no need to put records into tables for LastYear and
TheYearBeforeLast etc. Just mark the records Inactive; or simply use
queries to look at the current years workload.

Hope that helps


Tim F

 

[hellboy_ga]

thx tim, really appreciate it

going to sort it out with the boss

 

[Tim Ferguson]

going to sort it out with the boss

our thoughts go with you... <g>


Tim F