Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: HELP! Problem with Report, seems really easy to fix but just cannot figure it out !!

    Hello, I was hoping to get some advice on a report i'm creating. I created an Asset Tracking database and on one of the reports that I have to generate every month is a count of how many employees (represented here by classification) have a particular asset. I created a report that basically lists all the assets for all classications (all employees). This is the report that is generated below:

    Classification Asset Category ID
    AS-02 Home PC/Laptop
    AS-02 Home PC/Laptop
    AS-03 Blackberry
    AS-01 Blackberry
    AS-01 Cellular Phone
    AS-01 Cellular Phone

    What I want however is this:

    Classification Asset Category ID Total
    AS-02 Home PC/Laptop 2
    AS-03 Blackberry 1
    AS-01 Blackberry 1
    AS-01 Cellular Phone 2

    This seems like a no brainer, but honnestly, I've been trying to 5 hours lol. Please help me

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try this query:

    SELECT Classification, AssetCategoryID, Count(*) AS HowMany
    FROM TableName
    GROUP BY Classification, AssetCategoryID
    Paul

  3. #3
    Join Date
    May 2009
    Posts
    4
    Thank you!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem, and welcome to the site by the way!
    Paul

  5. #5
    Join Date
    May 2009
    Posts
    4

    Help!

    If possible, can someone please tell me what is wrong with the following SQL statement:

    SELECT; Classification.ClassificationName, AssetCategories.AssetCategory,
    count(Employees.EmployeeID)
    FROM Classification, Employees, Assets, Asset Categories
    WHERE; Classification.ClassficationID = Employees.ClassificationID
    AND Employees.EmployeeID = Assets.EmployeeID
    AND Asset Categories.AssetCategoryID = Assets.AssetCategoryID
    GROUP BY ClassficationName, AssetCategory, Employees.EmployeeID

    Side note: I'm trying to get the following result in the query

    Column 1 Column 2 Column 3
    (ClassificationName) (Asset Category) (Total Count)
    i.e AS-1 Blackberry 10
    AS-2 Home/PC 15

    Thanks in advance!

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    For one thing, you don't want Employees.EmployeeID in the GROUP BY clause.
    Paul

  7. #7
    Join Date
    May 2009
    Posts
    4
    Thanks Paul, I jsut figure it out, the problem I had was in the relationships I created between my tables. I used the query wizard to build the SQL statement and it worked after modifying the relationships.

    Best Regards,

Posting Permissions

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