Help with Query Design

[valglad]

Hi,

Would appreciate any help with designing a query(most probably queries)
based on the following table:

Product Line Customer Whse Variance Whse Mode Mode Variance
PRD1 6164 -10 One RAIL 30
PRD1 6164 28 One TRUCK -5
PRD1 6164 -20 Two RAIL 10
PRD1 6164 25 Two TRUCK -1


The objective is to produce a record, which first has a maximum value
for warehouse variance (in this example, it's warehouse "One" since it
has Whse Variance of 18 (-10+28)) and then determine which mode that
uses this warehouse has the highest value (in this case its Rail with a
value of 30).

The query output should look something like that:

Product Customer Whse Var Whse Mode Mode Var
PRD1 6164 18 One Rail 30

The query(ies) is(are) run from Excel using DAO and results are
transferred to Excel.


Could anyone show me some guidance with builiding queries here because
I'm sort of lost here.

Thanks

Val

 

[Guest]

You can create this with 2 queries. The first identifies the warehouse with
the highest value for whse Variance:

SELECT TOP 1 tblWarehouses.Whse, Sum(tblWarehouses.WhseVariance) AS
TotalWhseVariance
FROM tblWarehouses
GROUP BY tblWarehouses.Whse;

Assuming you save this with a name of qryTopWhse, you can use the following
second query to get the mode using this warehouse that has the highest mode
variance value:

SELECT TOP 1 tblWarehouses.ProductLine, tblWarehouses.Customer,
qryTopWhse.TotalWhseVariance, tblWarehouses.Whse, tblWarehouses.Mode,
tblWarehouses.ModeVariance
FROM qryTopWhse INNER JOIN tblWarehouses ON qryTopWhse.Whse =
tblWarehouses.Whse
ORDER BY tblWarehouses.ModeVariance DESC;

You will obvously need to repace tabel and field names with the names in
your database.


Delordson Kallon
http://www.instantsoftwaretraining.com/