Looking at the information required, a single table will not work unless someone is so genius in access, the possible and easy way is to create the listed tables below:
(1) tblteam - Main table
(2) tblscores - lookup
(3) tblmarch available - look up with dates
(4) tblLeague - lookup
Then make sure all your tables are indexed, then your queries will have no problem to give you what you want. The same principle we use when developing an accounting system will surely apply otherwise with only one table it will surely not work
No, not the issue, not the problem, not the solution. There is not a single table, but a temp table is needed for the solution. There is a tblPeriod, tblRound, tblTeam, and tblMatches. They are all from the same league so league table not needed.
In theory tblMatches is not normalized. In that table there is a AwayTeamID, HomeTeamID, AwayScore, and HomeScore. I imagine 9 times out of 10 most people would do it this way. But yes a more normalized would be
tblMatchScores with
-- matchID_FK
-- TeamID_FK
--HomeAway ' home or away
-- Score
Although that is more normalized IMO it is a lot more work. This actually makes data validation harder, requires some code, and makes a more difficult UI. You have to ensure through code that two and only two records created for each match and that one is marked home and the other away. IMO, this is a rare case where using a normalizing query is easier than creating a slightly more normalized design.
Either way, the normalizing Union query works fine returning all the correct results.
The issue (unknown to me) is that if you create a union query using "Union" and not "Union All", you may get correct results. However if you then create a query based on the Union query (such as an aggregate) and do not include all the fields in the original query you can create a condition where the original query requires a Union All, even though the original query did not.
Bottom Line, in this case a Union All is needed to ensure that queries built on the Union query are correct, even though the Union query is correct. My guess is that the execution plan is not as one would expect. They do not necessarily run in order from top down. The Union query does not get executed in entirety and then fields are chosen for the aggregate, and the aggregate runs. The execution plan probably selects only the fields in the aggregate to include in the union.
IMO,the original request cannot be done without a temp table, or if so it is beyond my abilities in SQL which are pretty good. I challenge anyone to do it in Access. I think in SQL server you may have a chance.
The ranking of teams follow these rules in order.
1. Total Points for all mateches (win 3, tie 1, lose 0)
Now the hard part.
2. If teams have the same amount of total points then ordered by Points only in those matches between teams with the same total points. (example Team 3,5,7 have 8 Total points)
3. If teams with the same total points, have the same points for matches played within the tie group, then ranked by point differential in the tie group
4. Then ranked by highest Goals For in all matches
5. Then ranked by lowest Goals against in all matches.
The solution was to create a temp table that saves the results from step 2 and 3 (Points within a tie group, point differential in tie group).
