Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64

    Unanswered: Report to count data items

    I am designing a database to help a school keep track of incidents of bad behaviour. There are 12 categories of behaviour, identified by a number and a description: e.g. 1 = Physical assault against student ,2 = Physical assault against staff, 3 = Theft, etc etc.
    When an incident occurs, staff record the relevant number in the database as an “Incident Code”. There can be up to 4 Incident Codes for each record. (Staff felt they didn’t need the facility to enter more than 4 codes, as most incidents only fell into one or possibly two categories). Therefore, in the data table, there will be a number for each incident code which identifies the behaviour category. E.g. IC1=1, IC2=4,IC3=12,IC4=Null.
    I would like to make a report which will count the number of instances of each category, so that the school can document how many assaults, thefts, etc are taking place. I’ve tried to use the “Count” command in the report, but it doesn’t really do what I want it to as it just counts how many IC1’s,IC2’s and so on.
    Sorry if I haven’t explained this very well, but hope someone can point me in the right direction.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    More information needed about your table structures.
    Plase include some sample data and expected results.

    Why not also show us what you have tried so far!
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Bad table design ... How about:

    IncidentTable

    IncidentID
    IncidentCode
    StudentID
    IncidentDate



    Then you can make a query to do your counts:
    SELECT COUNT(IncidentCode) AS IncidentCount FROM IncidentTable WHERE (IncidentDate BETWEEN #1/1/07# AND #6/15/07#) GROUP BY IncidentCode;
    Back to Access ... ADO is not the way to go for speed ...

  4. #4
    Join Date
    Mar 2007
    Location
    Bedfordshire, UK
    Posts
    64
    I have a table with 12 fields containing details of the incident. The school currently use a paper form, and the table is simply an electronic version of that. I've attached a document which shows the fields and example data.
    Attached Files Attached Files

Posting Permissions

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