Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    cannot get query results right (97)

    cannot get query results right
    I have a main query with my transaction data. Transaction are separated into 2 types. I am trying to get a report that lists, by type, every month and the totals of the transaction data per Director, for that month. So my query ought to look like this:

    Type Month Director unitSum
    -----------------------------------------
    OA 1 1100 555
    OA 1 1200 25
    .
    .
    OA 12 1500 2455
    .
    .
    BE 1 1100 990
    .
    .

    I

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: cannot get query results right (97)

    I looked at your <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=75165>earlier thread</A> to get a bit of context. I'm not sure I understand the need for nested joins; we typically create a number of queries in the Design window, and then base the next query on the one that went before. Sometimes, when I think the join is taking too long in memory, I use an intermediate Make Table query and work from there. (We end up with a macro to run each necessary part.) This "fragmented" approach is good for debugging queries because you can run each one in turn and examine where things are disappearing or doubling.

    I have written a couple of reports like the one you describe, using the following approach:
    (1) join all necessary raw data in one select or make table query (e.g., select for a given date range)

    (2) run a query to appropriately group by and sum the raw data, sorting as I want it to appear in the report

    At this point, you should be able to determine whether data is missing or doubling.

    (3) depending on the needs of the report layout, add any other necessary manipulations

    Particular challenges arise when you want to represent a table of a fixed set of rows and columns but some data is missing. For example, you might want to show January through December, but one employee started in March. In these cases, I find it useful to first run a make table query that simply establishes blank or zero data for the relevant people. Then I run update queries to fill in the available data. The report draws from the table, and is the last step in a macro that automates all the intermediate queries.

    I don't know how applicable these experiences are to your situation, but hopefully they will give you some alternative approaches to debugging your query and getting the data consistency you're looking for.

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cannot get query results right (97)

    please see attached as i'm still working on this, it's a screenshot of my query, and ofcourse you can see the far right is repeating.
    Attached Images Attached Images

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: cannot get query results right (97)

    Post the SQL of your query. It's impossible to guess at what it's doing based on a screen shot.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: cannot get query results right (97)

    the enclosed SQL is repeating the sumOfActivationForecast each time

    SELECT tblMonth.Type, tblMonth.Month, NewQuery.RegionalDirectorCode, Sum(NewQuery.ActivationForecast) AS SumOfActivationForecast
    FROM tblMonth LEFT JOIN NewQuery ON tblMonth.Type = NewQuery.PromotionType
    GROUP BY tblMonth.Type, tblMonth.Month, NewQuery.RegionalDirectorCode;

    Removing the months from the SQL above (tblMonth.Month) however doubles the value of the sumOfActivationForecast.

Posting Permissions

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