SQL Copy data from table to another and count value

Question

I am stuck at the moment. I am creating a sports database in MS Access for our sports club and have the following question...

So what I have: Tbl_Player (Player_ID, Firstname, Lastname, position, Min_Played, Goal, Assist, Yellow, Red)

Tbl_Player_Match (FK Tbl_Player_Player_ID, Min_Played, Goal, Assist, Yellow, Red).

What I need and strugling on is the following. When I add a match and data is being saved in tbl_Player_Match, I would need to have this saved in the "Summary" table which is Tbl_Player.

I cannot find the right syntax for this, should I create another temp table with the stats? Should I simply add an SQL code here?

Thanks a lot for your help.

Bob

Answer 1

I'd strongly suggest just using a query to pull the totals at run time:

SELECT  ID
        , Naam
        , Achtenaam
        , SUM(Goals) as Total
FROM    Speler LEFT JOIN SpelerStats ON Speler.ID = SpelerStats.Speler
GROUP BY ID
        , Naam
        , Achtenaam  

If you must add it to the table you'll have to use a domain aggregate function:

UPDATE  Speler
SET     Total = DSUM("Goals","SpelerStats","SpelerStats.Speler = " & Speler.ID)

Answer 2

I would tell you that you shouldn't store a summed value in tbl_Player. There comes only the characteristics of a Player for instance Name, Position and so on. For the Output you create a query that would go something like this:

Select p.Player_ID, p.Firsname, p.Lastname, p.position, SUM(pm.Min_Played) as Sum_Min_Played //, Count(pm.Tbl_Player_Match_ID) as Matches_Played From Tbl_Player p inner join Tbl_Player_Match pm on p.Player_ID = pm.Tbl_Player_Player_ID group by p.Player_ID, p.Firsname, p.Lastname, p.position

The Matches_Played is just an addition, if you have sth. like an ID for the Matches Table. Tell me if it helped you.