Can you use the Countif formula to use in Access?

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
I am creating a new database in Access and was wondering if there was a formula (similar to the Countif in Excel) that I can put in a column to count the previous number of times a persons name has been entered into the database?? Please help!!!
 
Joined
Jul 25, 2004
Messages
5,458
Hi, welcome to the board!

There is no COUNTIF in Access. I'm not sure what you're using this one. You could add a field to your record source by utilizing the IIF function, or look at the DCOUNT function as well. It's hard to say without knowing more about where and how you want this utilized.
 

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
well exactly what Im doing is making an escort database. Every person is allowed 3 escorts before they have to go through the process of getting approval to be on premises without an escort. I want the database to tell me how many previous times someone has been escorted so I know to approve or deny the request.
 
Joined
Jul 25, 2004
Messages
5,458
So are you viewing this in a report? On a form? I'm a little lost. What types of data fields are you using? You're not saying much about what you have. I mean, I can guess, but I'd probably be guessing wrong. ;)
 

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
Its in a table... most of the data fields are text but this one would be a number data field. the table looks like this:

Company -- Name -- previous # of escorts

If Joe was escorted twice before I would want the 'previous # of escorts' to say 2 when i typed his name in??
 
Joined
Jul 25, 2004
Messages
5,458
Assuming that you had a field in your table titled "Escorted" which was a number, and every time they got escorted you put a 1 in that record, you could use this as an event on your control OnClick event control (i.e. Code Builder)...
Code:
Private Sub cbNames_Click()
    Me.TimesEscorted.Caption = DCount("Escorted", "tblData", "Person = '" & Me.cbNames.Value & "'")
End Sub
This assumes your field is titled "Escorted", and it is numeric (as described above), and your table is titled "tblData" and your name field is titled "Person" and the control in which you are looking at (to choose the name) is titled "cbNames", and the returned label is titled "TimesEscorted".

A lot of assumptions, so I'm not sure if that will work for you or not. This is on a form, not a report. Although you can use the Dcount function as shown in many ways.

HTH
 

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
Totally lost me there, Im sorry Im just not the best with Access. Im gonna show you exactly what my table has so far and then maybe you can help me better from there...

Escorting : Table

Company requiring escort- text
name of individual- text
previously escorted- number (this is the column I need help with) I would need a formula here that looks at the previous column (name of individual) and gives me a number of how many times there name has been in the table before.
 
Joined
Jul 25, 2004
Messages
5,458
No, you would need to put a 1 in each record where they were escorted. If you have one record per person per escort, then by putting 1 there you will have a count. Make sense? You don't use a table for just one record, it houses multiple records (I hope I'm not being redundant here). So to get the count of the number of escorts so far, you would just need to count (if :) ) the person equals what was selected on the form, to count that field (previously escorted).

Is there any personal information in this database? If not, can you post it?
 

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
no there isnt personal info... LOL how do I post it??? Sorry this site is new to me, but you are alot of help :)
 
Joined
Jul 25, 2004
Messages
5,458
No worries. :)

If you click the Reply button, you'll have the reply screen, with the Subject and Body, where you type your reply. Underneath that you have a button labeled Manage Attachments, click that. It will pop up a new window (you should allow pop ups for this website if it is blocked), where you can click the Browse button to find your file, click Open, then click Upload. Once it uploads (the name will appear in the window showing as uploaded) you can close that window. Then when you click Post Reply, it will post with the attachment.

Clear as mud? ;)
 
Joined
Jul 25, 2004
Messages
5,458
Hmm, I only see one table. ?? Did you have a form or a report? Or is this all from scratch? I'm assuming you wanted a form for this. You said you want to see how many escorts have been done per person. What other information do you want to see? Do you want to see all escorts done by that person?
 

fonda

Thread Starter
Joined
Dec 7, 2011
Messages
7
Its all from scratch and yes that info is what I need to see. just basically how many times each person has been previously entered.
 
Joined
Jul 25, 2004
Messages
5,458
Ok. So a simple form with a combo box to choose a name, then an indication of how many times they have been an escort previously? We can do that. I'm going to add some things to your database, then re-upload shortly. I'll put a table of names in there. I'm a fan of using tables to define my lists, which lends itself to normalization (a heavy-handed topic, you can do a Bing/Google search for it and read the definition for days, it's lengthy, but it certainly does help with understanding databases more). I'm also going to add a form. You will have the functionality you desire, but certainly it doesn't mean the database is done. There is a lot you can do with this.
 
Joined
Jul 25, 2004
Messages
5,458
Okay, so I took some liberties as I didn't hear back from you and I made those changes. Added a form, table and a query. Run the form, choose a name from the drop down and it will tell you how many times they have had an escort. To do so I had to change the Escort field of your original table to a number. So you would put a 1 in there if they did escort somebody.

Please let me know if that makes sense.
 

Attachments

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Members online

Top