Number of Columns in MS Access 2010?

问题:

I have 52 Yes/No Columns for attendance Weekly .. I want to make query to know the number of columns that put in it a Yes or No... Can anyone help me to solve this problem? Please let me know if you know of any recommend sites or communities for Access queries. Thanks.

回答1:

I think this is a poor design. It breaks first normal form.

I can envision a user table, a meeting table, and a many-to-many JOIN table linking users with the meetings that they attended.

Querying for all the meetings tells you the dates where it's possible to attend.

JOINing the user table with the user_meeting JOIN table tells you which meetings a given user attended.

Now you have two sets: all the possible meetings and the dates that a given user attended. If you do a set difference, you'll see the potential dates that the user did not attend.

回答2:

Here is a rough sketch of how you can work with a list. There are undoubtedly other and better ways, but it is a start.

A three week gap:

SELECT b.UserID, b.WeekNo, (SELECT Top 1 WeekNo 
    FROM (SELECT * FROM table2 WHERE Attended=True) a 
    WHERE a.WeekNo > b.WeekNo) AS [next]
FROM (SELECT * FROM table2 WHERE Attended=True)  AS b
WHERE (((b.UserID)=1) AND (((SELECT Top 1 WeekNo 
    FROM (SELECT * FROM table2 WHERE Attended=True) a 
    WHERE a.WeekNo > b.WeekNo))>[b].[WeekNo]+3))
ORDER BY b.WeekNo;

Number of attendances:

SELECT Table2.UserID, Sum(Abs([Attended])) AS Attend
FROM Table2
GROUP BY Table2.UserID;

The table:

UserID  WeekNo  Attended
1       1       True
1       2       True
1       3       True
1       4       True
1       5       False
1       6       False
1       7       True
1       8       True
1       9       False
1       10      False
1       11      False
1       12      True
1       13      True
1       14      True