Hi all,

I know that the answer is so simple and I know that I will cause myself some serious bodily harm when the answer is made known but at the moment my battered brain cannot twig it.

I have a report that lists three different outcomes W, D, L - what I would like to do is simply(!!!!!!!!!!!!!!) count the amount of W, the amount of D etc and have them appear at the bottom of the report in text boxes.

Please help, it's driven me mad now!

Many thanks for any sympathetic help that you can offer me!

HTFC,

You want to do a DCount of the Table or Query, similar to the following:

=Dcount("[FieldName1]","[QueryorTableName]","[FieldName1]='W' ")

Use the same Field name for the FieldName1 and do not include the spacing after the W, I just wanted you to be able to see the single and double quotes after the W. Create the above for each of the W, D and L.

RAZMaddaz

Wow, many thanks for the speed of your reply, unfortunately - I keep getting #Error in the textbox.

I have used the following : =DCount("[fd_R]","[qry_indgames]","[fd_R]='W'")

It looks like you entered it in correctly.

So you want to Count in the Field "fd_R", in the Query "qry_indgames" the letter "W". Now, is there only one letter in the "fd_R" Field, or is part of a string/characters?

If you look in your Query, is there only either the letter W, D or L showing?

Yes, Only W, L or D

Here is an example. Look at the report and you will see if the DCount is similar to what you are trying to do.

Hi, Yes the DCount is the right one but I noticed you are going from a table, when I do that it shows all the games played that season that have been W,D or L however the qry filters it by player and not all the players took part in the games. so I really need it to run from the qry not the tbl!

Not to worry if it is a pain to work out!

One can do a Dcount by A Query or Table. Are you trying to a Dcount that has a W, D, or L for EACH player?

Or is there a Table or Query that has all the games played that either has a W, D, or L. Sorry, I'm on my first cup of coffee.

RAZMaddaz

The table that the has the info has the score and if that match was Won (W), Drawn or lost, however the ? reads from two players to separate the players and which matches they played and the result - if that makes sense.

I'm on my 15th beer if that also helps!

So your Report is based on a Query and in the Report you are Grouping the Report based on the Players. Now you want to see in the Footer of the Report in three unbound Text Boxes the number of Wins Loses or Draws for the current player you are viewing in the Report, but that data is from a different Table?

erm - yes! If I follow your question properly!

So then it might be a DCount where the Player ID equals a number or name AND the Match equals W, D or L. If so, what is the name of the Table and Fields that contains these? Is there a Player ID Field and if so is it a Text or Number field?

Am I throwing a strike or ball here?

The table that contains the WDL info is called tbl_Matches and the table that contains the player info is called tbl_StartingXI.

The player ID field is on the tbl_StartingXI and it is a Text field.

I think you are throwing the match winning ball!

I assume the Player ID field is in the Table tbl_Matches? And the Player ID Field is in the Query "qry_indgames" that your Report is based on?

erm No, The fd_Name field only appears in the tbl_startingXI. The qry is made up of the two tables linked at the fd_Opposition entry!

HTFC,

Is it possible to make a duplicate of your database, save it as an Access2007 and upload the file? If so, do not include any private data and then zip the file and upload the zipped file?

RAZ

This pic might help!

Problem there - I am working on it for a friend so it's not really mine to upload if you see what I mean!

Okay.

Thanks for the picture, that helps.

First are all the Fields in the Query for the Total set to Group By?

I would try an alternative. Create a subreport that groups on the fd_R field and counts the number of records. Then place the subreport on the end of your report.

yes, they are all group by

Hi ScottGem - erm sorry, I am not that intelligent - could you simplify for a thicko!

Remove the Group By in the qry_indgames, it is not needed.

So this query is a Parameter, based on the fd_Name?

In the Tbl_MatchID, you have this main Field set as an AutoNumber, which is fine, but in the other Table you have a same Field set as a Data Type as Text, therefore if you ever setup a relationship between the two Fields the Query will not work.

What ScottGem is saying is to create a new Query, Group by the Fd_R field, then Count by the same Field or another Field. Then use this Query to create another Form and place this Form in the Main Report Footer.

Use the Report Wizard and create a report. Include the Fd_R and the PK fields in your report. Select to Group By Fd_R and under summary options select to COUNT the PK field and select a Summary report. The resulting report should show just the value of the FD_R field and a count of records. You can modify this report as you want to display what you want.

You can then use the Subreport wizard to place the subreport in the Report Footer so it shows at the end of the report.

OK, I will alter that ASAP, didn't notice to be fair!

Blimey, that subreport sounds complicated.