Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Count Values In a Field

    Hey Guys,

    I working through my datbase bit by bit and educating myself.

    I will explain what I am trying to do.

    I have a "Place" field in my table that lists the finishing position in a horse race (either "1" for first, "2" for second, etc. I have not made it a number formatted field because there is an exception to a placing - being either a - non-finish "DNF" or a scratching, "SCR". I still need these two results in the field because the jockey still gets paid for these instances.

    What I am trying to do is Count the number of "finished races" (called "Starts" - every result except "SCR" and "DNF".

    I need this to be the total number of starts.

    Then I want to do another count where is counts the number of firsts (1), Seconds(2) and thirds (3).

    So that the Result of the Query might give

    Starts 5
    Wins 2
    Seconds 1
    Thirds 1

    For Example say I have the following data
    Race 1 1
    Race 2 SCR
    Race 3 5
    Race 4 2
    Race 5 3
    Race 6 3

    I would want the (a) query or report to provide me with these results
    Starts 5
    Wins 1
    Seconds 1
    Thirds 2.

    Is this easy to complete? My eyes have gone square from working out all this database stuff from scratch!

    Thanks for all of your help peeps!!!

    Cheers,

    Matt

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Use a crosstab - it will do it all under eachof the placings

  3. #3
    Join Date
    Apr 2007
    Posts
    5
    I still dont quite get it,

    could someone help either with step by step or an SQL or some sort advice. Something like this should be simple, I know it, but Ive been stuck on this for the last day and a half.

    Im thinking I should just take my idea to a professional Database builder and pay to have it done because I want it to sort and bring up reports based on a lot of variables, but Im having trouble just grouping that data.

    So Frustrating.

    Thanks,

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Can you clarify for me please
    How are the placings entered in to the table that you have?

    Have they been punched in with the horse name and the placing for each race i.e race horse1 placed1 etc

  5. #5
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    Code:
    SELECT horse, Count(*) AS Starts, 
        Sum(IIf([place]="1",1,0)) AS Wins, 
        Sum(IIf([place]="2",1,0)) AS Seconds, 
        Sum(IIf([place]="3",1,0)) AS Thirds
    FROM Table7
    WHERE (((place)<>"SCR" And (place)<>"DNF"))
    GROUP BY horse;

  6. #6
    Join Date
    Mar 2005
    Posts
    20
    For crosstab query, go to database window > queries > new.
    Select crosstab from the dialog box.
    Follow the hints from the wizard. The help topic doesn't elaborate much more than that. You can search "crosstab query".
    The problem you will have is in creating a report. Unless the number of positions a horse can finish doesn't change. Or at least not often. When you create the crosstab query you will end up with fields such as Place1, Place2, Place3.....etc.
    You will need to put fields Place1, Place2, Place3.....on the report in the positions you want. If today you have 5 places finishing then 10 the next then 15 the next and so on, then you'll need to adjust the report each time or create a new report based on the crosstab query each time so it picks up any new fields (Places) or drops ones that no longer show up.
    If you have a number of places that you know you'll never exceed then you can load dummy records into your table for those places so when you create the report for the first time you will have placeholders for all the Place fields. you will not be able to delete those dummy records because the report will complain that you are referencing a field that doesn't exist.
    I have seen vba code to dynamically create the crosstab report but it was complicated and I don't remember where I saw it. I abandoned the idea after looking at the code.

  7. #7
    Join Date
    Jan 2007
    Location
    California, USA
    Posts
    520
    garethfx,
    hayesbcajh has some very good ideas presented, but I would like to clear up a couple of items. First, when creating a CrossTab query, you can use the property "Column Headings" within the query properties to specifiy "Place1", "Place2", "Place3", etc. For as many "PlaceX"s you feel you will ever need or want to display on the report. That saves you from having to recreate the report each time.
    This paragraph
    If you have a number of places that you know you'll never exceed then you can load dummy records into your table for those places so when you create the report for the first time you will have placeholders for all the Place fields. you will not be able to delete those dummy records because the report will complain that you are referencing a field that doesn't exist.
    You don't need the dummy records, the Column Headings property will take care of that for you. These dummy records were to have the CrossTab query think it had all the "PlaceX"s in the table, that way the report would not complain about missing fields. The Column Headings property takes care of that.

    Now, if you need to create a crosstab query in VBA code, you can first create one very close to what you need to create in VBA code then copy that SQL to a VBA module so you can copy it into your VBA code. I have written a utility in Access that will do the conversion from SQL to VBA code that will create the SQL code that was presented to the utility. Go to my website (link is in my signature area), then click on Access Tips in the menu area (top of page), then at the bottom of the Access Tips page will be a link to download the SQL to VBA Utility. It is written in Access 2000 so it will work in any version from 2000 and up.
    Hope this helps,
    PS: To get to the query properties, when the query is open in design view, right click on the area where the tables/queries are shown (top part of the grid) and select Properties.

  8. #8
    Join Date
    Apr 2007
    Posts
    5
    thanks heaps for all your help guys, it works now!

    Matt

Posting Permissions

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