relation on a minimal value

[Pierre]

Hi all,

I have the tables linked on code
I want a query that would display
code, real size, nearest theorical size, id

tbl1 ----------- tbl2 ------------------------
code real size code theorical Size ID
A 15 A 1 1000
B 20 A 20 1010
C 4 A 50 5015
B 1
7001
B 5
7058
B 10
8888
C 1
5489
C 5
7895
C 10
8459

the result should be
A,15,20,1010
B,20,10,8888
C,4,5,7895
I try the use of query with min and expression but i always end with error
message
Actually i am doing that with functions.
I want to do that with a query
Can someone help me?

Best regards

 

[Gary Walter]

I have the tables linked on code
I want a query that would display
code, real size, nearest theorical size, id

tbl1 ----------- tbl2 ------------------------
code real size code theorical Size ID
A 15 A 1 1000
B 20 A 20 1010
C 4 A 50 5015
B 1
7001
B 5
7058
B 10
8888
C 1
5489
C 5
7895
C 10 8459

the result should be
A,15,20,1010
B,20,10,8888
C,4,5,7895
I try the use of query with min and expression but i always end with error
message
Actually i am doing that with functions.
I want to do that with a query

Hi Pierre,

Here might be one way:

qPreLim:

SELECT
t1.code,
t1.[real size],
Abs([t1].[real size]-[t2].[theoretical Size]) AS AbsDiff,
t2.[theoretical Size],
t2.ID
FROM tbl1 AS t1
INNER JOIN tbl2 AS t2
ON t1.code = t2.code;

results from sample data:
code real size AbsDiff theoretical Size ID
A 15 14 1 1000
A 15 5 20 1010
A 15 35 50 5015
B 20 19 1 7001
B 20 15 5 7058
B 20 10 10 8888
C 4 3 1 5489
C 4 1 5 7895
C 4 6 10 8459


qMin:

SELECT
qPreLim.code,
qPreLim.[real size],
Min(qPreLim.AbsDiff) AS MinOfAbsDiff
FROM qPreLim
GROUP BY
qPreLim.code, qPreLim.[real size];

results from sample data:
code real size MinOfAbsDiff
A 15 5
B 20 10
C 4 1



qFinal:

SELECT
qMin.code,
qMin.[real size],
qPreLim.[theoretical Size],
qPreLim.ID
FROM
qMin
INNER JOIN
qPreLim
ON
(qMin.MinOfAbsDiff = qPreLim.AbsDiff)
AND
(qMin.[real size] = qPreLim.[real size])
AND
(qMin.code = qPreLim.code);

results from sample data:
code real size theoretical Size ID
A 15 20 1010
B 20 10 8888
C 4 5 7895


One might be able to do this in one
query, but I think the fact that you
have fieldnames that will need brackets
around them will interfere with using
subqueries in your FROM clause.

Of course, I could be wrong.

good luck,

gary