Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Complicated Query

    SELECT DISTINCTROW CBAll.OfcName, Sum(SCMLS.SalePrice) AS TSODV, Count(*) AS TSORecs
    FROM CBAll INNER JOIN SCMLS ON CBAll.OfcName = SCMLS.SellName
    GROUP BY CBAll.OfcName;

    This query works - however every time I run it, it opens a small window that ask . . . . TblName.parameter "Enter a parameter and thenan OK and Cancel button.

    If I just click OK button the query runs. How do I get rid of the opening window so the query just runs?

    Also, there are 16 offices in the CBAll.tbl but only four of them show in the results. Why are the other 13 offices showing up with answers?

    Next . . . there are two very similar additional queries that run with this one each reporting a value for TSODV and TSORecs.

    After I run the three querys I need to add these values (TSODV and TSORecs) and a few others together and then show them on the last page of a report for each of the offices (16 in this case) in the CBAll.tbl?

    I don't know the first thing about this in MS Access.

    I do have it programmed in ObjectPal Pardox.

    Any assistance would be greatly appreciated.

    Thanks . . . Rick

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    How do I get rid of the opening window so the query just runs?

    It's a feature, not a bug.

    When Access processes your query, if you refer to a column it doesn't know about, it treats this as an unspecified paramter and, helpfully, asks you for its value.

    Obviously, somewhere in your query, you're referring to TblName.parameter.

    But, you're thinking, I can see my SQL and TblName.parameter is nowhere to be seen.

    Here's the fun part: it could be in your filter or sort. So go to the Advanced Filter/Sort menu and open that up.

    What probably happened is that when you first created the query you were sorting on TblName.parameter. Then you changed its name, but Access left it in the filter or sort without changing it. So now it thinks you want to sort or filter on a constant value.

  3. #3
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Rick Schreiber
    SELECT DISTINCTROW CBAll.OfcName, Sum(SCMLS.SalePrice) AS TSODV, Count(*) AS TSORecs
    FROM CBAll INNER JOIN SCMLS ON CBAll.OfcName = SCMLS.SellName
    GROUP BY CBAll.OfcName;

    Also, there are 16 offices in the CBAll.tbl but only four of them show in the results. Why are the other 13 offices showing up with answers?
    If there are 16 offices and you see 4, aren't you looking for 12, not 13?

    And how am I supposed to figure out why some data isn't returned by your query without seeing the data? You probably don't want an INNER JOIN, but if that's your mistake you probably don't know SQL and I forget whether you'd want to use Count(*) or Count(OfcName) if you did an outer join.

    I think I'll just fire you. Problem solved.

Posting Permissions

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