1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Can you use the Countif formula to use in Access?

Discussion in 'Business Applications' started by fonda, Dec 7, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. fonda

    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!!!
     
  2. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  3. fonda

    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.
     
  4. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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. ;)
     
  5. fonda

    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??
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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
     
  7. fonda

    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.
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  9. fonda

    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 :)
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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? ;)
     
  11. fonda

    fonda Thread Starter

    Joined:
    Dec 7, 2011
    Messages:
    7
    Ok i had to put it in a zip file... but let me know if this makes anymore sense then me trying to explain??
     

    Attached Files:

  12. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  13. fonda

    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.
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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.
     

    Attached Files:

  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1030175

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice