Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Question (Access 2003)

    I am working with a data base used to track HR issues reported to our HR department. They had been using Excel to do this but the reporting is getting to be too complex to continue using Excel.

    But I am having to remember how to write queries. And the help files aren't helping me.

    I need to write a query that includes: The Issue Types by month, (i.e. those issue types that occured in the month of May) I need to be able to count the issues so that we can tell how many of each type occured in the month. The query that I wrote includes, The Issue type in the first column with a Total left at Group By (To display the Issue name), the Issue Type again in the second column with a Total of Count to show how many of each issue type has been reported, and the date opened in the third column with a parameter criteria of Between [Start Date] And [End Date] so I can determine which month to pull the issues from.

    The problem I am having is that the results shows all of the issues (Sorted ascending order) the number of each type in the next column and the dates in the third. The dates in the month all show up though, so that if the same type of issue was reported multiple times in the month it appears that many times in the results when what I need is a listing of all of the issue types with the number of times each issue was reported.

    I would post the database, but it is confidential so I can't. Let me know if this is not clear enough. and I will try to clarify.

    Thanks

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

    Re: Grouping Question (Access 2003)

    Set the Total option for the date field to Where. This tells Access that the date field is only to be used for the criteria, but not to be displayed.

  3. #3
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Hans, Thanks for the help. This looks like what I might need, but I don't understand the syntax for the WHERE total command. I have attached a screen cap so you can see how far I have gotten.

    Thom

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

    Re: Grouping Question (Access 2003)

    Clear the Sort on the DateOpened column. The query should work then.

    In a Select query, you can sort on a field that is not displayed, but in a Totals query, that doesn't make sense.

  5. #5
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Thanks,

    That worked. Time to hit the books and get myself back up to speed on the rest of the program.

    Thom

  6. #6
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Hans,

    I need to create a version of this that will display the same information but on a year to date basis. What is the syntax to display the info. I am trying to use #1/1/2005# as the first part of the query and "TODAY" as the second criteria so that the query won't require any actual input by the user. Is this possible? And if so what is the syntax for a Between And query?

    Thanks

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

    Re: Grouping Question (Access 2003)

    TODAY() is an Excel worksheet function. Access uses the VB/VBA function Date() instead. You can use the following in the Criteria row:

    Between #1/1/2005# And Date()

    If you want the criteria to be independent of the year, you can use

    Between DateSerial(Year(Date()),1,1) And Date()

  8. #8
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Hi Hans,

    Another question. I am trying to create a nested query (I think) so that I can show the results for several months but the count keeps showing an odd number, 552, for all the issues. I have posted a screen shot of the design view. and the table view is in the next reply.

  9. #9
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Here is the results view. Any thoughts on how to get this to work will be greatly appreciated. I have to create a number of reports based on queries for consecutive months. i.e. the issues in the left column, and the count of each issue by month in the next several columns.

    Thanks

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

    Re: Grouping Question (Access 2003)

    As far as I can see, this query shouldn't be a totals query. You should join the three queries (for April, May and June) on the Issue field.

    Will there (possibly) be issues that don't occur in all three months? If so, you need an extra query - post back if so.

  11. #11
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Thanks for the quick post. Yes the issues vary by month. Which was why I was trying to group by issue and count by total so that all issues that are listed in each month are included.

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

    Re: Grouping Question (Access 2003)

    Try this: create a union query to get all issues. You must switch to SQL view and write the SQL code:

    SELECT Issue FROM [qry IssuesApr2005]
    UNION
    SELECT Issue FROM [qry IssuesMay2005]
    UNION
    SELECT Issue FROM [qry IssuesJun2005]

    Save this as qryIssues.

    Next, create a new query and add qryIssues, qry IssuesApr2005, qry IssuesMay2005 and qry IssuesJun2005.
    Join qryIssues to each of the others on Issue, then double click the join line and specify that you want to return all records from qryIssues.
    Add the Issue field from qryIssues, and the Total field from each of the other queries, with an appropriate alias (TotalApr, TotalMay and TotalJun for example)

  13. #13
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    That worked great. Now how do I get the empty cells in the table to show a zero rather than be blank?

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

    Re: Grouping Question (Access 2003)

    Try

    Apr: Val(Nz([qry IssuesApr2005].[Total],0))

    etc.

  15. #15
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Question (Access 2003)

    Hans, That worked great. Thanks alot.

    Now the next thing I have to do is to determine what percent of the total issues inititated by each location were initiated by the General Mgr. and not someone else at the location. I created a query (Below) that will compute the percentage for a month, But I need to be able to join several queries together so that they will line up by location across three months worth of data. I seem to be able to join two months, but not three. When I include the fields I need for all three months I get an error message talking about ambiguous outer joins. Do I need to create a joined query between the first two months and then another query including the joined query and the third month? I hope that makes sense.

Page 1 of 2 12 LastLast

Posting Permissions

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