doubt with the "MAX"

[fabiobarreto10]

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.

 

[llkhoutx]

You have to poppulate a parent table before you can populate a child table. Are table tblStep's ancestors populate, especially the linked fields? Probably not.

 

[fabiobarreto10]

Thanks for responding. Yes, of course I have to populate a parent table first. What does this have to query
I'm trying to do?

Thank you.

 

[llkhoutx]

All the predecessors of tblStep have to be populated on the linked field, othewise your tblStep will have an orphan record(s). Check tblStep to see if ProductID for all tblStep records are populated. Furthermore, all linked fields must be populated.

In other words, I think that your tables are not properly linked on the various primary & secdondary keys. That is, all primary and secondary keys are not appropriatey popuilated. Look at the data in each table to see what's populated and what's not populated.

 

[fabiobarreto10]

Sorry, all the tables are filled correctly and the links are correct as well. Just wanted to know
how do the query.
Thank you.

 

[llkhoutx]

Here's the SQL for your query:
SELECT tblAnalyst.nameofanalyst, tblCompany.nameofcompany, tblProduct.nameofproduct, tblStep.Date2, Max(tblStep.StepID) AS MaxOfStepID
FROM ((tblAnalyst INNER JOIN tblCompany ON tblAnalyst.AnalystID = tblCompany.AnalystID) INNER JOIN tblProduct ON tblCompany.CompanyID = tblProduct.CompanyID) INNER JOIN tblStep ON tblProduct.tblProduct = tblStep.ProductID
GROUP BY tblAnalyst.nameofanalyst, tblCompany.nameofcompany, tblProduct.nameofproduct, tblStep.Date2
HAVING (((tblStep.Date2)=[Forms]![FrmName]![date]));

Create a query, change to SQL view and past the above. Then chnage to the desin view and it will be more readable.

Note that your date parameter is nonsense. If a value from a form. Note the Sigma in thre icon bar and the "Max".

The foregoing should give you a running start.

I apologize for my confusion.

 

[fabiobarreto10]

Thanks for SQL. I did exactly as you gave me. But, still returning two values ​​for each product, I need
only one. Just made ​​a small change in SQL.


SELECT tblAnalyst.nameofanalyst, tblCompany.nameofcompany, tblProduct.nameofproduct, tblStep.Date2, Max(tblStep.StepID) AS MaxOfStepID
FROM ((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
HAVING (((tblStep.Date2) Is Not Null));

 

[recyan]

Just check out if below gives some guidelines :

SELECT 
	qryMaxProductStepIDDate2B.qryMaxProductStepIDA.ProductID, 
	qryMaxProductStepIDDate2B.MaxOfStepID, 
	qryMaxProductStepIDDate2B.Date2, 
	tblProduct.ProductID, 
	tblProduct.NameOfProduct, 
	tblProduct.CompanyID, 
	tblCompany.CompanyID, 
	tblCompany.NameOfCompany, 
	tblCompany.AnalystID, 
	tblAnalyst.AnalystID, 
	tblAnalyst.NameOfAnalyst
FROM 
	(
		(
			
			(
				SELECT 
					qryMaxProductStepIDA.ProductID, 
					qryMaxProductStepIDA.MaxOfStepID, 
					tblStep.ProductID, 
					tblStep.StepID, 
					tblStep.Date2
				FROM 
					(
						SELECT 
							tblStep.ProductID, 
							Max(tblStep.StepID) AS MaxOfStepID
						FROM 
							tblStep
						GROUP BY 
							tblStep.ProductID
					) 
					AS qryMaxProductStepIDA 
					INNER JOIN 
					tblStep 
					ON 
					(qryMaxProductStepIDA.ProductID = tblStep.ProductID) 
					AND 
					(qryMaxProductStepIDA.MaxOfStepID = tblStep.StepID)
			) 
			AS qryMaxProductStepIDDate2B 
			INNER JOIN 
			tblProduct 
			ON 
			qryMaxProductStepIDDate2B.qryMaxProductStepIDA.ProductID = tblProduct.ProductID
		) 
		INNER JOIN 
		tblCompany 
		ON 
		tblProduct.CompanyID = tblCompany.CompanyID
	) 
	INNER JOIN 
	tblAnalyst 
	ON 
	tblCompany.AnalystID = tblAnalyst.AnalystID;

Thanks

 

[fabiobarreto10]

Very, very, very thanks. I was trying to solve this problem is more than a week. Worked perfectly. Again, thank you.