Results 1 to 4 of 4

Thread: query troubles

  1. #1
    Join Date
    May 2003
    Posts
    22

    Unanswered: query troubles

    I used a query in VB with an access form as the front end to create a report...I have modified it to try to make it work in SQL Server...it is returning too many fields though currently.....

    Select dev.KinderNumber,dev.Client,dev.MFG,dev.FAMILY,dev .Intro_Date,dev.FIXTURE,dev.Dimensions_HWD,
    dev.MFG_Price,dev.Item_Number,dp.picname from Development
    as dev, DevelopmentPictures as dp
    RIGHT JOIN Development ON dp.[KinderNum] = Development.[KinderNumber]
    WHERE dp.[Revision_Letter]
    IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = dp.KinderNum);

    this returns about 57000 rows I usually have about 350-400..

    also : IN(select max([Revision_Letter]) from DevelopmentPictures where DevelopmentPictures.KinderNum = KinderNum);

    if I take out the dp from dp.kindernum it only retunrs the row headings??

    Please help!

    thanks in advance,

    John

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What does Development as dev join to? May be a cross join there...

  3. #3
    Join Date
    May 2003
    Posts
    26

    Re: query troubles

    Since you are comparing the results from two different databases, VB and SQL Server, I'd suggest first running a quick check on the tables to see if you've got the same number of rows in both.

  4. #4
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    jfouse

    You didn't say what your query should return so this is a bit of a guess.

    Code:
    Select dev.KinderNumber
         , dev.Client
         , dev.MFG
         , dev.FAMILY
         , dev.Intro_Date
         , dev.FIXTURE
         , dev.Dimensions_HWD
         , dev.MFG_Price
         , dev.Item_Number
         , dp.picname 
      from Development as dev
      left join DevelopmentPictures as dp on dev.KinderNumber = dp.KinderNum
     where dp.Revision_Letter = (select max(Revision_Letter) 
                                   from DevelopmentPictures 
                                  where DevelopmentPictures.KinderNum = dp.KinderNum)
    Paul Young
    (Knowledge is power! Get some!)

Posting Permissions

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