Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002

    Question Unanswered: The boss wants me to do this...

    This is a crosstab query in Access, hopefully this is the right section of the forums to place this....

    I am getting the right results as far as what the boss wants to see, but
    he just asked to see all the Equipment Names for the row headers, not just the ones that have results for the time period which is what I have now.

    That way he can see which equipment isnt having any operations, as well as the equipment that is.
    Any thoughts how to accomplish this?

    PARAMETERS [Start Date] DateTime, [End Date] DateTime;
    TRANSFORM Count([Operation Entry].[Operation Status ID No]) AS [CountOfOperation Status ID No1]
    SELECT [Equipment Type].[Equipment Name]
    FROM ([Operation Entry] INNER JOIN [Operation/Failure] ON [Operation Entry].[Operation Status ID No] = [Operation/Failure].[Operation Status ID No]) INNER JOIN [Equipment Type] ON [Operation Entry].[Equipment ID No] = [Equipment Type].[Equipment ID No]
    WHERE ((([Operation Entry].[Operation Status ID No])=1 Or ([Operation Entry].[Operation Status ID No])=2) AND (([Operation Entry].[Class ID No])="4") AND (([Operation Entry].RecDate) Between [Start Date] And [End Date]))
    GROUP BY [Equipment Type].[Equipment Name]
    ORDER BY "# " & [Operation Status Name] DESC
    PIVOT "# " & [Operation Status Name];

  2. #2
    Join Date
    Nov 2002
    I ended up making a query of the crosstab query and included the equipment table. Right join on the equiptable from the crosstab query.
    It gave me two rows of equipment, but one of them was correct because it showed all equipment values.
    So when I made the report I just didnt include the crosstab field from the original query.
    It's a hack way of doing it but it works ...

  3. #3
    Join Date
    Feb 2004
    In front of the computer
    Provided Answers: 54
    Isn't the universe actually a collection of hacks, bound together with kludges?


Posting Permissions

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