Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: returning lowest result.

    i have a database with 2 tbles. 1 called events which list the events and the world record, and one listing the results.

    events table:
    heat, min, sec, mili secs(as world record)

    results table:
    heat, min, secs, mili secs

    each have results in.. i now need to create a query. that will do something like this...

    if results.event is LOWER(has a lower value) than events.mins then display the chosen info on screen.

    i dont know much abour databases so if any help can be offered wud be much appreciated.

    example:
    if the world record, min=1 secs=22 mili=09
    and
    if the result, min=1 secs=10 mili=55
    then
    display the chosen fields(once query has been ran)

    in english terms: if the world record(events tbl) has been beaten by the result(results tbl) show any beaten records on screen.

    but there are 3 different fields that need to be taken into account.. and the the results need to be shown for each event(heat) and its beyond my ability, any help appreciated
    Last edited by forcer; 03-12-04 at 05:53.

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Try this out - I renamed the columns in the query for my own simplicity:

    Code:
    select r.min &  ':' & r.sec & ':' & r.milli
    from event e, results r
    where e.heat = r.heat
    and e.min &  ':' & e.sec & ':' & e.milli >  r.min &  ':' & r.sec & ':' & r.milli
    This will give you any records from the results table in which the results are less than the events (or event is greater than result). If an event heat is faster than the results heat, it will not be shown, per your requirements.

    if the world record(events tbl) has been beaten by the result(results tbl) show any beaten records on screen.

  3. #3
    Join Date
    Mar 2004
    Posts
    5
    i ended up with this but i think its still wrong.

    Code:
    SELECT Results.Min &  ':' & Results.Sec & ':' & Results.Milli
    FROM Events e, Results r
    WHERE Events.Heat = Results.Heat
    AND Events.Min &  ':' & Events.Sec & ':' & Events.Milli >  Results.Min &  ':' & Results.Sec & ':' & Results.Milli
    FROM Events e, Results r <this line i dindt understand much so i dont think i put that right.

    apart from that i ran the query and i just get alot of boxes popping up where i'm supposed to enter data then once thats done it returns no results.

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by forcer
    i ended up with this but i think its still wrong.

    Code:
    SELECT Results.Min &  ':' & Results.Sec & ':' & Results.Milli
    FROM Events e, Results r
    WHERE Events.Heat = Results.Heat
    AND Events.Min &  ':' & Events.Sec & ':' & Events.Milli >  Results.Min &  ':' & Results.Sec & ':' & Results.Milli
    FROM Events e, Results r <this line i dindt understand much so i dont think i put that right.

    apart from that i ran the query and i just get alot of boxes popping up where i'm supposed to enter data then once thats done it returns no results.

    Ok - I assume you dont know SQL, so I should have explained this better. If you are going to put an alias in the FROM statement (e for events and r for results), in the SELECT statement, use those aliases instead of the actual table name per my example above.

    The bottom line with the > is saying "only give me back records where MIS:MS from the results table is less than the MIS:MS from the Events table. Again, since you have an alias, use E and R respectively instead of the actual table name. Copy my query exactly, but just simply change the column names, dont change the aliases-

    If you start a new query, go to SQL view, and paste this in it will work. i tested it this morning.

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    when i do that i get loads of popup boxes saying enter paremeter values or something. :s

  6. #6
    Join Date
    Jan 2004
    Posts
    492
    Originally posted by forcer
    when i do that i get loads of popup boxes saying enter paremeter values or something. :s
    PM me and I'll send you my db sample later in the day so you can take a look at it.

Posting Permissions

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