UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
> Count Fields, Access 2010    
 
   
samersy
post Sep 20 2019, 03:00 PM
Post#1



Posts: 11
Joined: 20-September 19



hello
How to count fields in the same row using criteria
thanks advance
Go to the top of the page
 
 
Start new topic
Replies
DanielPineault
post Sep 20 2019, 03:07 PM
Post#2


UtterAccess VIP
Posts: 6,974
Joined: 30-June 11



welcome2UA.gif

Could you explain your needs a little more please. You want to count the number of fields in a record, where? In a query, in VBA, on a form? Please give us some context, your existing code, ... so we can assist you.

If you are in VBA and have a recordset then you can simply do something along the lines of
CODE
rs.Fields.Count

assuming rs is your recordset object.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
samersy
post Sep 20 2019, 03:39 PM
Post#3



Posts: 11
Joined: 20-September 19



than you for your answer
in vba i used this code : me.grade= currentdb.tabledefs("students") . fields.count
but this code give me all numbers of fields that is 10 , i want if fields that <50
than you
Go to the top of the page
 
DanielPineault
post Sep 20 2019, 04:07 PM
Post#4


UtterAccess VIP
Posts: 6,974
Joined: 30-June 11



fields are the columns in your table. So if . fields.count gives you 10, that is because you have 10 columns in that table.

Now, I believe you actually want to perform a query and restrict the data based on a criteria. In such a case, you don't need any VBA whatsoever. Simply create a new query, add the table to it, drag the fields you want to display and then in the criteria for the column of interest put <50, then run your query, I believe that is what you are after.

--------------------
Daniel Pineault (2010-2019 Microsoft MVP, UA VIP, EE Distinguished Expert 2018)
Professional Help: https://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: https://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
samersy
post Sep 20 2019, 04:15 PM
Post#5



Posts: 11
Joined: 20-September 19



thank you
i assigned a field in this table to put the number of the fields that are <50 by using a code so i need this code as i wrote before
i used this code me.grade= currentdb.tabledefs("students"). fields . count but i need how to make an criteria for only fields that are <50 , i tried many times but no use
and another thing , i need not all fields in this table becase i have nameofstudent ... so i need only what i put them on criteria
thank you alot
This post has been edited by samersy: Sep 20 2019, 04:42 PM
Go to the top of the page
 
MadPiet
post Sep 20 2019, 06:16 PM
Post#6



Posts: 3,361
Joined: 27-February 09



Could you post your table definition, or something like it?

You have something like [StudentID], and then lots of fields (like one for each course?), and you're trying to get data out of it?

If your database is designed right, this is a really easy question. If it's not, it's really hard. And any time I find a database where an easy question has a hard answer, I wonder about the structure.
Go to the top of the page
 
samersy
post Sep 21 2019, 03:29 PM
Post#7



Posts: 11
Joined: 20-September 19



thank you
I have a table containing studentid, nameofstudent, mathsubject , enlgishlanguge and about 8 teaching subjects and three of them are consederid as theory subjects and the other are practise subjects
so each subject has a mark(grade) so to success the student should
take 50% in theory subjects and 60% in practise subjects so i need ( if student take < 50% in 2 theory subjects and <60% in 2 practis subjects then he doesnt' pass the exam ) and if i write the code by normal it will take me a long code , so i did a new field for number of theory subjects that <50 and another field for practise subjects that <60 % ) so in this point i am trying to get number of these fields but no use
thank you
Go to the top of the page
 
MadPiet
post Sep 21 2019, 07:29 PM
Post#8



Posts: 3,361
Joined: 27-February 09



Sounds like your table needs restructuring. If your table looked like this, you'd be in business:

StudentID
SubjectID
Grade

then you'd have two other tables (at least):

Student
------
StudentID (PRIMARY KEY)
Firstname
LastName


Subjects
---------
SubjectID
SubjectName
GradeLevel

Then getting their averages is trivial.

If you post your database with maybe 10 records (max), I can probably restructure it for you. Then I can explain/show you how a properly normalized database will make your life MUCH easier in the long run. (No lie, getting your head around relational databases was hard for all of us... it takes a while to catch on.) If the data is confidential, you can leave those columns out. Just leave the core data and columns in it.
This post has been edited by MadPiet: Sep 21 2019, 07:38 PM
Go to the top of the page
 

Posts in this topic



Custom Search


RSSSearch   Top   Lo-Fi    20th November 2019 - 07:39 AM