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
> About The Fields, Access 2010    
post Sep 30 2019, 04:19 PM

Posts: 11
Joined: 20-September 19

how to count the fields in each record based on criteria for example : count fields in this record <50 , i tried in this code :
me.grade= currentdb.tabledefs" student". fields.count
but this code gave me all fields in this record , so i want count fields based on criteria
thank you
Go to the top of the page
post Sep 30 2019, 04:20 PM

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

You could use DCount and pass whatever criteria you wish.

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
post Sep 30 2019, 04:26 PM

Posts: 11
Joined: 20-September 19

thak you
but i tried dcount , this code gave me all fields in the table , i want in each record not all records
thank you
Go to the top of the page
Larry Larsen
post Oct 1 2019, 02:56 AM

UA Editor + Utterly Certified
Posts: 24,377
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


I may be very mistaken with my thoughts, but would really like to see the structure of this query/table that has so many fields that you need to evaluate against each other..

iconfused.gif thumbup.gif

"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
post Oct 1 2019, 06:50 AM

Posts: 11
Joined: 20-September 19

i have a table containing (studentname), (math), (phys), (chem) , (history) (language) and etc and a field to count number of fields that are <50 .
so every student have a grade for each subject , i want to coutn (number) of fields that grade<50
for example if student has math=40 , phs=60 , history=30 so i want to count that we have 2 fields <50
thank you
Go to the top of the page
post Oct 1 2019, 07:02 AM

Posts: 1,984
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL

Seems your table set up in Access would be similar to


If you really have


Then you are using data as part of your field names which indicates a design issue.

Good luck with your project!
Go to the top of the page
post Oct 1 2019, 09:36 AM

UA Admin
Posts: 36,029
Joined: 20-June 02
From: Newcastle, WA

Quite often, it seems, someone comes here with a question about writing a really tough query to get data out of a table. The problem turns out to be that the table against which the query is written has an improper design, and the reason for that nasty query is that such table designs make querying seriously difficult. This happens A LOT. This one is not unique.

As Orange999 says, your table, as described has an inappropriate design and that poor design is the reason for this complicated query. Fixing the table design will help make that query much easier (and most other tasks as well).

Invest some time in learning more about working with Relational Databases, like Access. Once you've corrected the table design, you'll find your query MUCH easier to write--along with many other queries you'll need in the future.

It also looks like you are storing a calculated value. "... and a field to count number of fields that are <50"

Again, that's not a good idea and is unnecessary in a properly designed Relational Database built with Access. We do not often need to store calculated values. This doesn't appear to be a case where it is useful to do so. It will be calculated from the underlying values each time you need it in the query. So, just delete that field and use the query when you need that count.

My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    13th November 2019 - 06:01 PM