sum query

  • Thread starter Thread starter MJ
  • Start date Start date
M

MJ

A table has list of 25 products.it has ProductID,Actual
Cost & quantity fields.now i want to make
query using this formula:

result=(((sum of Actual Cost from row 1 until row x)*75%)+
(sum of Actual Cost from
row (x+1) until end)))/total quantity

which will show me one result. And x is input by user
how to create query for such calculation?

TIA,
james
 
Hi,


There is no row order in a table. There is no possibility to have "up to
row", since "row" are free to move in relation among others when a data page
split occur, when a record is delete then the db compacted, etc. You have to
relay on "values" saved in the row, not to relay on "position" of the row,
unless you are in a recordset (clearly, they are a row sequence, since you
can move next or move back), by TABLE have no row sequence/order. If you can
say "up to March", then, you relay on data stored in the record (at least,
that sounds reasonable you stored a date_time value), you can say "the first
10 based on their gross revenue", if you saved the gross revenue as data,
but up to "actual position" or up to position x, niet! It is possible that
what you seek it NOT based directly on "until row x", but as long as you
bang your head in seeing just the "position x", not much can be done.
Database work on DATA, and on data they know. They know nothing about
"record position", because it is not fix, and it is not "maintain" in their
storage.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top