Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2012
    Posts
    126

    Unanswered: Counting the number of occurances

    I don't know why but im drawing a blank on how to do this.

    I have a table with employee name and hire date. I have another table with employee name and event date. I need to count how many events each employee attended since his/her hire date.

    I was thinking something like this would work.

    IIF(hiredate<=event date,1,0) and then just use the count function but it doesn't seem to be working.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Syrch View Post
    I have a table with employee name and hire date. I have another table with employee name and event date. I need to count how many events each employee attended since his/her hire date.
    I must be missing something; if you have a Table with Employee Names and Dates of Events attended, what does the DOH have to do with it? Could you not simply add up the number of Records, for a given employee, in the Name/Date Table? Or are people routinely hired/fired/rehired? And if so, does it really matter?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    I must be missing something; if you have a Table with Employee Names and Dates of Events attended, what does the DOH have to do with it? Could you not simply add up the number of Records, for a given employee, in the Name/Date Table? Or are people routinely hired/fired/rehired? And if so, does it really matter?

    Linq ;0)>
    I should clearify a bit. The business we are in is very reliant on our suppliers. We have to work very hard to keep them happy and we need employee participation at the events. We are looking to reward those with good attendance. The employee table is employee, hiredate. The event table is EventID, EventName,DateofEvent. I have a third table that is Employee/EventID/signed up/showed up. We track whether or not they show when they sign up but we'd also like to see how many events (of those that occured) that they attended since being hired.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    If you're doing this on a Form based on the EmployeeTable, where the Employee's HireDate is Bound to a Control,

    Code:
    AvailableEvents = DCount("*", "EventTable", "[DateofEvent] >= #" & Me.HireDate & "#")


    will get you the number of events that were available since the HireDate. Is this what you're looking for?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    If you're doing this on a Form based on the EmployeeTable, where the Employee's HireDate is Bound to a Control,

    Code:
    AvailableEvents = DCount("*", "EventTable", "[DateofEvent] >= #" & Me.HireDate & "#")


    will get you the number of events that were available since the HireDate. Is this what you're looking for?

    Linq ;0)>
    You sir may have made my night! I can easily make this into a form but was doing it in a query. I'd assume the dcount function would work in a query as well? I won't be able to test this out until tomorrow.

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Yes, the 'domain' parameter can be either a Table or a Query.

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  7. #7
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    Yes, the 'domain' parameter can be either a Table or a Query.

    Linq ;0)>
    Thank you for showing me this function. I see myself using it often.

    If I could i'd buy you a drink

  8. #8
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  9. #9
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    Glad we could help!

    Linq ;0)>
    Hey Linq,

    I was wondering if this can be thrown into an IIF Statment? Something Like this.

    IIF([Attended] = "TRUE", DCount("*", "EventTable", "[DateofEvent] >= #" & Me.HireDate & "#"), ?)

    I used the ? b/c im not sure what i'd put there.

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    IIF() has a way of producing unexpected results, and I usually avoid it like the plague! Your mileage may vary!

    I would do the total for the number attended, per employee, as a separate calculation. After all, your requirement, as stated, was having to know how many out of those available, an employee attended, was it not?

    So you'd do a DCount() where [Attended] = "TRUE" and the Employee name was the same as in the particular Record being displayed, on the Form.

    BTW, the line

    [Attended] = "TRUE"

    is only correct if [Attended] is a Text Field, i.e. enetered as Text in a Textbox or selected from a Combobox where the Field actually says 'True'.

    If this is a Checkbox, the syntax would be

    [Attended] = TRUE

    or, better yet

    [Attended] = -1

    Don't know why, but in code the Access Gnomes prefer the latter, with the former causing problems, at times!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by Missinglinq View Post
    IIF() has a way of producing unexpected results, and I usually avoid it like the plague! Your mileage may vary!

    I would do the total for the number attended, per employee, as a separate calculation. After all, your requirement, as stated, was having to know how many out of those available, an employee attended, was it not?

    So you'd do a DCount() where [Attended] = "TRUE" and the Employee name was the same as in the particular Record being displayed, on the Form.

    BTW, the line

    [Attended] = "TRUE"

    is only correct if [Attended] is a Text Field, i.e. enetered as Text in a Textbox or selected from a Combobox where the Field actually says 'True'.

    If this is a Checkbox, the syntax would be

    [Attended] = TRUE

    or, better yet

    [Attended] = -1

    Don't know why, but in code the Access Gnomes prefer the latter, with the former causing problems, at times!

    Linq ;0)>
    WOW thank you for the quick response

    Yes that is my original request and its working great! I noticed the flaw though. Three fields are [date] [signed up] [attended]. Counting just the [date] does not take into account the events the user did not attend. I am going to use both though. I want to see our of 72 events he/she signed up for 70 and attended 68. The signed up part is working which was my original request.

    Thank you on the Boolean explanation. I've been using access for years but just to reproduce data from our main server. I'm working on taking down most of what I wrote and rebuilt it to be better and more functional. And you are right I need to use True or False -1/0 since the columns are checkbox, my apologies.

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    instead of using = true or =1 better to use the intrinsic vba constant vbTrue (or vbFalse for that matter
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Aug 2012
    Posts
    126
    Quote Originally Posted by healdem View Post
    instead of using = true or =1 better to use the intrinsic vba constant vbTrue (or vbFalse for that matter
    How would you apply that to a query. Where.vbtrue ? Im assuming you'd need to build an expression in the criteria field?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •