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

    report sql repeatings for a particular record (97)

    report sql repeatings for a particular record
    I wrote the following sql:

    SELECT qry1.Type, qry1.Month, qry1.MonthName, Director.Code, Director.DirectorName, Sum(qry1.SumOfResults) AS SumOfSumOfResults, Sum(qry1.SumOfActivationForecast) AS SumOfSumOfActivationForecast, Sum(qry1.SumOfTotalExpressCommits) AS SumOfSumOfTotalExpressCommits, Sum(qry1.SumOfTotalPaid) AS SumOfSumOfTotalPaid, Sum(qry1.SumOfAccrual) AS SumOfSumOfAccrual, Sum(qry1.SumOfProjectedSpending) AS SumOfSumOfProjectedSpending
    FROM qry1, Director INNER JOIN NewQuery ON Director.Code = NewQuery.Code
    GROUP BY qry1.Type, qry1.Month, qry1.MonthName, Director.Code, Director.DirectorName;

    Currently there are 5 directors who end up being listed but 2 of them wind up displaying the same results. This makes me wonder why it is happening for those two, but one else.

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: report sql repeatings for a particular record (97)

    I'm not an SQL guru, but it seems to me that you have no relationship defined between qry1 and Director. This will join every record with qry1 to every record in Director (called a cartesian join).
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: report sql repeatings for a particular record (97)

    You're right, Mark. That's exactly what will happen in this query.
    Charlotte

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: report sql repeatings for a particular record (97)

    I usually cheat and use the QBE grid for my own work, so it's sometimes hard to decipher someone else's SQL statement. So thanks for the confirmation!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: report sql repeatings for a particular record (97)

    This is the giveaway:

    FROM qry1, Director

    Anytime you have a source table or query listed like that with no join, you're going to have a cartesian product *unless* criteria has been entered to create an implied join. Since there's no WHERE clause in this at all, that isn't the case.
    Charlotte

Posting Permissions

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