I have three dbase tables:
Project: Building a set of tables to record customer information, record selected uses of transportation, record the points earned from using certain types of transportation, record the total points in a database table.

Currently, these are the tables:
Code:

TEAM
teamID
teamName

LIVE DATA:
Live Data:
TEAM
1       Buttmunch
____________________________
ACTIVITY
activityID
activityName
activityValue

LIVE DATA:
ACTIVITY
1       Walking    50
2       Biking      60
3       Trolley     10
 
____________________________
ENTRY (this records when the team applied, there TEAM info, and the ACTIVITY they have chosen)
entryID
date
teamID
activityID

LIVE DATA:
ENTRY
1    2009-05-05    1     2
2    2009-05-05    1     3
3    2009-05-04    1     1

_______________________________
TEAM_TOTALS (this is where I get scatter brained)
tt_ID
teamID
activityID
entryID
totalPoints

LIVE DATA:
TOTAL POINTS
1    1    1    120

I need to know how I can calculate the total and record it in the TOTAL POINTS database table based on teamID.

Currently, with assistance from others, I have this:
Code:

SELECT teamName
     , date
     , activityName
     , activityValue
     , CASE WHEN activityName IS NULL
            THEN 'totals'
            ELSE 'details' 
        END AS sortkey
  FROM (
       SELECT team.teamName
            , entry.date
            , activity.activityName
            , activity.activityValue
         FROM team
       INNER
         JOIN entry
           ON entry.teamID = team.teamID
       INNER
         JOIN activity
           ON activity.ID = entry.activityID
        WHERE team.teamID = 2
       UNION ALL
       SELECT NULL
            , NULL
            , NULL
            , SUM(activity.activityValue) 
         FROM entry
       INNER
         JOIN activity
           ON activity.ID = entry.activityID
        WHERE entry.teamID = 2
       ) AS u
ORDER
    BY sortkey
     , date

I would appreciate anyone helping this newb finish this one up.

Thank you!

make relations via myqsl query with respective tables

Better picture:
We have a web page that allows the user the choose the form of transportation. This information, after the 'Insert' button has been pressed, will post this to an 'entry' table' (this has been figured out). The 'entry' table records the transportation method and points earned from using that kind of transportation. Plus, after the 'Insert' button has been pressed, we want the points of that particular team to total and post to another table called 'teamTotals' (how do I do that?). This table will include the team's ID and the total points earned.

On another web page we will post the top three teams with the highest scores. Getting this info. isn't the hard part. The hard part is how do I calculate and post to two different tables the information requested above?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.