Database help

[Andyjones]

Please could some one take a look at this and see if they can get it to work it should be easy to see what i am trying to do and any improvements on this or the other database please feel free to make them links below.

http://www.box.com/s/olsu457f3eqalhvocvl4

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

[June7]

What do you mean by 'get it to work'. The Langley database is extensively developed. What issue(s) need to be addressed?

Why does Database2 have two tables for team names?

[Andyjones]

Database 2 has two tables because im trying to create a home form and away match form for entering the data as thus speeds the process up. in the langley database i have to enter each player scores in idividualy im trying to change that in database 2 by having a form that looks like a score card so its quicker to enter the information.

[June7]

What is specific issue you have encountered? And 'I don't know what to do next' is not specific. You have tables built - next is build forms.

[Andyjones]

I have a relationship set up between the tables, and if you look some data has already been added, test data.

What I am trying to do is get the relationship to work correctly so that the player averages query adds up each players indivudial data to produce a league table like langley b database.

[June7]

As long as both tables have all the same players, can join them on players. Don't need the CopyOfTeamNames. Try:

Sunday Averages
SELECT [Sunday Results].[Team Name], [Sunday Results].[Player Name], Sum(Abs([Played Home]+[Played Away])) AS Played, Sum(Abs([Won Home]+[Won Away])) AS Won, Sum(Abs([Lost Home]+[Lost Away])) AS Lost, Sum(Nz([Score 1],0)+Nz([Score 3],0)) AS [For], Sum(Nz([Score 2],0)+Nz([Score 4],0)) AS Against, Sum(Nz([Score 1],0)+Nz([Score 3],0)-(Nz([Score 2],0)+Nz([Score 4],0))) AS [+/_], Sum(Nz([points Away],0)+Nz([Points Home],0)) AS Points
FROM [Sunday Away] INNER JOIN ([Team Names] INNER JOIN [Sunday Results] ON [Team Names].[Team Name] = [Sunday Results].[Team Name]) ON [Sunday Away].[Player Name] = [Sunday Results].[Player Name]
GROUP BY [Sunday Results].[Team Name], [Sunday Results].[Player Name]
ORDER BY Sum(Nz([Score 1],0)+Nz([Score 3],0)-(Nz([Score 2],0)+Nz([Score 4],0))) DESC , Sum(Nz([points Away],0)+Nz([Points Home],0)) DESC;

Then the ranking query appears to work.

[Andyjones]

pls demonstrate an example of what you have typed up. Also i want to create a form form that looks like the match card layout in langley b averages but i want a single form with a continous form feel. ie each 1 record with multipul entries per match,then when you click on add new record ur shown blank fields ready 4 u to enter the next batch of data.


Example

Team Name Date of Game

Player Name Played home Won Home Lost Home Points
tom
dick
harry
sue

(1 record with continous entries)

if you could do example of this i can adapt it 4 my own needs.

thanks 4 ur help so far

[June7]

Just copy/paste the SQL statement into the SQL View window of the query designer. Access Help has guidelines on building aggregate queries (GROUP BY).

Then why don't you just import the form from Langley and edit it as needed - change the RecordSource? I am not going to build your project.

[Andyjones]

How would i get this to work if players in both tables were different as not all the same players would play each week the team would change depending on which players were avaliable

[June7]

The members playing on a team would not be the same each week? For this particular query, doesn't seem to matter. And on second look the Team Names table doesn't need to be in the query.

The way tables set up can't be more than one record per team in the Team Names table. Is this intended?