Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Conditional Count in Access?

Discussion in 'Information Technology' started by f2rox, Jul 28, 2006.

  1. f2rox

    f2rox
    Expand Collapse
    Guest

    How can I set up a conditional count in an Access query to calculate how many
    times a specific value occurs in each record for several fields (eg. I would
    like to know how many times the value "A" occurs for John Smith in the fields
    5/26, 5/30, 6/1, 6/5, and 6/30)?
     
  2. Loading...

    Similar Threads Forum Date
    Inspirational Unconditional love for the Guru Videos Sep 4, 2010
    General Unconditional Love Hard Talk Jun 28, 2008
    Pure and Unconditional Love for God Gurmat Vichaar Feb 18, 2008
    Ashdoc's Movie Review---The Accountant Theatre, Movies & Cinema Oct 21, 2016
    Gurmat Vichaar | Futility Of Counting Paaths | April 12, 2016 Gurmat Vichaar Apr 23, 2016

  3. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Hi,

    I don't really understand the problem, for example:
    Are '5/26', '5/30' etc. Field Names?
    Do they only have a single letter in them or could the A that we're looking
    for be part of a larger string?
    Could 'John Smith' appear in the table more than once?
    ....

    Would it be possible for you to give a sample of the sort of data that is in
    the table?

    Cheers,

    ChrisM.

    "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    > How can I set up a conditional count in an Access query to calculate how
    > many
    > times a specific value occurs in each record for several fields (eg. I
    > would
    > like to know how many times the value "A" occurs for John Smith in the
    > fields
    > 5/26, 5/30, 6/1, 6/5, and 6/30)?
     
  4. f2rox

    f2rox
    Expand Collapse
    Guest

    Yes, they are field names. They contain either "A" "P" "L" or "M/U". And
    John Smith appears only once.
    This is a query to track attendance. The fields are: Name, 5/26, 5/30, 6/1,
    6/5, 6/30, etc.
    The 'Name' field contains names of students ('John Smith')
    The rest of the fields are dates of classes and contain either a "A" "P" "L"
    or "M/U" for each student (for Absent, Present, Late, Make-Up)
    I would like to calculate how often each student was absent.

    "ChrisM" wrote:

    > Hi,
    >
    > I don't really understand the problem, for example:
    > Are '5/26', '5/30' etc. Field Names?
    > Do they only have a single letter in them or could the A that we're looking
    > for be part of a larger string?
    > Could 'John Smith' appear in the table more than once?
    > ....
    >
    > Would it be possible for you to give a sample of the sort of data that is in
    > the table?
    >
    > Cheers,
    >
    > ChrisM.
    >
    > "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    > > How can I set up a conditional count in an Access query to calculate how
    > > many
    > > times a specific value occurs in each record for several fields (eg. I
    > > would
    > > like to know how many times the value "A" occurs for John Smith in the
    > > fields
    > > 5/26, 5/30, 6/1, 6/5, and 6/30)?

    >
    >
    >
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    OK,
    So the fields '5/26' etc. represent dates(?)
    So do you add a new field to the table for each relevant date? Or is there a
    pre-set number of these fields.
    How many are there/do you expect there to be eventually?

    If I've understood the above correctly, this is really not an ideal design,
    and any queries you write will probably have to be modified each time you
    add a new date column(field) to the table.

    Have you considered a slightly different design like:

    Student Date Attendance
    John Smith 5/26 A
    John Smith 5/30 P
    ....

    This will makes things easier though it depends how far down the road you've
    gone with your current design.

    Cheers,

    ChrisM

    "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    news:E43A7EC9-A0A4-41F8-A19D-72AD9B730F55@microsoft.com...
    > Yes, they are field names. They contain either "A" "P" "L" or "M/U". And
    > John Smith appears only once.
    > This is a query to track attendance. The fields are: Name, 5/26, 5/30,
    > 6/1,
    > 6/5, 6/30, etc.
    > The 'Name' field contains names of students ('John Smith')
    > The rest of the fields are dates of classes and contain either a "A" "P"
    > "L"
    > or "M/U" for each student (for Absent, Present, Late, Make-Up)
    > I would like to calculate how often each student was absent.
    >
    > "ChrisM" wrote:
    >
    >> Hi,
    >>
    >> I don't really understand the problem, for example:
    >> Are '5/26', '5/30' etc. Field Names?
    >> Do they only have a single letter in them or could the A that we're
    >> looking
    >> for be part of a larger string?
    >> Could 'John Smith' appear in the table more than once?
    >> ....
    >>
    >> Would it be possible for you to give a sample of the sort of data that is
    >> in
    >> the table?
    >>
    >> Cheers,
    >>
    >> ChrisM.
    >>
    >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    >> > How can I set up a conditional count in an Access query to calculate
    >> > how
    >> > many
    >> > times a specific value occurs in each record for several fields (eg. I
    >> > would
    >> > like to know how many times the value "A" occurs for John Smith in the
    >> > fields
    >> > 5/26, 5/30, 6/1, 6/5, and 6/30)?

    >>
    >>
    >>
     
  6. f2rox

    f2rox
    Expand Collapse
    Guest

    ChrisM,
    Once the query is set up i will not have to add any more fields. But, there
    are 80 class dates, and 30 students. So with your suggested design, I would
    have 2400 records. Also, my tables have some complicated relationships that
    would be messed up.
    Any ideas?
    But, assuming I was able to successfully use your design, how would I phrase
    the expression to calculate number of absences, latenesses, etc.


    "ChrisM" wrote:

    > OK,
    > So the fields '5/26' etc. represent dates(?)
    > So do you add a new field to the table for each relevant date? Or is there a
    > pre-set number of these fields.
    > How many are there/do you expect there to be eventually?
    >
    > If I've understood the above correctly, this is really not an ideal design,
    > and any queries you write will probably have to be modified each time you
    > add a new date column(field) to the table.
    >
    > Have you considered a slightly different design like:
    >
    > Student Date Attendance
    > John Smith 5/26 A
    > John Smith 5/30 P
    > ....
    >
    > This will makes things easier though it depends how far down the road you've
    > gone with your current design.
    >
    > Cheers,
    >
    > ChrisM
    >
    > "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > news:E43A7EC9-A0A4-41F8-A19D-72AD9B730F55@microsoft.com...
    > > Yes, they are field names. They contain either "A" "P" "L" or "M/U". And
    > > John Smith appears only once.
    > > This is a query to track attendance. The fields are: Name, 5/26, 5/30,
    > > 6/1,
    > > 6/5, 6/30, etc.
    > > The 'Name' field contains names of students ('John Smith')
    > > The rest of the fields are dates of classes and contain either a "A" "P"
    > > "L"
    > > or "M/U" for each student (for Absent, Present, Late, Make-Up)
    > > I would like to calculate how often each student was absent.
    > >
    > > "ChrisM" wrote:
    > >
    > >> Hi,
    > >>
    > >> I don't really understand the problem, for example:
    > >> Are '5/26', '5/30' etc. Field Names?
    > >> Do they only have a single letter in them or could the A that we're
    > >> looking
    > >> for be part of a larger string?
    > >> Could 'John Smith' appear in the table more than once?
    > >> ....
    > >>
    > >> Would it be possible for you to give a sample of the sort of data that is
    > >> in
    > >> the table?
    > >>
    > >> Cheers,
    > >>
    > >> ChrisM.
    > >>
    > >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > >> news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    > >> > How can I set up a conditional count in an Access query to calculate
    > >> > how
    > >> > many
    > >> > times a specific value occurs in each record for several fields (eg. I
    > >> > would
    > >> > like to know how many times the value "A" occurs for John Smith in the
    > >> > fields
    > >> > 5/26, 5/30, 6/1, 6/5, and 6/30)?
    > >>
    > >>
    > >>

    >
    >
    >
     
  7. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    Once you properly normalize the table (and I strongly suggest that you do),
    the query will be simple:

    Select Student, Attendance, Count(*) As AttandanceCount
    From YourTable
    Group By Student, Attendance;

    This will produce a file similar to:

    John, A, 3
    John, P, 2
    Mary, A, 1
    Mary, L, 1
    Mary, M/U, 1
    {censored word, do not repeat.}, L, 2

    An easy alternative if you want each attendance type listed across the top
    is to use the crosstab query wizard to build the query and then you'll end
    up with

    Student, A, P, L, [M/U]
    John, 3,2,0,0
    Mary, 1,0,1,0
    {censored word, do not repeat.}, 0,0,2,0

    Your table design reflects what you would have done had you built the
    application with Excel. But, tables with this design are handled poorly by
    relational databases and you are going to HATE Access and never know why.
    No matter how far your design has progressed, I suggest backing up and
    fixing the table definitions. 2400 records is nothing to a relational
    database. Many Access databases operate easily with tables containing
    millions of rows. In fact, you can reduce the number of rows substantially
    if you store only exception records. That means that a student does NOT
    have a row in the table when he was present. He only has a row when there
    is some exception activity to report. This might be a little tricky for you
    because it will involve working with a table of dates in many of your
    queries when you need all students to appear even though they have no
    exceptional activity.

    If you don't think you understand how to work with the sparse table, you can
    build a query that appends a row for each person in the class for a
    particular date. Place this code in a button on a form and run it
    immediately before you want to enter the exceptions for that day. Don't add
    these records ahead of time because you don't know what your class roster
    will be for every future day. It can easily change as the semester
    progresses. Then the subform should show only the records for a particular
    date and you can modify the ones that have exceptions.
    "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    news:4083932C-E525-425C-B06E-8B773E1F4B7C@microsoft.com...
    > ChrisM,
    > Once the query is set up i will not have to add any more fields. But,
    > there
    > are 80 class dates, and 30 students. So with your suggested design, I
    > would
    > have 2400 records. Also, my tables have some complicated relationships
    > that
    > would be messed up.
    > Any ideas?
    > But, assuming I was able to successfully use your design, how would I
    > phrase
    > the expression to calculate number of absences, latenesses, etc.
    >
    >
    > "ChrisM" wrote:
    >
    >> OK,
    >> So the fields '5/26' etc. represent dates(?)
    >> So do you add a new field to the table for each relevant date? Or is
    >> there a
    >> pre-set number of these fields.
    >> How many are there/do you expect there to be eventually?
    >>
    >> If I've understood the above correctly, this is really not an ideal
    >> design,
    >> and any queries you write will probably have to be modified each time you
    >> add a new date column(field) to the table.
    >>
    >> Have you considered a slightly different design like:
    >>
    >> Student Date Attendance
    >> John Smith 5/26 A
    >> John Smith 5/30 P
    >> ....
    >>
    >> This will makes things easier though it depends how far down the road
    >> you've
    >> gone with your current design.
    >>
    >> Cheers,
    >>
    >> ChrisM
    >>
    >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> news:E43A7EC9-A0A4-41F8-A19D-72AD9B730F55@microsoft.com...
    >> > Yes, they are field names. They contain either "A" "P" "L" or "M/U".
    >> > And
    >> > John Smith appears only once.
    >> > This is a query to track attendance. The fields are: Name, 5/26, 5/30,
    >> > 6/1,
    >> > 6/5, 6/30, etc.
    >> > The 'Name' field contains names of students ('John Smith')
    >> > The rest of the fields are dates of classes and contain either a "A"
    >> > "P"
    >> > "L"
    >> > or "M/U" for each student (for Absent, Present, Late, Make-Up)
    >> > I would like to calculate how often each student was absent.
    >> >
    >> > "ChrisM" wrote:
    >> >
    >> >> Hi,
    >> >>
    >> >> I don't really understand the problem, for example:
    >> >> Are '5/26', '5/30' etc. Field Names?
    >> >> Do they only have a single letter in them or could the A that we're
    >> >> looking
    >> >> for be part of a larger string?
    >> >> Could 'John Smith' appear in the table more than once?
    >> >> ....
    >> >>
    >> >> Would it be possible for you to give a sample of the sort of data that
    >> >> is
    >> >> in
    >> >> the table?
    >> >>
    >> >> Cheers,
    >> >>
    >> >> ChrisM.
    >> >>
    >> >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> >> news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    >> >> > How can I set up a conditional count in an Access query to calculate
    >> >> > how
    >> >> > many
    >> >> > times a specific value occurs in each record for several fields (eg.
    >> >> > I
    >> >> > would
    >> >> > like to know how many times the value "A" occurs for John Smith in
    >> >> > the
    >> >> > fields
    >> >> > 5/26, 5/30, 6/1, 6/5, and 6/30)?
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     
  8. f2rox

    f2rox
    Expand Collapse
    Guest

    Thanks, you have both been very helpful

    "Pat Hartman(MVP)" wrote:

    > Once you properly normalize the table (and I strongly suggest that you do),
    > the query will be simple:
    >
    > Select Student, Attendance, Count(*) As AttandanceCount
    > From YourTable
    > Group By Student, Attendance;
    >
    > This will produce a file similar to:
    >
    > John, A, 3
    > John, P, 2
    > Mary, A, 1
    > Mary, L, 1
    > Mary, M/U, 1
    > {censored word, do not repeat.}, L, 2
    >
    > An easy alternative if you want each attendance type listed across the top
    > is to use the crosstab query wizard to build the query and then you'll end
    > up with
    >
    > Student, A, P, L, [M/U]
    > John, 3,2,0,0
    > Mary, 1,0,1,0
    > {censored word, do not repeat.}, 0,0,2,0
    >
    > Your table design reflects what you would have done had you built the
    > application with Excel. But, tables with this design are handled poorly by
    > relational databases and you are going to HATE Access and never know why.
    > No matter how far your design has progressed, I suggest backing up and
    > fixing the table definitions. 2400 records is nothing to a relational
    > database. Many Access databases operate easily with tables containing
    > millions of rows. In fact, you can reduce the number of rows substantially
    > if you store only exception records. That means that a student does NOT
    > have a row in the table when he was present. He only has a row when there
    > is some exception activity to report. This might be a little tricky for you
    > because it will involve working with a table of dates in many of your
    > queries when you need all students to appear even though they have no
    > exceptional activity.
    >
    > If you don't think you understand how to work with the sparse table, you can
    > build a query that appends a row for each person in the class for a
    > particular date. Place this code in a button on a form and run it
    > immediately before you want to enter the exceptions for that day. Don't add
    > these records ahead of time because you don't know what your class roster
    > will be for every future day. It can easily change as the semester
    > progresses. Then the subform should show only the records for a particular
    > date and you can modify the ones that have exceptions.
    > "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > news:4083932C-E525-425C-B06E-8B773E1F4B7C@microsoft.com...
    > > ChrisM,
    > > Once the query is set up i will not have to add any more fields. But,
    > > there
    > > are 80 class dates, and 30 students. So with your suggested design, I
    > > would
    > > have 2400 records. Also, my tables have some complicated relationships
    > > that
    > > would be messed up.
    > > Any ideas?
    > > But, assuming I was able to successfully use your design, how would I
    > > phrase
    > > the expression to calculate number of absences, latenesses, etc.
    > >
    > >
    > > "ChrisM" wrote:
    > >
    > >> OK,
    > >> So the fields '5/26' etc. represent dates(?)
    > >> So do you add a new field to the table for each relevant date? Or is
    > >> there a
    > >> pre-set number of these fields.
    > >> How many are there/do you expect there to be eventually?
    > >>
    > >> If I've understood the above correctly, this is really not an ideal
    > >> design,
    > >> and any queries you write will probably have to be modified each time you
    > >> add a new date column(field) to the table.
    > >>
    > >> Have you considered a slightly different design like:
    > >>
    > >> Student Date Attendance
    > >> John Smith 5/26 A
    > >> John Smith 5/30 P
    > >> ....
    > >>
    > >> This will makes things easier though it depends how far down the road
    > >> you've
    > >> gone with your current design.
    > >>
    > >> Cheers,
    > >>
    > >> ChrisM
    > >>
    > >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > >> news:E43A7EC9-A0A4-41F8-A19D-72AD9B730F55@microsoft.com...
    > >> > Yes, they are field names. They contain either "A" "P" "L" or "M/U".
    > >> > And
    > >> > John Smith appears only once.
    > >> > This is a query to track attendance. The fields are: Name, 5/26, 5/30,
    > >> > 6/1,
    > >> > 6/5, 6/30, etc.
    > >> > The 'Name' field contains names of students ('John Smith')
    > >> > The rest of the fields are dates of classes and contain either a "A"
    > >> > "P"
    > >> > "L"
    > >> > or "M/U" for each student (for Absent, Present, Late, Make-Up)
    > >> > I would like to calculate how often each student was absent.
    > >> >
    > >> > "ChrisM" wrote:
    > >> >
    > >> >> Hi,
    > >> >>
    > >> >> I don't really understand the problem, for example:
    > >> >> Are '5/26', '5/30' etc. Field Names?
    > >> >> Do they only have a single letter in them or could the A that we're
    > >> >> looking
    > >> >> for be part of a larger string?
    > >> >> Could 'John Smith' appear in the table more than once?
    > >> >> ....
    > >> >>
    > >> >> Would it be possible for you to give a sample of the sort of data that
    > >> >> is
    > >> >> in
    > >> >> the table?
    > >> >>
    > >> >> Cheers,
    > >> >>
    > >> >> ChrisM.
    > >> >>
    > >> >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    > >> >> news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    > >> >> > How can I set up a conditional count in an Access query to calculate
    > >> >> > how
    > >> >> > many
    > >> >> > times a specific value occurs in each record for several fields (eg.
    > >> >> > I
    > >> >> > would
    > >> >> > like to know how many times the value "A" occurs for John Smith in
    > >> >> > the
    > >> >> > fields
    > >> >> > 5/26, 5/30, 6/1, 6/5, and 6/30)?
    > >> >>
    > >> >>
    > >> >>
    > >>
    > >>
    > >>

    >
    >
    >
     
  9. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Glad to be of service.
    If you need any further assistance with re-structuring (normaliSing) your
    database, let us know, and I'm sure me, or someone else will be able to
    help.

    If you're new to access, personally, I would recommend you added a row to
    the table for each student for each class date, whether they attend or not.
    Dealing with a 'sparse' table will only give you problems, until you are
    more confident with the way relational databases work.

    For what its' worth, the query to do what you originally asked with a table
    with 80 date fields will be horrific to write and maintain,
    Not even sure of the best way to do it, but it could be somthing like:

    SELECT StudentName,
    ((iif([5/23]="A",1,0)+(iif([5/27]="A",1,0)+(iif([5/30]="A",1,0)...for each
    of the 80 date fields...) as Absent, ((iif([5/23]="P",1,0) +... for each
    date field...) as Present

    Nasty! If there is ANY way you can redesign, please (for your own sanity) do
    it...

    Cheers,

    Chris.

    "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    news:269C2C09-6B99-459B-9ABB-68190E7646FE@microsoft.com...
    > Thanks, you have both been very helpful
    >
    > "Pat Hartman(MVP)" wrote:
    >
    >> Once you properly normalize the table (and I strongly suggest that you
    >> do),
    >> the query will be simple:
    >>
    >> Select Student, Attendance, Count(*) As AttandanceCount
    >> From YourTable
    >> Group By Student, Attendance;
    >>
    >> This will produce a file similar to:
    >>
    >> John, A, 3
    >> John, P, 2
    >> Mary, A, 1
    >> Mary, L, 1
    >> Mary, M/U, 1
    >> {censored word, do not repeat.}, L, 2
    >>
    >> An easy alternative if you want each attendance type listed across the
    >> top
    >> is to use the crosstab query wizard to build the query and then you'll
    >> end
    >> up with
    >>
    >> Student, A, P, L, [M/U]
    >> John, 3,2,0,0
    >> Mary, 1,0,1,0
    >> {censored word, do not repeat.}, 0,0,2,0
    >>
    >> Your table design reflects what you would have done had you built the
    >> application with Excel. But, tables with this design are handled poorly
    >> by
    >> relational databases and you are going to HATE Access and never know why.
    >> No matter how far your design has progressed, I suggest backing up and
    >> fixing the table definitions. 2400 records is nothing to a relational
    >> database. Many Access databases operate easily with tables containing
    >> millions of rows. In fact, you can reduce the number of rows
    >> substantially
    >> if you store only exception records. That means that a student does NOT
    >> have a row in the table when he was present. He only has a row when
    >> there
    >> is some exception activity to report. This might be a little tricky for
    >> you
    >> because it will involve working with a table of dates in many of your
    >> queries when you need all students to appear even though they have no
    >> exceptional activity.
    >>
    >> If you don't think you understand how to work with the sparse table, you
    >> can
    >> build a query that appends a row for each person in the class for a
    >> particular date. Place this code in a button on a form and run it
    >> immediately before you want to enter the exceptions for that day. Don't
    >> add
    >> these records ahead of time because you don't know what your class roster
    >> will be for every future day. It can easily change as the semester
    >> progresses. Then the subform should show only the records for a
    >> particular
    >> date and you can modify the ones that have exceptions.
    >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> news:4083932C-E525-425C-B06E-8B773E1F4B7C@microsoft.com...
    >> > ChrisM,
    >> > Once the query is set up i will not have to add any more fields. But,
    >> > there
    >> > are 80 class dates, and 30 students. So with your suggested design, I
    >> > would
    >> > have 2400 records. Also, my tables have some complicated relationships
    >> > that
    >> > would be messed up.
    >> > Any ideas?
    >> > But, assuming I was able to successfully use your design, how would I
    >> > phrase
    >> > the expression to calculate number of absences, latenesses, etc.
    >> >
    >> >
    >> > "ChrisM" wrote:
    >> >
    >> >> OK,
    >> >> So the fields '5/26' etc. represent dates(?)
    >> >> So do you add a new field to the table for each relevant date? Or is
    >> >> there a
    >> >> pre-set number of these fields.
    >> >> How many are there/do you expect there to be eventually?
    >> >>
    >> >> If I've understood the above correctly, this is really not an ideal
    >> >> design,
    >> >> and any queries you write will probably have to be modified each time
    >> >> you
    >> >> add a new date column(field) to the table.
    >> >>
    >> >> Have you considered a slightly different design like:
    >> >>
    >> >> Student Date Attendance
    >> >> John Smith 5/26 A
    >> >> John Smith 5/30 P
    >> >> ....
    >> >>
    >> >> This will makes things easier though it depends how far down the road
    >> >> you've
    >> >> gone with your current design.
    >> >>
    >> >> Cheers,
    >> >>
    >> >> ChrisM
    >> >>
    >> >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> >> news:E43A7EC9-A0A4-41F8-A19D-72AD9B730F55@microsoft.com...
    >> >> > Yes, they are field names. They contain either "A" "P" "L" or
    >> >> > "M/U".
    >> >> > And
    >> >> > John Smith appears only once.
    >> >> > This is a query to track attendance. The fields are: Name, 5/26,
    >> >> > 5/30,
    >> >> > 6/1,
    >> >> > 6/5, 6/30, etc.
    >> >> > The 'Name' field contains names of students ('John Smith')
    >> >> > The rest of the fields are dates of classes and contain either a "A"
    >> >> > "P"
    >> >> > "L"
    >> >> > or "M/U" for each student (for Absent, Present, Late, Make-Up)
    >> >> > I would like to calculate how often each student was absent.
    >> >> >
    >> >> > "ChrisM" wrote:
    >> >> >
    >> >> >> Hi,
    >> >> >>
    >> >> >> I don't really understand the problem, for example:
    >> >> >> Are '5/26', '5/30' etc. Field Names?
    >> >> >> Do they only have a single letter in them or could the A that we're
    >> >> >> looking
    >> >> >> for be part of a larger string?
    >> >> >> Could 'John Smith' appear in the table more than once?
    >> >> >> ....
    >> >> >>
    >> >> >> Would it be possible for you to give a sample of the sort of data
    >> >> >> that
    >> >> >> is
    >> >> >> in
    >> >> >> the table?
    >> >> >>
    >> >> >> Cheers,
    >> >> >>
    >> >> >> ChrisM.
    >> >> >>
    >> >> >> "f2rox" <f2rox@discussions.microsoft.com> wrote in message
    >> >> >> news:44436CFE-A817-404A-A263-56FEC0C2349E@microsoft.com...
    >> >> >> > How can I set up a conditional count in an Access query to
    >> >> >> > calculate
    >> >> >> > how
    >> >> >> > many
    >> >> >> > times a specific value occurs in each record for several fields
    >> >> >> > (eg.
    >> >> >> > I
    >> >> >> > would
    >> >> >> > like to know how many times the value "A" occurs for John Smith
    >> >> >> > in
    >> >> >> > the
    >> >> >> > fields
    >> >> >> > 5/26, 5/30, 6/1, 6/5, and 6/30)?
    >> >> >>
    >> >> >>
    >> >> >>
    >> >>
    >> >>
    >> >>

    >>
    >>
    >>
     

Share This Page