Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: Query to Summary Table

    Hello All,

    I am attempting to create a summary table in Microsoft Access from another table.

    I want the Summary table to have three columns

    Lawson, Uabs, Adherence.

    I want the query to count the number of Uabs and Adherences and insert the data along with the Lawson into a Summary table.

    I have tried several count statements, nested queries, etc but am having no luck.

    Here is what the data looks like.
    Table: Attendance

    Lawson Date Name Occurence Letter Comments
    120486
    121393 2/6/2009 Alexander, Jeffrey Adherence Late-16 mins
    121393 1/24/2009 Alexander, Jeffrey Adherence Late- 5 mins
    121393 5/16/2009 Alexander, Jeffrey Adherence Late- 1 hr 28 mins
    121393 5/8/2009 Alexander, Jeffrey Adherence Late- 4 mins
    121393 2/13/2009 Alexander, Jeffrey Adherence Late- 4 mins from break
    121393 5/30/2009 Alexander, Jeffrey Adherence Left Early- 2 hr 45 mins
    121393 6/13/2009 Alexander, Jeffrey Adherence Written - Adherence Written re-issued
    121393 4/23/2009 Alexander, Jeffrey Adherence Late- 5 mins from break
    121393 1/19/2009 Alexander, Jeffrey Uabs Car trouble
    121393 3/15/2009 Alexander, Jeffrey Uabs Sick
    121393 2/14/2009 Alexander, Jeffrey Adherence Late- 4 mins from lunch
    121393 3/27/2009 Alexander, Jeffrey Adherence Late- 10 mins
    121393 1/13/2009 Alexander, Jeffrey Written - Adherence
    101156 3/8/2009 Antao, Francine Adherence Left Early
    101156 3/15/2009 Antao, Francine Uabs
    101156 3/22/2009 Antao, Francine Adherence 2 hrs 59 mins late
    101156 3/31/2009 Antao, Francine Adherence Left Early
    101156 4/26/2009 Antao, Francine Uabs
    101156 5/3/2009 Antao, Francine Adherence Left Early
    101156 2/1/2009 Antao, Francine Uabs

  2. #2
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Have you tried using a crosstab?

    For a crosstab, your Occurence Letter field should only have Uabs or Adherence, you have Written with Adherence in the comments. If you do not have these two entries then the crosstab will produce extra columns based on the other words in the field.

    Then use an append query to append your crosstab to your table.

  3. #3
    Join Date
    Jun 2009
    Posts
    12
    Oh sorry the written is in a different column... That column has three value, either uabs, adherence, or is empty.

  4. #4
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    No problem. A crosstab will work, in your crosstab your row headings will be Lawson, your Column Heading will be Occurence and you will count on date.

  5. #5
    Join Date
    Jun 2009
    Posts
    12
    Awesome that works great.... Now I have another problem.. I have two cross tab queries.. One to count the number of Uabs and Adherence for each Lawson. And one to show the last date from Occurence for each Lawson.

    When I append the two together I get over 7000 results rather then 86..

    The occurence field has 8 possible values. So for "Written - Uabs" I need to know when it was entered for that Lawson and then put that date in the summary table.

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Correct me if I am wrong for each Lawson you need to know the last date when the relevant Occurence was entered? So for Lawson 121393 you need to know the last date of entry for Adherence, UABS etc. keeping in mind there maybe 8 possible values.

    Create a query with the following fields Lawson, Occurence and Date.
    From the View menu select the Totals and the query will change to a totals query.

    Leave the Group by for the Lawson and Occurence columns BUT change the Group by in Date to DMAX.

    The above will give you the Last Date for each Occurence value for each Lawson.

    Then use this query to update Date field in the table that currently stroes the results of your crosstab count of Occurences.

    If need assistance, please send a copy of your Attendance table and the table that you are appending the crosstab. Also include your crosstab queries.

  7. #7
    Join Date
    Jun 2009
    Posts
    12
    Ok I figured out how to append all of the information to the table.. How do I update the table when there are changes to the data. So if a Lawson has more Uabs how do I update the table..

    I tried using the append to update it but that doesn't seem to work. Gives me the error "The Insert Into statement contains the following unknown field name:"

    Which obviously the field name is correct because I used it to append the original data to the table.

Posting Permissions

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