Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    filtering one of two identical column values (2000)

    Is there a way to
    <UL><LI>pull the same column value twice into a report
    <LI>Set criteria for one of them
    <LI>Do a count on both of them, one being essentially filtered?[/list]I have a training database and I would like to post both how many registered and how many actually attended a class. Right now the data is stored in column value lngAttendance. When lngAttendance=2, the student actually attended the class. The other values are 1 and 3.

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: filtering one of two identical column values (2000)

    If all you are after is the total, dont do it in the query. Put a text box on the footer of the report with a formula for its source like

    =DCount("lngAttendance","tblTrainees","lngAttendan ce=2")
    David Grugeon
    Brisbane Australia

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering one of two identical column values (2000)

    Cool, I didn't know of the DCount function and seems to have the functionality to do what I want. The problem is that I'm having trouble with setting the domain in the function. When I set it to the equivalent of "tblTrainers" (in my db, it's actually a view, trnadmin.vwInstructors), it pulls ALL of the records in the table with lngAttendance=2, rather than just the ones I'm dealing with in the report. How do I restrict this? Here's my current formula:
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2'")

    How do I restrict it so that it only pulls data for the current class?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: filtering one of two identical column values (2000)

    Change David's DCount command to have extra criteria.
    Eg.
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND Class = nn")
    where Class needs to be changed to your Class fieldname and nn to the Class required. You may need to enclose Class number in single quotes if the Class is a text field.
    HTH
    Pat

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering one of two identical column values (2000)

    Well, you're hitting on my problem! I'm not sure what to set for that additional criteria. I want it to pull out the attendance for whatever class I happen to be in, so I can't set my lngClassID field to be the ID for any one class. Neither of these works:
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND lngClassID=[lngClassID]")
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND [lngClassID]=[lngClassID]")

    Both pull all of the data in the entire dataset, not the data for that particular detail set. I don't know how to explicitly call the current [lngClassID] value. In the normal Count function, Count only counts records in the current detail set, so it doesn't need to be explicitly called. Right now my results look something like this:
    Registered.....Attended
    17.....2097
    21.....2097
    11.....2097

    Argh!!

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: filtering one of two identical column values (2000)

    Hi Angela
    If the lngClassID a field on your form you will have to change the DCount to:

    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND lngClassID=forms![YourFormName]![lngClassID]")

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  7. #7
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering one of two identical column values (2000)

    Would it make a difference if it's a report and not a form?

    Here's what I entered and it gives me a "#Error" message:
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND lngClassID=reports![rptInstructorInfo]![lngClassID]")

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: filtering one of two identical column values (2000)

    AFAIK if you are referencing this field from the ReportFooter then you will need the lngClassID also defined in the ReportFooter. As it's not defined in the ReportFooter this is why you are getting the #Error.

    Is the report for a particular Class?

    You may have to change your Dcount to:
    =DCount("[lngAttendance]","trnadmin.vwInstructors","[lngAttendance]='2' AND lngClassID=" & reports![rptInstructorInfo]![lngClassID])
    as long as you define the lngClassID in the Footer.

    You could setup a global to the report with the ClassID and use this in the DCOunt.

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  9. #9
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering one of two identical column values (2000)

    THAT WORKED!! Thanks SO much. Why does the formula need to be broken apart like that? Where can I find out more about these formulas and what their syntax should be? And, most importantly, what's AFAIK?

    Thank you! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: filtering one of two identical column values (2000)

    Pat will no doubt answer your Access questions, but I can answer the most important one <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    AFAIK is short for As Far As I Know. People often use this kind of shorthand on the Internet; other examples are IMHO = In My Humble Opinion, HTH = Hope This Helps, FWIW = For What It's Worth.

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: filtering one of two identical column values (2000)

    IMAIDK why the formula needs to be broken apart like that. I am sure that Hans will know the answer to that one.

    The syntax is the WHERE clause of an SQL without the word WHERE.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    IMAIDK means I Must Admit I Don't Know. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  12. #12
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Appleton, Wisconsin, USA
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: filtering one of two identical column values (2000)

    Thank you! When looking for help on this one I didn't know if I should look in Access Help, in VB Help, in Excel Help - I didn't think of SQL, although that makes sense now. OTOH, how hard would it be to put some coding basics in the Access Help screens? I don't even know why I look there. Here or MSDN is where I seem to find more info.

    And thanks for the new acronymns to add to my collection! This database will be non-existent in 20 years, but we'll probably "speak" exclusively in acronymns and other shorthand, so once again, Woody's Lounge saves the day! <img src=/S/sailing.gif border=0 alt=sailing width=25 height=25>

Posting Permissions

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