2. ASPRunnerPro General questions
  3. Calculating values
This topic is locked

Calculating values

 		11/10/2004 08:23:24
ASPRunnerPro General questions
D
 dnoakes author

I have 3 fields
Connections Purchased

Connections Used

Connections Remaining
The problem I have is that when a record is stored, it does not take into account a previous entry for Connections Purchased and so it thinks I have 10 per record.
Is there anyway round this, as I would like to see purchased - used = Remaining not by record but overall by supplier.
Anybody got any ideas as I am completley stuck on this one. Maybe acces has a way of doing it ???
I can provide more info if this is not clear enough.
Sergey Kornilov admin 11/10/2004

Hi,
here is the SQL query that can calculate Connections Remaining

select sum(purchased-used) as ConnectionsRemaining  from ...
D
 		dnoakes author 11/10/2004

Worked great but is totalling all of the connections as I have lots of different suppliers.
Can this be done by individual supplier without having loads of SQL queries ?
Sergey Kornilov admin 11/10/2004

Hi,
if you need to do the same for any specific supplier use the following:

select sum(purchased-used) as ConnectionsRemaining  from ...

where supplier = 'AnySupplier'
D
 		dnoakes author 11/10/2004

Sadly as I feared, it is not totalling by supplier it is only looking at the individual records, which is defeting the purpose of the SQL
Can this be done by supplier as the count looking line by line ?
I currently have
SELECT Sum(Purchased-Used) AS Remaining, tablename.supplier

FROM table

Group by tablename.supplier
Will this work in a ASP Environment ?
Sergey Kornilov admin 11/10/2004

You can create a view/query on the top of your SQL statement and use this query as a datasource in ASPRunner. Probably this is what you looking for.
D
 		dnoakes author 11/10/2004

That works a treat thanks <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=2773&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />