calculate multi yes/no fields in percentage (1 Viewer)

sorry to be rude but i know exactly what i'm doing and my table structure are perfect

Click to expand...

I am truly glad you figured out a way to solve your immediate problem. You do, however, have a very serious long-term problem. I mean no disrespect, but you are making a very common error and it will continue to hamper you. You see Access and think Excel. This is wrong. Let me try to illustrate by analogy.

When you think of documentation, it is perfectly true that you can turn off borders and do other sorts of "diddling" so that you could write a nice document using Excel and even make it look pretty. But you should be writing documents in Word, not Excel, because they are different programs with different viewpoints. Does that make sense?

Similarly, Access and Excel are two programs written with radically different viewpoints. The difference is based in structural relationships and uniformity of data. Technically, all Excel cells are independent of each other. You can do things to cells as a group, such as forming horizontal or vertical sums. Leaving a cell empty or blank will not screw up a formula. The functions in Excel include ways for you to aggregate bunches of cells in various ways. You can even make a cross-cell reference so that a given bunch of numbers are repeated elsewhere on the same spreadsheet.

With Access, though, the utility program that is the Access user environment (which we call the GUI) expects a more formalized structure to the data you give it. Here is a simple comparison:

In Excel, you can have a string of number-filled columns and right in the middle of that spreadsheet, toss in a blank line or two and then start a new set of data IN THE SAME COLUMNS - but use data that is all text, not numeric. The fact that rows 4-64 are numbers and rows 70-99 are text for the same columns means nothing to Excel - but that cannot EVER be done in Access.

Therefore, you are asking Access to do something it wasn't built to do. I have no doubt that your databases ARE perfect - for Excel. But as you described them, they are nowhere near perfect for Access.

I don't want to upset you or insult you, but I have to tell you that your horizon doesn't reach far enough. The Bard of Avon put it this way: "There are more things in Heaven and Earth, Horatio, than are dreamt of in your philosophy."

Because of all the misunderstanding here (my problem with the wording) I feel I have to explain what I did

My system was not responsible for student attendance This was done by another system. at one point they asked me to put it in my system
My approach was to create three tables

1"Studentsattendance" with fields as "ID" (Automatic Number) "Attendance Code" (Number) "Class" "Student ID" "Last Name" "First Name" "a1"-"e1" "a2"-"e2" "a3"-e3" "a4"-"e4"(all yes/no)(These letters are displayed in my language which is how the days and weeks show up)
2."AttendanceTemp" same as "Studentsattendance"
3."AttendanceList" with fields as "ID" (Automatic Number) "date"(date)

then i created a form with subforms one is the "AttendanceList" there you insert the "date"(1.12.2019) And it gets auto "id"
second is "List of classrooms" Third is the "AttendanceTemp"
now there are buttons on the form one "load students by classrooms and date to the "AttendanceTemp"(add query) than you fill up the yes/no fields
second button launch "add query" to insert records to "Studentsattendance" and "delete query" for the "AttendanceTemp"
then i build all my queries with the relationship and reports

now for my Q'
i made the query for the percentage that field is sowing in the student card (form) How many percent he was present in the last month

Thanks to all of you for the comments and comments

best regards: Eshai

It would have helped if you had stated that the attendance data was imported from another system in a non normalised format. However, there is absolutely no reason why it needed to be saved in your system in the same format.
When you imported to the temp table, you should have then converted it to normalised structure before saving the data in the final table.

As I stated previously, you would then use a crosstab query to display in a spreadsheet format and calculate % attendance data. That is exactly how I do this in my own student attendance feature in my schools databases using either imported data or data generated within the application.

eshai said:

My approach was to create three tables

1"Studentsattendance" with fields as "ID" (Automatic Number) "Attendance Code" (Number) "Class" "Student ID" "Last Name" "First Name" "a1"-"e1" "a2"-"e2" "a3"-e3" "a4"-"e4"(all yes/no)(These letters are displayed in my language which is how the days and weeks show up)

Click to expand...

Lastname and FirstName do not belong in that table.