Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query making me barmy (Office XP)

    I have a query that is giving me the ........

    I am trying to query a list of quotes to find the lowest cost item. I can get this to work if I drop the primary key off and then do a grouping query, however, I then need to do other work on the outcome and for that I need the primary key to reference by.

    Here is the SQL of the query without grouping


    SELECT tblItemQuote.ItemQuote, tblItemDetail.Group, tblItemQuote.Job, tblItemQuote.Cost
    FROM tblItemDetail INNER JOIN tblItemQuote ON tblItemDetail.ItemDetailID = tblItemQuote.Item;


    Here it is grouped producing wrong outcome (that is this query lists the same as above)

    SELECT tblItemQuote.ItemQuote, tblItemDetail.Group, tblItemQuote.Job, Min(tblItemQuote.Cost) AS MinOfCost
    FROM tblItemDetail INNER JOIN tblItemQuote ON tblItemDetail.ItemDetailID = tblItemQuote.Item
    GROUP BY tblItemQuote.ItemQuote, tblItemDetail.Group, tblItemQuote.Job;


    Here the query works properly - produces expected outcome, however, I have lost any references making it very difficult to do further work on the query.

    SELECT tblItemDetail.Group, tblItemQuote.Job, Min(tblItemQuote.Cost) AS MinOfCost
    FROM tblItemDetail INNER JOIN tblItemQuote ON tblItemDetail.ItemDetailID = tblItemQuote.Item
    GROUP BY tblItemDetail.Group, tblItemQuote.Job;


    I have considered approaching the query tree in reverse, accumulating the data and then going through and finding the minimum but this does not want to work either. No matter which way I go I seem to be getting hung on the promary keys.

    Help

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query making me barmy (Office XP)

    You should probably create a new query based on the last query in your post (the working one) and on the two tables involved, with tblItemQuote linked to the query on MinOfCost vs Cost.

    If that doesn't work, it would help if you could post a stripped down copy of your database, so that Loungers can investigate the problem directly. See <post#=401925>post 401925</post#> for instructions.

Posting Permissions

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