Min/max from secondary table without returning extra rows from primary (1 Viewer)

[DocNice]

Hi all, don't know if this is possible, but right now I'm running a query and then performing Dmin/Dmax on each record in the recordset and it's running SLOOOOW. Perhaps if I can get it from a query it would be faster.

I have Table-A and Table-B with a one to many relationship. In the Table-A I preferably need to return only one row per record. From Table-B, I need to return the minimum and maximum record that corresponds to the ID in Table-A.

I am familiar with min and max, but it the join methods I've been using either return multiple rows per ID or none. Any way around this?

 

[Vassago]

Create one query for the max of each record on table b. Create a second query with a min of each record on table b. Then create a third query that links table a to the two queries.

For example:

table1 has fields coid, coname
table2 has fields coid, orderdate

query1:

SELECT Table1.coid, Max(Table2.orderdate) AS maxorderdate
FROM Table1 INNER JOIN Table2 ON Table1.coid = Table2.coid
GROUP BY Table1.coid;

query2:

SELECT Table1.coid, Min(Table2.orderdate) AS minorderdate
FROM Table1 INNER JOIN Table2 ON Table1.coid = Table2.coid
GROUP BY Table1.coid;

query3:

SELECT Table1.coid, Query1.maxorderdate, Query2.minorderdate
FROM Table1 INNER JOIN Query1 ON Table1.coid = Query1.coid INNER JOIN Query2 ON Table1.coid = Query2.coid

 

[DocNice]

I love you guys

Gonna have to take y'all out for a beer sometime.