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

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> 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
 
DanielPineault
post Sep 20 2019, 03:07 PM
Post#2


UtterAccess VIP
Posts: 6,978
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
 
MadPiet
post Sep 20 2019, 03:38 PM
Post#3



Posts: 3,361
Joined: 27-February 09



I have a sneaking suspicion that your table is not set up correctly. Could you show us the table structure? (I dealt with this once before, and fixing it can be a huge nightmare.)
Go to the top of the page
 
samersy
post Sep 20 2019, 03:39 PM
Post#4



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#5


UtterAccess VIP
Posts: 6,978
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#6



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
 
DanielPineault
post Sep 20 2019, 05:19 PM
Post#7


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



Well, I'm not sure I follow. One thing is for sure, you can't use TableDef to search through values.

Perhaps something like the following is what you are after?
CODE
Sub a()
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim sSQL                  As String

    Set db = CurrentDb()
    sSQL = "SELECT * FROM students WHERE (grade<50);"
    Set rs = db.OpenRecordset(sSQL)
    With rs
        If .RecordCount <> 0 Then
            Do While Not .EOF
                Debug.Print ![grade]
                .MoveNext
            Loop
        End If
    End With
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
    If Not db Is Nothing Then Set db = Nothing
End Sub

--------------------
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
 
MadPiet
post Sep 20 2019, 06:16 PM
Post#8



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#9



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
 
Jeff B.
post Sep 21 2019, 06:16 PM
Post#10


UtterAccess VIP
Posts: 10,327
Joined: 30-April 10
From: Pacific NorthWet


As others have pointed out, your table structure would benefit from further normalization. If this is 'coming from' a spreadsheet, but simply imported as is, it is unlikely to be well-normalized.

So what, you ask? So, Access is a relational database, and is optimized to work best with well-normalized data.

With the fields you described, what do you do if/when another subject is added? In a spreadsheet, you'd just add another column. That's not a very useful approach when working with Access.

What about a table that lists [Subject]? That way, a new subject just becomes a new/added row, and any/all forms, queries, reports, macros, code just keeps working...

--------------------
Regards

Jeff Boyce
Microsoft Access MVP (2002-2015)

Mention of hardware or software is, in no way, an endorsement thereof. The FTC of the USA made this disclaimer necessary/possible.
Go to the top of the page
 
MadPiet
post Sep 21 2019, 07:29 PM
Post#11



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
 


Custom Search


RSSSearch   Top   Lo-Fi    21st November 2019 - 11:47 AM