Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2008
    Posts
    10

    Unanswered: Hookom Query By Fields Form Question

    I love this form - I can't wait to get to work every day so I can use this
    form to teach another of my users to write their own reports without my doing
    it. FANTASTIC FORM

    I am working with the following crosstab query SQL
    TRANSFORM Count([<qryMonthlyThreeMonths>].IncidentID) AS CountOfIncidentID
    SELECT [<qryMonthlyThreeMonths>].Agency,
    [<qryMonthlyThreeMonths>].MonitoringRegion,
    [<qryMonthlyThreeMonths>].InitialIncidentCategory,
    [<qryMonthlyThreeMonths>].InitialIncidentSubCategory,
    Count([<qryMonthlyThreeMonths>].IncidentID) AS [Total Of IncidentID]
    FROM [<qryMonthlyThreeMonths>]
    WHERE ((([<qryMonthlyThreeMonths>].IncidentDate) Between
    DateAdd("yyyy",-20,Date()) And Date()))
    GROUP BY [<qryMonthlyThreeMonths>].Agency,
    [<qryMonthlyThreeMonths>].MonitoringRegion,
    [<qryMonthlyThreeMonths>].InitialIncidentCategory,
    [<qryMonthlyThreeMonths>].InitialIncidentSubCategory
    PIVOT Format([IncidentDate],"yyyy mm") & "(This month - " &
    DateDiff("m",[IncidentDate],Date()) & ")";

    I use this to grab specific months of data from the last 20 months. In the
    form I can designate which months I want and go.
    The trouble is that I get all the groupings of data even if there is no data
    for that group in the specific months that I asked for in the form.

    So, somewhere in the 20 months there is a month that had one record of
    [<qryMonthlyThreeMonths>].Agency, = A
    [<qryMonthlyThreeMonths>].MonitoringRegion, = B
    [<qryMonthlyThreeMonths>].InitialIncidentCategory, = C
    and[<qryMonthlyThreeMonths>].InitialIncidentSubCategory = D

    That month is not one the months I am asking for in the form.

    I still get that record but when I move across and look in my 4 months there
    is no data.

    Is there a way to tell the form or the query that I only want records that
    have data in one of my designated four (or six or eight) months?

    I hope this is clear.
    --
    Thanks

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I hope this is clear.
    Far from it. The only thing that is clear to me is that you love your form.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Apr 2008
    Posts
    10

    Talking StarTrekker

    Thanks so much for your reply.
    It's clear you use your vast expertise to make a difference for peple on this forum.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    looks like you are getting the last 20 years not the last four months...
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2008
    Posts
    10
    Exactly. Does anyone have any useful suggestions? Thanks

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by knowshowrosegrows
    Thanks so much for your reply.
    It's clear you use your vast expertise to make a difference for peple on this forum.
    I try to

    You've used this: DateAdd("yyyy",-20,Date()) in your SQL. This means 20 years ago. You mentioned 20 months, so this is what pootle flump is referring to.

    You might want to consider making a query that ensures you have relevant data first and then base your crosstab query on that. This might ensure you have data in the columns you want to see. I can't be sure though.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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