0

# Some help here

These days on vacation I'm making a small database.

I have a problem in the Games table. Ihave each game with the goals and points . How do I to sum the gols and points in the Teams Table.

This don't work.

``````sum(select Partidos where 'Equipo Local_>' = 'Ptos L') +
sum(select Partidos where 'Equipo Visitante_>' = 'Ptos V')
``````

## 22replies

• Kruna
• 11 days ago
• Reported - view

Hola Rafael, can you attach your db here to see?

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

hi kruna.

I have a Formula on trigger after update for GF and GC and work perfect but needfor each teams the points, on Equipos table

• Kruna
• 11 days ago
• Reported - view

I ma not sure I understood right, but

sum((select Partidos).'Ptos L') + sum((select Partidos).'Ptos V')

gives me results in a formula field.

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

Hi Kruna

This Formula SUM the same for all Teams need, need for each team.

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

Example this game GF 3 gosls  and GC 1 goal  win the Young Boy and gain 3 points need toplace the 3 point on Teams table on Young Boy Team but if win Arsena need to place the 3 point to Arsensl on Teams table

• Kruna
• 11 days ago
• Reported - view

you mean on table 'Equipos' in field Total Puntos?

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

yes

• Fred
• 11 days ago
• Reported - view
said:
This Formula SUM the same for all Teams need, need for each team.

You are correct. Why don't you use the relationship link to Partidos? That way you only get related records?

I also don't think you need a new table for each round. The data is the same the only difference is what round you are in. It would make more sense to just have a choice field in Partidos that has group play, rounda eliminatoria, 8tvos, 4rtos, Semis, Final.

Have fun.

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

Hi Fred.

i have the relationship between Teams (Equipos)  to Games (Partidos)

• Fred
• 11 days ago
• Reported - view

So use that relationship to do your sum().

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

yes Fred, I'm doing something wrong

• Fred
• 11 days ago
• Reported - view

• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

sum((select Partidos).'Ptos L') + sum((select Partidos).'Ptos V')

but this plase the same result in all game, in the Equipos need the points for each teams.

_______________

In field GF and GC I place this code and works ok.

Team 1. 2 goals Team 2 0 goals / Team 1 have 3 points

Team 1. 2 goals Team 2 2  goals / Each Team  have 1 points

Team 1. 0 goals Team 2 2 goals / Team 2 have 3 points

``````if GF = null or GC = null then
'Ptos L' := null;
'Ptos V' := null
else
if GF > GC then
'Ptos L' := 3;
'Ptos V' := 0
else
if GF < GC then
'Ptos L' := 0;
'Ptos V' := 3
else
'Ptos L' := 1;
'Ptos V' := 1
end
end
end
``````
• Fred
• 11 days ago
• Reported - view
• Rafael Sanchis
• Rafael_Sanchis
• 11 days ago
• Reported - view

I'm not getting the idea, I have the gross at a million.  sorry

• Fred
• 11 days ago
• Reported - view

You may want to think about renaming <_Partidos1 & 2. Maybe <_PartidosL and <_PartidosV. If you do that then you can change the code to:

``````sum(<_PartidosL.'Ptos L') + sum(<_PartidosV.'Ptos V')
``````
• Rafael Sanchis
• Rafael_Sanchis
• 10 days ago
• Reported - view

Hi Fred I explained to bad very sorry. Is more complicate.

To update the teams' table with the points earned from the matches, I need  use a script that loops through all the matches, evaluates the result, and assigns the appropriate points to each team.

I have a Teams table and a Matches table, where:

The Matches table includes the following fields: EquipoLocal_> (link to the Teams table), EquipoVisitante_> (link to the Teams table), GolesF, GolesC ,PtosL (3 for a win, 1 for a draw, 0 for a loss) PtosV (3 for a win, 1 for a draw, 0 for a loss).

The Teams table includes a field: TotalPuntos, TotalGF, TotalGC (fields that will sum the points from each match).

• Fred
• 10 days ago
• Reported - view
said:
To update the teams' table with the points earned from the matches, I need  use a script that loops through all the matches, evaluates the result, and assigns the appropriate points to each team.

I thought you had this already in the trigger after update of GF and GC.

Doesn't this:

``````sum(<_PartidosL.'Ptos L') + sum(<_PartidosV.'Ptos V')
``````

work for TotalPuntos?

• Rafael Sanchis
• Rafael_Sanchis
• 10 days ago
• Reported - view

Ok Fred, on Table Patidos I place the Goals for each macht, then I need to sum all the poins and goals  the on table Equipos for each table. Now place it manually.

• Fred
• 10 days ago
• Reported - view

I'm confused on what you are asking.

1) You have to manually enter the goals scored by each team.

2) that will run the trigger after update to update puntos.

3) then you need to modify the code in Equipos to sum GF, GC and Puntos.

So the code for TotalPuntos:

``````sum(<_PartidosL.'Ptos L') + sum(<_PartidosV.'Ptos V')
``````

should work. Does it not?

• Rafael Sanchis
• Rafael_Sanchis
• 10 days ago
• Reported - view

You have to manually enter the goals scored by each team Yes in table Partidos when the game finish I place de score GF and GC and depends on that result place 3 if win Local 1 and 0 to Visitor if is Tie 1 for both teams and if the visitor win 3 point to visitor and 0 to local.

That will run the trigger after update to update puntos. Yes and work perfect.

Then you need to modify the code in Equipos to sum GF, GC and Puntos. Yes one team can play 8  games minimun and 17 maximun. For each teams need summ all GG, GC and points.

exmple is one team play 8 games if win 4 tie 2 and lost 2 the finsl points 14 points points.

• Fred
• 10 days ago
• Reported - view

here is what i have done to your DB:

1) the links to from Equipos to Partidos have been renamed to reflect if they are local or visitor.

2) the totalGF, totalGC, and totalPuntos have been updated

3) I've also created a new subtable to Partidos to show another way of tracking scores. The Partidos record records basic info about the event itself. Then the subtable, Concursante (bad Google translate), tracks the team and their score.