Modify data

Hi all:

We have a web site set up where we can enter data into a database, then
display it in a web page. The table includes a fields named ptsID, team,
points, totPoints, and tourn.

The form we are using to enter data includes a name field, a tourn field,
and a pts field. When submitted, the page that processors this data first
first pulls the last record for the team and calculates the new totPoints
value based on the old value plus the new points being entered. We then
display this information in a web page.

All of this is working well, except if we find an error in the points
entered say 2 or three tourn records prior to the current one. This creates
a problem for us, in that we then have to go thru and update the totPoints
field manually to correct the error.

We can update the pts field for each team/tourn record, but we can not
figure out how to then loop through all of the records coming after the
correction to update the totPoints field to the correct value.

Any help would be greatly appreciated

Steve

Re: Modify data

Steve,

I'd suggest having the Web pages calc the totals each time a Web page is
displayed (assuming the site is not a high traffic site) instead of
trying to store calculations in the database.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

<<
We have a web site set up where we can enter data into a database, then
display it in a web page. The table includes a fields named ptsID, team,
points, totPoints, and tourn.

The form we are using to enter data includes a name field, a tourn
field,
and a pts field. When submitted, the page that processors this data
first
first pulls the last record for the team and calculates the new
totPoints
value based on the old value plus the new points being entered. We then
display this information in a web page.

All of this is working well, except if we find an error in the points
entered say 2 or three tourn records prior to the current one. This
creates
a problem for us, in that we then have to go thru and update the
totPoints
field manually to correct the error.

We can update the pts field for each team/tourn record, but we can not
figure out how to then loop through all of the records coming after the
correction to update the totPoints field to the correct value.

Any help would be greatly appreciated
>>

*** Sent via Developersdex http://www.developersdex.com ***

Re: Modify data

Thanks for responding Paul. That was my thought as well, but that created
problems displaying the data correctly.If I used the aggregate method, it
required all preceding records to be included in the recordset opened, and
in turn displayed all such records when I looped through it. This created
quite a mess when displayed. If I used the sum method, then the point count
for that specific tournament was not displayed, and again, it required all
preceding records to be included. Soooo....I admit I am not great at this,
but storing the calculation in the database seemed to be the best way around
those problems.

I am very open to any ideas.

Steve

"Bullschmidt" wrote in message
news:Ogoef9M6FHA.4012@TK2MSFTNGP14.phx.gbl...
Steve,

I'd suggest having the Web pages calc the totals each time a Web page is
displayed (assuming the site is not a high traffic site) instead of
trying to store calculations in the database.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips

<<
We have a web site set up where we can enter data into a database, then
display it in a web page. The table includes a fields named ptsID, team,
points, totPoints, and tourn.

The form we are using to enter data includes a name field, a tourn
field,
and a pts field. When submitted, the page that processors this data
first
first pulls the last record for the team and calculates the new
totPoints
value based on the old value plus the new points being entered. We then
display this information in a web page.

All of this is working well, except if we find an error in the points
entered say 2 or three tourn records prior to the current one. This
creates
a problem for us, in that we then have to go thru and update the
totPoints
field manually to correct the error.

We can update the pts field for each team/tourn record, but we can not
figure out how to then loop through all of the records coming after the
correction to update the totPoints field to the correct value.

Any help would be greatly appreciated
>>

*** Sent via Developersdex http://www.developersdex.com ***

Re: Modify data

>If I used the sum method, then the point count
>for that specific tournament was not displayed, and again, it required all
>preceding records to be included. Soooo....I admit I am not great at this,
>but storing the calculation in the database seemed to be the best way around
>those problems.
>
>I am very open to any ideas.

Well running sum totals can be reset for each group. And here's a
short grouping article I put together:

Classic ASP Design Tips - Grouping Data
http://www.bullschmidt.com/devtip-groupdata.asp

Here's a little something I wrote to myself awhile back about using
subqueries (in case that might help at all):

Example of one query (QueryB) based on the results of another query
(QueryA):

QueryA = "SELECT CustID FROM tblCUSTOMERS WHERE CustName = 'A%'"

QueryB = "SELECT CustID, CustName FROM tblCUSTOMERS WHERE CustID IN ("
& QueryA & ")"

But the following is even faster and allows for more than one field to
be returned in QueryA:

QueryB = "SELECT tblCUSTOMERS.CustID, CustName FROM (" & strSQLA & ")
AS tblSQLA INNER JOIN tblCUSTOMERS ON tblSQLA.CustID =
tblCUSTOMERS.CustID"

So QueryA would include all the CustID's for customers starting with A.

And QueryB would include more fields in the customers table (i.e. not
just the CustID field) for the records returned in QueryA (which was
the customers starting with A).

I suppose it wouldn't hurt to always use LEFT JOIN's in QueryB and
build from the tblSQLA on the left to other tables that have fields you
want to return.

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer
http://www.Bullschmidt.com
Access Database Sample, Web Database Sample, ASP Design Tips