calculate multi yes/no fields in percentage

[eshai]

hi:
i have a table for Student attendance. the table have "id" "month" "lastname" "firstname" Then i have the day fields "a"b"c"d"e" etc'
what i need is to SUM all "yes" days fields for a month and show it as percentage for Each student. so in the student form I will put a text box named "present" for example the result "present 80%" for the last month

Sorry for syntax errors

 

[Deleted member 147267]

You are off to a bad start, I think. Objects should not use reserved words (month) or contain special characters (save for perhaps underscore) or spaces, or start with numbers. You are also suffering from Excelitis in that you've designed a table (tall) like a spreadsheet (wide). That might be the main reason you are here, because it would be far easier to sum over records than it is over fields.

You might want to research normalization and naming conventions. Here's a start if you agree
Normalization Parts I, II, III, IV, and V

http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html
and/or
http://holowczak.com/database-normalization/

Naming conventions
- http://access.mvps.org/access/general/gen0012.htm
- https://www.access-programmers.co.uk/forums/showthread.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

 

[eshai]

wow
Uncle Gizmo, Micron
sorry to be rude but i know exactly what i'm doing and my table structure are perfect
the "month" field is "date" (1.12.2019) the "a,b,c,d,e" is a day of a weak
did you ask yourself in which language is my db? let me tell you is not in english and its using a different calendar
my Q' was what do i use "Dsum" "count" function or vba or whatever

 

[namliam]

Eshai

You would do well following either of their advice, redesigning your table / form should be a serious option.

2 other options you can follow
1) make union queries to rebuild your data so its more managable for a database (and less like a spreadsheet) then have the database do its work
2) you can add the ticked values using iif
IIF(a;1;0) + iif(b;1;0) +
etc and devide by number of days per month

* replace ; by , if needed.

Again please do consider the advice of long time forum vets.

 

[Deleted member 147267]

wow
Uncle Gizmo, Micron
sorry to be rude but i know exactly what i'm doing and my table structure are perfect

Then I have nothing to add and will unsubscribe from your thread as I certainly don't want to risk agitating you further. Odd that you have given 3 people the same impression.

 

[eshai]

Eshai

You would do well following either of their advice, redesigning your table / form should be a serious option.

namlian

my design is good i know what i'm doing it's not my first db
my Q' is there any method like in Excel to warp cells [A1 to H1] and get the result
i know i can use "count(iif statement)"

 

[isladogs]

Eshai
I have written many commercial databases for schools and colleges over a number of years including one which includes recording student session and lesson attendance.

Having said that I'll now add the same advice. Your table structure would be fine in Excel but not in Access as it isn't normalised. You can either carry on as you are and battle against Access or use it effectively with fully normalised data.
If you do that, you can use a crosstab query to display your data in a spreadsheet type format and still be able to calculate % present, % absent etc easily.

 

[eshai]

my code

countattendant: count(iif([attendance]![yes_no]=yes,1,null))

now can warp all my yes/no fields for a record instead of the"[attendance]" in the code

 

[isladogs]

Yes you can do that though I'd suggest using 0 instead of null.
You can then work out the number of possible days attendance in each month (which will vary from month to month) and from that get the % values.
But as stated you are trying to use Access as though its Excel.
You may well get it to work but each step will be unnecessarily complex.

Good luck with your endeavours.

 

[Mark_]

my code

countattendant: count(iif([attendance]![yes_no]=yes,1,null))

now can warp all my yes/no fields for a record instead of the"[attendance]" in the code

ACCESS does not have native functions to sum or count values in different fields of a record.

ACCESS does have native functions to sum or count values in the same field in different records.

You can write your own function to return the totals.

 

[Galaxiom]

I knew just by reading the question title that the problem would be caused by inappropriate table structure. Wanting to Sum across multiple fields is a reliable indicator of normalization issues.

You are building a spreadsheet instead of a database. Asking this question put it beyond any doubt.

my Q' is there any method like in Excel to warp cells [A1 to H1] and get the result

While you persist with this structure you will continue to require workarounds that will also have adverse effects on database performance.

 

[eshai]

ok thanks you all

i solve it

in the quiry i put this code

countattendant: Sum([A1]+[B2]+[C3]+[D4]+[E5]+[A2]+[B2]+[C2]+[D2]+[E2]+[A3]+[B3]+[C3]+[D3]+[E3]
+[A4]+[B4]+[C4]+[D4]+[E4])

that code give me a new colman that are showing the number of "true" for the id
the reason of those field name it's because [A1-E1] is one week etc'

next come this code

Expr1: FormatPercent([countattendant]/20)

this code make a new column that convert the numbers to percentage divided by 20 days that are in 4 weeks

Finally, I did not reinvent the wheel. I just tweak it for my own needs

 

[isladogs]

OK. Clearly you're not going to follow the advice of 6 highly experienced developers.
Note that all of us made the same points. Nobody supported your approach.

You solved it in the way I described in post #10.
However, not all months will have 4 full calendar weeks or exactly 20 calendar days of attendance.

No doubt we'll see you again for your next problem...and you'll get the same advice.
As you are determined to keep this structure, why not do this in Excel instead.

 

[eshai]

No doubt we'll see you again for your next problem...and you'll get the same advice.
As you are determined to keep this structure, why not do this in Excel instead.

I really appreciate you friends
It is a huge database used by 10 end users
Believe me, it's all built according to the rules you wrote here
Here, in this case, it is a database that only deals with the presence of students on only one computer
And the tables are linked. I couldn't build it from scratch because it is already used in another system built 20 years ago and running

for the date it as no mining because the 4 weeks is between 2 dates

 

[namliam]

Making code readable makes it understandable, much like making a proper database instead of building excel sheets.

[A1]+[B2]+[C3]+[D4]+[E5]+
[A2]+[B2]+[C2]+[D2]+[E2]+
[A3]+[B3]+[C3]+[D3]+[E3]+
[A4]+[B4]+[C4]+[D4]+[E4]

It would seem to me that your [perfect] code is flawed !

Nice four weeks between 2 dates how do we solve 25 nov thru 22 dec ?

Or even 2 dec thru 28 dec, I doubt perfect attandence

 

[namliam]

It is a huge database used by 10 end users

Huge, define Huge ? all 10 tables and 100 megs?

Believe me, it's all built according to the rules you wrote here

Erm, your requirements and your solution shows otherwize :banghead:

And the tables are linked. I couldn't build it from scratch because it is already used in another system built 20 years ago and running

Having a system for 20 years is no reason to propegate a bad situation, if anything its reason to invest into some proper design. Though legacy problems can be a pain to deal with.

 

[The_Doc_Man]

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

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."

 

[eshai]

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

 

[isladogs]

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.

 

[Galaxiom]

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)

Lastname and FirstName do not belong in that table.