Using Access to tally points

  • Thread starter Thread starter Maccers
  • Start date Start date
M

Maccers

Ok, I am designing a database using access 2007. First of all i am trying to
make a table that tallys all Users points from an "Events" table. I am unsure
how to go about this. First of i have a "Users" table, which has 2 fields,
UserID (Primary Key) and Username, a "Events" table: EventID (Primary Key)
EventName, Date, and an Attendance table: AttendID (Key), Event (lookup from
EventName from Events table), Name (lookup from Users) and Points.

What im trying to do is make a table that tallys all users points from all
events that specific user has attended. eg. UserA attended EventA and
recieved 10 points, and EventB and recieved 10points, what i want to do is
make a table that can add all these points together so it can be shown in a
"Profile" form.
 
If I'm understanding your table structure correctly, this query should give
you the desired results:

SELECT Users.UserID, Users.UserName,
Sum(Attendance.Points) AS PointsSum
FROM (Users INNER JOIN Attendance
ON Users.UserName = Attendance.[Name];


However, the Attendance table should not have a "Name" field that is the
foreign key from the Users table. It should be the UserID value from the
Users table. And, perhaps this is what it is -- your statement "Name (lookup
from Users)" perhaps is referring to a <shudder> Lookup field? If this is
indeed a look up field, then this query should work:

SELECT Users.UserID, Users.UserName,
Sum(Attendance.Points) AS PointsSum
FROM (Users INNER JOIN Attendance
ON Users.UserID = Attendance.[Name];

I note that you're using the word Name as the name of a field in your table.
It and many other words are reserved words in ACCESS and should not be used
for control names, field names, etc. Allen Browne (MVP) has a very
comprehensive list of reserved words at his website:

Problem names and reserved words in Access
http://www.allenbrowne.com/AppIssueBadWord.html


See these Knowledge Base articles for more information about reserved words
and characters that should not be used (these articles are applicable to
ACCESS 2007 as well):

List of reserved words in Access 2002 and Access 2003
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335

List of Microsoft Jet 4.0 reserved words
http://support.microsoft.com/?id=321266

Special characters that you must avoid when you work with Access
databases
http://support.microsoft.com/?id=826763


See this site for code that allows you to validate your names as not being
VBA keywords:

basIsValidIdent - Validate Names to Make Sure They Aren't VBA Keywords
http://www.trigeminal.com/lang/1033/codes.asp?ItemID=18#18
 
Back
Top