Query

[Andyjones]

Hi I need help to create a query to calculate Pos played won lost for against +- and points like a league table for the following database. the database in link 1 is a re-design of link 2 database if you click on sunday averages query and design you will see what im trying to acheive in link 1.

So could anyone help with this part of the query, of if the anyone else can come up with another design please feel free to change it.

Link1:http://www.box.com/s/bf0shjkm2gdokpu4e42v


Link2: http://www.box.com/s/6uyexcp3bj0p5glvn678

[June7]

The Langley download took a looong time. Did you run Compact & Repair before uploading? Unfortunately, the database won't open for me, get errors, it might have corrupted in either the upload or my download.

I tried to open the Averages query and it errors with a circular reference. I also don't understand the calculations. In one you add Won + Lost then in another you take that sum and subtract Lost. Doesn't that just leave the Won?

[Andyjones]

Write here goes, one last attempt at trying to explain what Im trying to do, I have a database that works but want to re-design it to do the the following

1. store and record match data, see below
Players
Team Played
Date of Home Home/away Game

Played at home - Yes No
our score
opp score
Won H - yes/no
Lost H - yes/no
Points - 2 points 4 each player if they win at home

Played away - yes No field
Our score - if player reaches 21 they have won there game
Opp score
Won Away Yes/no
Lost Away Yes/ No
Points Away 3 points 4 each player of they win away.

However in my 1st attempt at re-designing it with some help, how i want the data entry form to look is perfect but i do not now how to get the query i have in the 1st database t work in the 2nd, because the query in the 1st database relys on two sets of data to add up.

ideay i would like two data entry forms, for the home and away matches, put players that make up my sunday team is a total of 8 but the players names can very from match to match depending on who is avalible or who is performing. like many to many.

If anyone could help with either a design that they no will work or a query that will work then please feel free to have a go.

Link 1 and link to i have had to use box.com to upload even though i have cut the info down as much as i can.

so could someone please help resolve this issue.

http://www.box.com/s/zl0afvze86rzg2infk5h - What im trying to do re-design
http://www.box.com/s/4rmrkrqbz9y8by6ihlao- What old DB looked like