displaying additional data that correlates to my query

[Guest]

I have a query that gets the following information from my tables

ProductName, Voltage, Current, Peak, TargetDifference
(Peak and target are the same unit)

I have created an additional query to retrieve the Peak that is closest to
the Target for each ProductName. The problem is that it is important to
display the corresponding Voltage and Current readings. When I try to set
this up I get the following error. "You tried to execute a query that does
not include the specified expresson 'Peak' as part of an aggregate function"



This is what I have so far that works the way I want it to.

SELECT [Flux Density Query].ProductName,MIN([Flux Density
Query].TargetDifference)
FROM [Flux Density Query]
GROUP BY ProductName;

This is the result from this.

ProductName TargetDifference
XXXX .01
YYYY .014


I would like it to look like this.
ProductName Voltage Current Peak TargetDifference
XXXX 2000 2000 2.735 .035
YYYY 3000 3000 1.356 .006
etc.

 

[David Lloyd]

Margo:

One approach would be to take the result of your GROUP BY query and do an
inner join on the [Flux Density Query] on the ProductName and
TargetDifference fields, and then select all the fields that you want in
your result set. There is the possibility of duplicates if two records for
the same ProductName have the same minimum TargetDifference. You will have
to decide whether this is an issue and how to handle it.

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I have a query that gets the following information from my tables

ProductName, Voltage, Current, Peak, TargetDifference
(Peak and target are the same unit)

I have created an additional query to retrieve the Peak that is closest to
the Target for each ProductName. The problem is that it is important to
display the corresponding Voltage and Current readings. When I try to set
this up I get the following error. "You tried to execute a query that does
not include the specified expresson 'Peak' as part of an aggregate function"



This is what I have so far that works the way I want it to.

SELECT [Flux Density Query].ProductName,MIN([Flux Density
Query].TargetDifference)
FROM [Flux Density Query]
GROUP BY ProductName;

This is the result from this.

ProductName TargetDifference
XXXX .01
YYYY .014


I would like it to look like this.
ProductName Voltage Current Peak TargetDifference
XXXX 2000 2000 2.735 .035
YYYY 3000 3000 1.356 .006
etc.