Create table with COUNT ms-access

2019-03-06 07:36发布

问题:

I have a database and I want to create a table with COUNT function in it. Is it possible ?

I have 3 existing tables:

Member
Feedback
Attendance

In Feedback table, 2 columns

Class_ID, 
Likes 

(Class_ID link with the attendance, as each member attend 1 class eg. class 1,2,3,etc. and Likes is for the number of people like the class).

In Attendance table, 3 columns:

Class_ID
Member_ID
Non_member_name

Now I want to alter Feedback table to add 2 new columns. One to count the number of people attend the class, e.g if there is 4 people attend class 1,there would be 4 rows of Class_ID=1. Two to count the percentage of likes i.e Likes/Number_attending*100%

ALTER TABLE Feedback
ADD COUNT(*) AS Number_Attending
WHERE Class_ID.Feedback=Class_ID.Attendance

I tried to run but there is a syntax error in field definition at the bracket ( before the *. Suggestions/Corrections are welcomed.

回答1:

For example:

 SELECT Class_ID, Count(Member_ID) As MemCount, Count(Non_Member_Name) As NonMemCount
 FROM Attendances
 GROUP BY Class_ID

See alternatively Access 2010 calculated columns