Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Unanswered: Counting Horizontally?

    I manage databases in my school for my business teacher. I created attendance databases for different committes. My columns are Student ID, followed by several dates. I use yes/no boxes to check if they have attended that date. Now my problem is that my teacher wants to be able to count how many meetings an individual has been too when i create reports. Meaning i need a formula for counting horizontally. I have no problem with counting vertically, or how many kids showed up to a meeting, but this is really stumping me. Thanks for any help.

  2. #2
    Join Date
    Oct 2003
    Location
    Canada
    Posts
    574
    Wow. The easy solution is to create a table to hold the attendance dates. you would need to write a query to append the data from the first date column then you could edit that query and just change the date and run it for the rest of the columns. Then you are counting vertically again. ANd when you need a report with them horizontal... use a crosstab query.

    You can get a count of your columns by going though the tabledef. I have the code for that at work, but check in help for examples on tabledef.fileds. something like:

    dim tdf as tabledef, fld as field, cnt as integer
    tdf = tabledefs("AttendanceTableName") 'you may need set at the start
    for each fld in tdf
    cnt=cnt+1
    loop

    subtracting out the non-date columns will give you your count.

  3. #3
    Join Date
    Oct 2002
    Location
    Leicester - UK
    Posts
    820
    not only the easiest but the best not to mention the way it should have been done orginaly so as to allow this, now what i'd suggest is remaking the table so it reads

    StudentID, Date

    you can then use a form to goose the data into this table while still giving the user a check box form and a cross tab query will get the data out in a simalar format

    otherwise your in for some tricky and time comsuming work which involes manipulating the data in VBA and dumping it back into a seperate table
    Definition of a Beginner, Someone who doesn't know the rules.

    Definition of an Expert, Someone who knows when to ignore the rules.

Posting Permissions

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