micamb
asked on
Calculating value of records from a column - classic asp
Yes - Classic ASP - I'm that old...
AnywaY - I have a table in my mysql database that I use to store all actions performed on a specific task. Let's call it TaskHistory. I have another table - let's call it TaskList - that contains the newest version of the task - a clumsy revision management system you might say.
Anyway - If my users choose the task category "Project" they are given the opportunity to enter a price for this current part of the project. Every time the task is updated a price is therefore added to TaskHistory containing (if applicable) a new price every time. Each of the tasks placed in TaskHistory have the same caseID appended as the original task in TaskList.
What I want to do is this: I want to run through TaskHistory & calculate the total from all of the tasks related to (e.g.) task 21. The current price in entered in Task 21 is 213, however if you calculate the total in TaskHistory the result is 5461. Is this possible?
Also: I have an overview page where I'd like this calculation done on every project in the database - so some kind of loop might be the solution. I'm a total arse head when it comes to programming stuff like this (ask my math teacher) so help is much appreciated!
BR
Michael
AnywaY - I have a table in my mysql database that I use to store all actions performed on a specific task. Let's call it TaskHistory. I have another table - let's call it TaskList - that contains the newest version of the task - a clumsy revision management system you might say.
Anyway - If my users choose the task category "Project" they are given the opportunity to enter a price for this current part of the project. Every time the task is updated a price is therefore added to TaskHistory containing (if applicable) a new price every time. Each of the tasks placed in TaskHistory have the same caseID appended as the original task in TaskList.
What I want to do is this: I want to run through TaskHistory & calculate the total from all of the tasks related to (e.g.) task 21. The current price in entered in Task 21 is 213, however if you calculate the total in TaskHistory the result is 5461. Is this possible?
Also: I have an overview page where I'd like this calculation done on every project in the database - so some kind of loop might be the solution. I'm a total arse head when it comes to programming stuff like this (ask my math teacher) so help is much appreciated!
BR
Michael
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
so just to clear things up, you have 2 tables with respective fields here:
table: Fejlmelding:
-Id
-Navn
-Lokale
-Opgavetype
-Beskrivelse
-Institution
-filepath
-Datomodtaget
-Datoafsluttet
-Status
-bemaerkninger
-Sagsnr
-Price
table: idFejlmelding
-cid
-Pris
which info you would like to display in your page?
is that we joining Fejlmelding.id with idFejlmelding.cid together?
table: Fejlmelding:
-Id
-Navn
-Lokale
-Opgavetype
-Beskrivelse
-Institution
-filepath
-Datomodtaget
-Datoafsluttet
-Status
-bemaerkninger
-Sagsnr
-Price
table: idFejlmelding
-cid
-Pris
which info you would like to display in your page?
is that we joining Fejlmelding.id with idFejlmelding.cid together?
ASKER
idFejlmelding contains the exact same fields as Fejlmelding. Only difference is cid which I use to mirror actions taking place in Fejlmelding. idFejlmelding contains multiple versions of the task found in Fejlmelding.
The Price field in fejlmelding is irrelevant for the table I'd like to display. That price is already part of idFejlmelding when the task is entered into the database the first time. So I need a column in my table that contains the calculation of all the Pris values in idFejlmelding that matches each specific task id in Fejlmelding.
The calculation needs to run through idFejlmelding and calculate the total price for a given task with the same id. Given that this overview page displays all tasks (taken from Fejlmelding which only contains the latest version of a task) it would have to run through all tasks in idfejlmelding and display the total for each task on this page.
So to answer your question to the best of my ability - the only link between those two is the id and the cid respectively. I.E.: There is ONLY one task with id 21 in Fejlmelding, but the task has been updated 7 times so in idFejlmelding you'll find 7 versions of the same task with cid 21. Each containing a different price. Makes sense?
BR
Michael
The Price field in fejlmelding is irrelevant for the table I'd like to display. That price is already part of idFejlmelding when the task is entered into the database the first time. So I need a column in my table that contains the calculation of all the Pris values in idFejlmelding that matches each specific task id in Fejlmelding.
The calculation needs to run through idFejlmelding and calculate the total price for a given task with the same id. Given that this overview page displays all tasks (taken from Fejlmelding which only contains the latest version of a task) it would have to run through all tasks in idfejlmelding and display the total for each task on this page.
So to answer your question to the best of my ability - the only link between those two is the id and the cid respectively. I.E.: There is ONLY one task with id 21 in Fejlmelding, but the task has been updated 7 times so in idFejlmelding you'll find 7 versions of the same task with cid 21. Each containing a different price. Makes sense?
BR
Michael
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Scott,
Thank you for a very elaborative answer! I think my database setup is almost ok - except that my overview takes its tasks from Tasks in your setup. An identical task is logged in Task history. So I can use Task history (idFejlmelding in my case) - I just can't figure out how to only display the latest edition of a task. Some tasks have been changed 5-6 times and they will then show up in my overview 5-6 times. How do I prevent that?
Thank you for a very elaborative answer! I think my database setup is almost ok - except that my overview takes its tasks from Tasks in your setup. An identical task is logged in Task history. So I can use Task history (idFejlmelding in my case) - I just can't figure out how to only display the latest edition of a task. Some tasks have been changed 5-6 times and they will then show up in my overview 5-6 times. How do I prevent that?
ASKER
I will refrain from calculation in my overview - it will be too heavy in terms of rescources.
ASKER
This is what worked on the individual task page:
Open in new window
Seems to work... However the overview page proves more difficult. Here's the code where I'd want this to happen. The issue is looking in a different table (idFejlmelding in this case), calculating the total of Price for each record that corresponds to each record in Fejlmelding. Inserting above code doesn't work - that only works on an individual level (when you have the id):
Open in new window