Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts

    Access 2007 - doubt with the "MAX"

    friends,

    I am sending herewith the relationship of four tables. I'm trying to do a query for records with the
    maximum value of "StepID" of each product, and also with the field "Date2" value. That is, a single record for each product.
    If the "Date2" empty returns no records. I thought it was just putting the words "MAX" as
    criterion, but did not work. How do I fix?

    Thanks for helping.
    Attached Images Attached Images

  2. #2
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Hi Fabio,

    If you want the Maximum Value of StepID for each product, you should have a totals query, grouped by ProductID. You will also need to include all the other select clause fields in the group by clause, except date2.

  3. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-10)

  4. #3
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Hi Rui, thanks for answering.

    Sorry, not clear to me. As image, I added the productID field in the query, and clicked
    total button. Is that it? How do I go from here?

    I think I understand the ORDER BY. After the query completes, so I put the sql and add before ";"
    ORDER BY NameOfAnalyst, NameOfCompany, NameOfProduct, StepID, NameOfStep;

    ORDER BY is correct?

    Thanks for helping.

    Fabio.
    Attached Images Attached Images

  5. #4
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Seems you did get the group by (agrupar por in Portuguese). I would change Date2 to have Where, instead of Group By (not sure what is used for WHere in the Access Portuguese version).
    Once you do that, do you get the results you were expecting?

  6. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-11)

  7. #5
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, as you said before that I need a totals query just clicked on the "total". But the query
    always returns all records. I did some variations by putting "MAX" and also in the ProductID StepID, but did not work.

    the sql looks like this:

    SELECT tblAnalyst.NameOfAnalyst, tblCompany.NameOfCompany, tblProduct.NameOfProduct, tblStep.Date2, Max(tblStep.StepID) AS MáxDeStepID, tblStep.NameOfStep, tblProduct.ProductID
    FROM tblAnalyst AS tblAnalyst_1, tblCompany AS tblCompany_1, tblProduct AS tblProduct_1, tblStep AS tblStep_1, ((tblAnalyst INNER JOIN tblCompany ON tblAnalyst.AnalystID = tblCompany.AnalystID) INNER JOIN tblProduct ON tblCompany.CompanyID = tblProduct.CompanyID) INNER JOIN tblStep ON tblProduct.ProductID = tblStep.ProductID
    GROUP BY tblAnalyst.NameOfAnalyst, tblCompany.NameOfCompany, tblProduct.NameOfProduct, tblStep.Date2, tblStep.NameOfStep, tblProduct.ProductID
    HAVING (((tblStep.Date2) Is Not Null));


    You meant that I have put "WHERE" Instead of HAVING? I tried, but appeared a syntax error.

  8. #6
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Ok Fabio, I need to know a bit more of your problem, before I can offer a full solution.

    You want the maximum value of StepID per ProductID. That is given by this:
    Code:
    SELECT tblProduct.ProductID, tblProduct.NameOfProduct,Max(tblStep.StepID) AS MáxDeStepID 
    FROM  tblProduct INNER JOIN tblStep ON tblProduct.ProductID = tblStep.ProductID
    WHERE (((tblStep.Date2) Is Not Null))
    GROUP BY tblProduct.ProductID, tblProduct.NameOfProduct
    


    Question now is, what is the role of the other fields that you wanted on this query? I suppose company is the company that produced this product? And analyst? And the date?

    I need to know this, to know how to "force" them to have values that make sense.

  9. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-11)

  10. #7
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    SELECT TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, TblProduto.NomeDoProduto
    FROM ((TblAnalista INNER JOIN TblEmpresa ON TblAnalista.CodAnalista = TblEmpresa.CodAnalista) INNER JOIN TblProduto ON TblEmpresa.CodEmpresa = TblProduto.CodEmpresa) INNER JOIN TblPassosStatus ON TblProduto.CodProduto = TblPassosStatus.CodProduto
    GROUP BY TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, TblProduto.NomeDoProduto, TblProduto.[Ativado], TblPassosStatus.DataRealizada, TblPassosStatus.CodProduto
    HAVING (((TblProduto.[Ativado])=-1) AND ((TblPassosStatus.DataRealizada) Is Not Null));

  11. #8
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Try this one, Fabio

    Code:
    SELECT TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, P.NomeDoProduto FROM ((TblAnalista INNER JOIN TblEmpresa ON TblAnalista.CodAnalista = TblEmpresa.CodAnalista) INNER JOIN TblProduto P ON TblEmpresa.CodEmpresa = P.CodEmpresa) INNER JOIN TblPassosStatus ON P.CodProduto = TblPassosStatus.CodProduto WHERE (P.[Ativado]=-1) AND (TblPassosStatus.CodPassos IN (SELECT TOP 1 CodPassos FROM TblPassosStatus S WHERE ( (S.CodProduto=P.CodProduto) AND (S.DataRealizada Is Not Null) ) Order By S.CodPassos DESC));
    Last edited by ruirib; 2012-07-11 at 13:22. Reason: minor correction

  12. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-11)

  13. #9
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, returned records where the value of the field Date performed the last step is null.

  14. #10
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    I don't see how that can happen. Could you zip and pm a copy with the database, just with the necessary records to show what is happening?

  15. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-11)

  16. #11
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, bd in annex.

  17. #12
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Have you pmed it?

  18. #13
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    oops, The name of the consultation and 0Query.
    Attached Files Attached Files

  19. #14
    Administrator
    Join Date
    Jun 2010
    Location
    Portugal
    Posts
    10,410
    Thanks
    131
    Thanked 1,167 Times in 1,071 Posts
    Ok, with this code:
    Code:
    SELECT TblAnalista.NomeDoAnalista, TblEmpresa.NomeDaEmpresa, P.NomeDoProduto, TblPassosStatus.DataRealizada
    FROM ((TblAnalista INNER JOIN TblEmpresa ON TblAnalista.CodAnalista = TblEmpresa.CodAnalista) INNER JOIN TblProduto P ON TblEmpresa.CodEmpresa = P.CodEmpresa) INNER JOIN TblPassosStatus ON P.CodProduto = TblPassosStatus.CodProduto
    WHERE (P.[Ativado]=-1)  AND 
    (TblPassosStatus.CodPassosStatus IN (SELECT TOP 1 CodPassosStatus FROM TblPassosStatus S WHERE ( (S.CodProduto=P.CodProduto) AND (S.DataRealizada Is Not Null) ) 
    Order By S.CodPassosStatus DESC));
    I get no null dates, none at all?

    It's the same code, with a field name corrected and the DataRealizada field added to the SELECT clause.

  20. The Following User Says Thank You to ruirib For This Useful Post:

    fabiobarreto10 (2012-07-11)

  21. #15
    2 Star Lounger
    Join Date
    Jan 2012
    Posts
    155
    Thanks
    118
    Thanked 0 Times in 0 Posts
    Rui, continues with records with the date performed zero. Test with a bd that I have sent. Click on the button "adviser", in the first combo choice Fabio, in the second IBM, then click monitoring and finally choose the product aTestecom8. This product is part of the consultation, but the field datarealizada the last step is null. Thank you.

Page 1 of 2 12 LastLast

Posting Permissions

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