Solved Query to Find if a Data Column has a Number in it (1 Viewer)

Bean Machine

Member
Local time
Today, 01:34
Joined
Feb 6, 2020
Messages
98
Hi everyone! I am attempting to make a query that returns the amount of users banned from the system. The only problem is that user data is associated with a username and for every item a user signs out an item on my database a new record sprouts with their associated username and status attached to it. So there are multiple records for one user that says they are banned, mainly because I want staff to absolutely know that the user is banned if they decide to search for that user, and because there are many records per student it was important to have that status attached to each record. Anyway, I am trying to make a query that counts multiple records of users banned status as just one (the query is of course grouped by user). I know something like this would give me a solid result if "[Banned?]" was not numerical and instead in "yes/no" format:

Count(IIf([Banned?]="Yes",1,Null))

What would I need to change to make it so it counts a number as the way to add one to the users banned column? Hopefully I described this well enough, I apologize for the choppy structuring. Any help is appreciated!
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
21,358
Hmm, not sure I follow but could you create a new query with DISTINCT rows and then use it for the Count() query? Just a thought...
 

Bean Machine

Member
Local time
Today, 01:34
Joined
Feb 6, 2020
Messages
98
Hmm, not sure I follow but could you create a new query with DISTINCT rows and then use it for the Count() query? Just a thought...

One user can have multiple bans (as a result of how records are managed, I admit it is sloppy) and it is counted as a number. I basically just want to count every user with a number in the ban field if that makes sense.
 

plog

Banishment Pending
Local time
Today, 00:34
Joined
May 11, 2011
Messages
11,613
This thing screams "poor table structure".

In a well structured system you would simply have a user table with either a Status or Banned field to designate banned users. Then when you want to "absolutely know" if a user is banned, you simply look at that field.

Perhaps you can post a screen shot of your Relationship tool so we can see the structure of this thing and guide you to a proper structure.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:34
Joined
Oct 29, 2018
Messages
21,358
One user can have multiple bans (as a result of how records are managed, I admit it is sloppy) and it is counted as a number. I basically just want to count every user with a number in the ban field if that makes sense.
Sorry, still not clear to me. So, if UserA has two rows in the query, do you want the result to say 1 or 2?
 

Bean Machine

Member
Local time
Today, 01:34
Joined
Feb 6, 2020
Messages
98
Sorry, still not clear to me. So, if UserA has two rows in the query, do you want the result to say 1 or 2?

Found a way to do it, it was counting fields with zero values still even when I had set it to zero so I changed it to Null, also yeah I think I'm going to work on my table structure. I started work on the DB when I didn't have a lot of knowledge of access, this is just a temporary fix as I am working towards making a larger database from scratch with the knowledge I've gathered. Thanks for the help!
 

Bean Machine

Member
Local time
Today, 01:34
Joined
Feb 6, 2020
Messages
98
Here is what I did to garner the results I wanted:

Count(IIf([Banned?]=0,Null,1))
 

Users who are viewing this thread

Top Bottom