Results 1 to 12 of 12
  1. #1
    Join Date
    Oct 2004
    Posts
    16

    Unanswered: Comparing Tables = a value?

    Is it possible to return results of a query into a numeric value?
    I have a DB that I have created to track basketball games. One table is the predictions, the other is the actual result. I would like to query the results, compare it to the predictions and get the number of correct picks, not sure how to create an expression to do this.

    Thanks for reading.

  2. #2
    Join Date
    Feb 2005
    Location
    The Hague, Netherlands
    Posts
    95
    Quote Originally Posted by SevenEleven
    Is it possible to return results of a query into a numeric value?
    I have a DB that I have created to track basketball games. One table is the predictions, the other is the actual result. I would like to query the results, compare it to the predictions and get the number of correct picks, not sure how to create an expression to do this.

    Thanks for reading.
    Dim res() as byte

    For count = 1 to 100
    If 'result' = 'prediction' then
    res(count) = 1
    else
    res(count)=0
    end if
    next count

    Something like that?

  3. #3
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT Count(ta.id)
    FROM tableA ta
    INNER JOIN
    tableB tb ON
    ta.condition = tb.condition
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  4. #4
    Join Date
    Oct 2004
    Posts
    16
    Quote Originally Posted by r123456
    SELECT Count(ta.id)
    FROM tableA ta
    INNER JOIN
    tableB tb ON
    ta.condition = tb.condition
    Thanks for the replies. To be sure I am getting this right, do I add this statement into the criteria section of my querie?
    Select Count(selections)
    FROM Selections 1
    INNER JOIN
    Results 1 ON
    ta.condtion = tb.condition

    Does this look correct?

    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    what r123456 has give you there is a SQL Statement
    replace tableA with the name of your first table, tableB with your 2nd table,

    ta.id is the primary key of your tableA
    and ta.condition = tb.condition is a condition you want to hold youll need to change conditions to your field names something like

    ta.prediction = tb.result

    for example
    after youve made the changes paste it into the SQL view of the query and run from there

    Hope that clears it up a bit
    Dave

  6. #6
    Join Date
    Oct 2004
    Posts
    16
    Sorry to keep coming back to this. but I am getting syntax errors when I try to save the new query.
    Here is how my tables are setup.
    Table Picks
    Fields are
    PLayerID(primary), Pick1, pick2,....Pick63.

    Table Results
    Fields are
    Pick1, Pick2,...Pick 63.

    The SQL I have created based on your teachings is as follows.

    SELECT Count(picks.playerid)
    FROM Picks
    INNER JOIN
    Results ON
    Picks = Results

    I did some reading at w3schools and wasnt able to get much more out of that. And I bought an access2003 book made by microsoft that wasnt much help either.
    If anyone can shed some more light I would appreciate it.

    Thanks for reading

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you need to specifyu in your join which columns in the two tables are linked. If you have already defined your realtionship then the relevant columns will already be linked.

    have a look at the help file for the syntax of the join

    alternatively open the query designer place you two tables and define the link. If you wish you can then save your query OR copy and paster the SQL into the the form.

  8. #8
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    You are experiencing the effects of the table design you are working with. I recommend you alter the design and then your query should be trivial. If this is not an option, then if you post your rules for determining if a result has a matching prediction, I can provide the query for you.
    Last edited by r123456; 02-22-05 at 05:02.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  9. #9
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    i guess what Robert is telling you would be something like this.
    Change your table design to the following
    tblPicks
    PlayerID
    PickID (1..63)
    PickValue

    tblResults
    PickID (1..63)
    PickValue

    Now you can build a query by pasting the following SQL
    SELECT P.PlayerID, Count(P.PickID) AS Hits
    FROM tblPicks AS P INNER JOIN tblResults AS R ON (P.PickValue = R.PickValue) AND (P.PickID = R.PickID);

    which gives you a count of Hits per Player. if you omit P.PlayerID you'll get a total hit list.

  10. #10
    Join Date
    Oct 2004
    Posts
    16
    Altering the tables is no problem. I can change it to whatever is easiest.
    Thanks again to all of you for helping me.
    Bachatero:
    I used your suggestions and altered the Tables as you suggested and copied your sql into access.
    It saves fine, but when I try to open the datasheet view I get an Enter Parameter Prompt: P.PicksID, if I enter anything it then asks for R.PicksID.
    If I add in P.PlayerID to get hits per player I get the error "you tried to execute a query that does not include P.playerid as part of an aggregate function".
    Should I tear down these tables and start fresh all over? I came up with the DB plan myself, and as you can tell by my questions I am very raw. So I am sure there is a more efficient setup.

    Thanks for reading

  11. #11
    Join Date
    Mar 2004
    Location
    Berlin, Germany
    Posts
    107
    i used an alias to make the sql-code more readable .. try the following statement instead

    SELECT tblPicks.PlayerID, Count(tblPicks.PickID) AS Hits
    FROM tblPicks INNER JOIN tblResults ON
    (tblPicks.PickValue = tblResults.PickValue) AND (tblPicks.PickID = tblResults.PickID)

    hope it works!

  12. #12
    Join Date
    Oct 2004
    Posts
    16
    I have this working - sort of

    SELECT Count(P.Pick) AS Hits, P.PlayerID
    FROM Picks AS P INNER JOIN Results AS R ON P.Pickvalue = R.pickvalue
    GROUP BY P.PlayerID
    ORDER BY P.PlayerID DESC;

    It gives me the results of only the first row from the picks table, there will be many rows I would like to see all of them. Also I have to enter the P.Pick(#) each time, is it possible to have it display all 63?

    Thanks for your persistence.

Posting Permissions

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