If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > Count Values In a Field

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Wales
Posts: 343
Use a crosstab - it will do it all under eachof the placings
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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,
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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;
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Quote:
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.
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Apr 2007
Posts: 5
thanks heaps for all your help guys, it works now!

Matt
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On