Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Location
    Romania
    Posts
    4

    Unanswered: Selective calculation

    hello...



    what I need to do... i have to search the db for records matching a given id and make some calculations with those values.

    given the fact that i'm a newbie in access i need a little help...

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    the question is vague... so the answer is too.

    start experimenting with queries.

    new query in design view, use the show-table dialog to add the table containing the field that you want to match given id

    drag interesting fields into the query design grid (including the id column)

    type your given id in the criteria box in the id column and run the query.

    once that works, experiment with:
    -adding other related tables to the query design (a right-click will re-open the show-table dialog)
    -using the main menu query options to try a Totals query
    -using a form to feed the given id to the query criteria and using the query results to populate the form
    -and dozens of hours of other fun things to do!

    izy

  3. #3
    Join Date
    Aug 2003
    Location
    Romania
    Posts
    4
    Originally posted by izyrider
    the question is vague... so the answer is too.

    start experimenting with queries.

    new query in design view, use the show-table dialog to add the table containing the field that you want to match given id

    drag interesting fields into the query design grid (including the id column)

    type your given id in the criteria box in the id column and run the query.

    once that works, experiment with:
    -adding other related tables to the query design (a right-click will re-open the show-table dialog)
    -using the main menu query options to try a Totals query
    -using a form to feed the given id to the query criteria and using the query results to populate the form
    -and dozens of hours of other fun things to do!

    izy
    -------

    that's easy... i know how to do that... the problem is that i need to read the given id, it's not my choise. 2nd, the calculations are simply sums and differences... but they should be done between different records of the table.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    can you explain what you mean by "sums and differences ... between different records of the table" please?

    you can do sums with an aggregate function

    select sum(payment) as totalpayments
    from invoices
    where id = 12345

    you can do differences with a join but only if you have some way to distinguish the rows

    select exit.age - entry.age as yearsenrolled
    from registrations exit
    inner join registrations entry
    on exit.id = entry.id
    where exit.transactiontype='X'
    and entry.transactiontype='N'
    and entry.id = 12345

    rudy
    http://r937.com/

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    read given id from where?

    sums & differences: my preference is to make a recordset and do it all in code, but you really have to say a little more about the problem.

    easy-izy

  6. #6
    Join Date
    Aug 2003
    Location
    Romania
    Posts
    4
    ok... here's the problem...




    i have a table with the folowing fields: id/name/input/output/price/


    i have to know at one time how many products with the id "xxx" i have.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    select count(*) from yourtable where id='xxx'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •