Comparing fields and counting

[NHSNeil]

Hello all,

I have a table (LEAGUETABLE) with three fields:
HOMETEAM
HOMESCORE
AWAYSCORE

I want to create a query which displays all HOMETEAM entries once only, and counts the amount of records for each HOMETEAM where the HOMESCORE is greater than AWAYSCORE.

I know this should be simple, but I've not been using Access for 6 months and seem to have forgotton everything I ever knew!

Thanks in advance for your help
Neil

 

[spoole]

Without making it complicated then you will need 2 queries.

1. Create query based on LEAGUETABLE. Include fields HOMETEAM,HOMESCORE and AWAYSCORE. In the "Criteria" section of the field HOMESCORE type >[AWAYSCORE]

This will give you all results where homescore is greater than awayscore save this query as a select query to use later.

2. Use the "Query Wizard" and select the previously created query. Once finished open the new query in design view and remove field AWAYSCORE. Now hit the "Totals" button on the tool bar (looks like a sideways M) then ensure the value in Total for HOMETEAM is set to "Group By" and the value in HOMESCORE is "Count"

And Voila!!!

 

[Brianwarnock]

I'm sure one query will do it, instead of selecting awayscore select homescore twice, then select Totals query.
Groupby Hometeam, count Homescore, 2nd Homescore select where in totals row and put the criteria in as stated before.

Brian

 

[NHSNeil]

Perfecto!

Thanks to both of you - this is a great help.

Regards
Neil

 

[gemma-the-husky]

is this just counting how many home wins?