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

eshai · Dec 4, 2019

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

Uncle Gizmo · Dec 4, 2019

The design of your database is completely wrong...

See my blog:-

https://www.niftyaccess.com/excel-in-access/

for an explanation of how to correct these problems easily.

I also provide a tool to aid with the correction, there's a paid for version, and a free version. If you want the free version, then contact me.

Sent from Newbury UK

Micron · Dec 4, 2019

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 · Dec 4, 2019

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 · Dec 4, 2019

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.

Micron · Dec 4, 2019

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 · Dec 4, 2019

namliam said:
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 · Dec 4, 2019

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 · Dec 4, 2019

my code

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 · Dec 4, 2019

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_ · Dec 4, 2019

eshai said:
my code

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.

Uncle Gizmo · Dec 4, 2019

eshai said:
I know exactly what i'm doing and my table structure are perfect

I don't agree...

Sent from Newbury UK

Uncle Gizmo · Dec 4, 2019

eshai said:
did you ask yourself in which language is my db?

The correct structure is language independent.

Sent from Newbury UK

Galaxiom · Dec 4, 2019

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.

eshai said:
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 · Dec 5, 2019

ok thanks you all

i solve it

in the quiry i put this code

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

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 · Dec 5, 2019

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.

Uncle Gizmo · Dec 5, 2019

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

You had a square wheel, and instead of changing it to a round wheel as advised by many, you got a team of horses to pull the cart.

But when you get to a hill, you are going to need to get behind the cart and push.

Then you will say, I wish I'd taken the advice and got a round wheel....

Sent from Newbury UK

eshai · Dec 5, 2019

isladogs said:
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 · Dec 5, 2019

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

Code:

[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 · Dec 5, 2019

eshai said:
It is a huge database used by 10 end users

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

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

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

eshai said:
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.

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

Registered User.

Nifty Access Guy

AWF VIP

Registered User.

The Mailman - AWF VIP

AWF VIP

Registered User.

MVP / VIP

Registered User.

MVP / VIP

Longboard on the internet

Nifty Access Guy

Nifty Access Guy

Super Moderator

Registered User.

MVP / VIP

Nifty Access Guy

Registered User.

The Mailman - AWF VIP

The Mailman - AWF VIP

Similar threads

Users who are viewing this thread