Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2009
    Posts
    5

    Question Unanswered: need help totaling 2 records????

    Ok I am not the greatest w/ writing SQL statements for Access. I am using Dreamweaver and Access 2007 I have a DB that was written by a 3rd prty company that tracks race results. Here is a link http://www.bigfootdesigns.com/mrp2/database/db.zip to a dummy setup in that DB. The idea is that once the races are complete that just he DB can get uploaded and results will display on the site.

    I can get the basic info out of it but I am having trouble getting the total of 2 records. Here is what I am trying to do. If 1 racer races round 1 and round 2 and say he places 1st on both nights. he receives 25 pts the first round and 25 pts the 2nd round 50 total for 2 rounds. So in the DB the 2 races are labeled (tb_event_data.event_name)LumberJack CDA 1 and lumber jack cda 2 each with (tb_event_entries.main_points)25 pts. What is the syntax to add these records together for the 2 rounds and so on though the 6 rnds?

    So when I call the info to the page I get something like this.


    CDA1
    class - Name - moto1 position - moto2 position - rnd1 pts total

    CDA2
    class - Name - moto1 position - moto2 position - rnd2 pts total

    Overall points
    class - Name - pts total

    If anyone can help or point me in the right direction.....I have tried using the query function in access but when I put it into Dreamweaver it does not read the SQL correct and errors.

    thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In a query, you can group by the racer, and anything else you want to group and sum the total field.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2009
    Posts
    1

    Group

    group by the racer

  4. #4
    Join Date
    Jan 2009
    Posts
    5
    Will this work....still trying out the kinks an how I call it dreamweaver
    SELECT series.series_desc,
    event_data.event_id,
    event_data.event_name,
    event_data.track_name,
    event_data.event_date,
    b.class_id,
    b.class_desc,
    event_entries.racer_id,
    racer_info.fname,
    racer_info.mi,
    racer_info.lname,
    racer_info.racing_nbr,
    event_entries.overall_finish,
    event_entries.main_points,
    machine_brands.machine_brand_shortdesc,
    b.sort_order
    FROM class a,
    class b,
    event_data,
    event_entries,
    racer_info,
    machine_brands,
    series
    WHERE (( series.series_id = :ai_series) ) and
    ( racer_info.machine_brand_id = machine_brands.machine_brand_id ) and
    (( series.series_id = event_data.series_id ) or
    ( series.series_id = event_data.series_id2 )) and
    ( event_data.event_id = event_entries.event_id ) and
    ( event_entries.racer_id = racer_info.racer_id ) and
    ( event_entries.class_id = a.class_id ) and
    ( a.points_class = b.class_id ) and
    ( a.racing_class = 'Y')


    B

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well I am not a machine so I don't know. To find out if it works, TRY IT.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by StarTrekker
    Well I am not a machine so I don't know. To find out if it works, TRY IT.
    good point, and well made........

    I've never understood why people post something (such as a query) and ask will it work..... providing the query doesn't delete data whats the worst that can happen? (and if it did delete data you'd be working from your backup wouldn't you... WOULDN'T YOU)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed... and working on backups for UPDATEs as well!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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