Help with Query Design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

valglad@mail.com - 27 Apr 2006 01:44 GMT

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

Reply to this Message

Delordson Kallon - 27 Apr 2006 08:48 GMT

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/

> Hi,
>
[quoted text clipped - 27 lines]
>
> Val

Reply to this Message