Problems with getting the rest of the fields when using GROUP BY

[tudoridvoreanu]

I have problems with getting the rest of the record when using a
GROUP BY.

I have this table, Operations:

Id (Autonumber)
CodeI (In-shipment code, number, ascending, the newest input has
a bigger number)
CodeP (Product code, text)
Unit (Measuring Unit, I can have the same product with different
measuring units, e.g.: box / piece; text)
CodeO (Out-shipment code, ascending number or -1 <==> not yet
shipped)
Var (various other fields, reduced to one: text)
StockI (quantity before this operation)
StockE (quantity after shippment or = StockI if not yet shipped)

Example:

Id CodeI CodeP Unit CodeO Var StockI StockE
1 2 a pc -1 xxx 200 200
2 2 a pc 2 xxx 200 0
3 3 a pc -1 xxx 300 300
4 3 a pc 2 xxx 300 200
5 4 a pc -1 xxx 200 200
6 3 b sqm -1 xxx 100 100
8 4 b pc -1 xxx 100 100
9 4 c pc -1 xxx 400 400

My problem: I have to select (given <CodeP, Unit> from the orders
table) the record which has the lowest CodI, but still has stock
(StockE > 0).

In the example above:
<a, pc> should give (4, 3, a, pc, 2, xxx, 300, 200).

But before joining the orders table to the candidates, I have to
select them. I could not find a better solution than working with
4 intermediate querries:

qu1: (selects the candidates from Operations)

SELECT CodeI, CodeP, Unit, MIN ( o.StockE ) AS StockE
FROM Operations AS o
GROUP BY CodeI, CodeP, Unit
HAVING MIN ( StockE ) > 0;


qu2: (gets the rest of informations for the candidates selected
before)

SELECT o.Id, o.CodeI, o.CodeP, o.Unit, o.CodeO, o.StockE,
o.Var
FROM Operations AS o, qu1
WHERE o.CodeI = qu1.CodeI AND
o.CodeP = qu1.CodeP AND
o.Unit = qu1.Unit AND
o.StockE = qu1.StockE;


qu3: (selects the oldest candidate)

SELECT MIN ( qu2.CodeI ) AS CodeI, CodeP, Unit
FROM qu2
GROUP BY CodeP, Unit;


qu4: (again gets the rest of informations)

SELECT qu2.Id, qu2.CodeI, qu2.CodeP, qu2.Unit, qu2.CodeO,
qu2.StockE, qu2.Var
FROM qu2, qu3
WHERE qu2.CodeI = qu3.CodeI AND
qu2.CodeP = qu3.CodeP AND
qu2.Unit = qu3.Unit;

Is there a way to do it all with fewer queries (maybe just one)?

 

[raskew]

Take a look at this article from the Access Web. Hopefully it'll provide some ideas.

Bob

 

[tudoridvoreanu]

Take a look at this article from the Access Web. Hopefully it'll provide some ideas.

Bob

Thanks. It is a good start.
:-( I was there (well, alike) but I could not solve the need of
two Min : Min(CodI with (Min(StockE) > 0)) and carrying the rest
of infos. The closest I came is:

SELECT MIN(o.Id) AS Id
FROM o
WHERE StockE > 0 AND
o.Id IN (SELECT MAX(Id) FROM o
GROUP BY CodeI, CodeP, Unit HAVING MIN(StockE) > 0)
GROUP BY CodeP, Unit;

which is bad:
- the MIN(Id) does not work after an insert query - I cannot be
sure of the order in which the records are added,
- I have now the unique Key (Id) but I still need another query to
get the rest of infos.