problem with correlated subquery please help

[Guest]

I have 3 tables :

tbl1: ID,...
tbl2: ID, ID2
tbl3: ID2, value

1 links to 2 links to 3

When i use a select query i get the folowing result

ID ID2 value

1 1 2
1 2 1
1 3 7
2 1 3
3 1 4
3 2 6

now i want to use a subquery to select the ID with the ID2 where the value
is the lowest so something like this :

ID ID2

1 2
2 1
3 1

How can i do this ??

 

[Guest]

You could use DMin to find the lowest value, but that would also result in a
very slow query: using the database functions in a query, especially as
criteria, can lead to very poor performance.

Another (hopefully better) approach: make a totals query. Base it on your
existing query that combines the 3 tables, and group by ID, show the Min of
Value. Then create the final query that links this totals query with your
existing query. Link the ID fields together and use Value=[MinOfValue] as
the criteria. I think this should work and run fairly quickly for you.

HTH!

 

[Guest]

Thanks,

the totals query solution works just fine, it's still slow but acceptable

You could use DMin to find the lowest value, but that would also result in a
very slow query: using the database functions in a query, especially as
criteria, can lead to very poor performance.

Another (hopefully better) approach: make a totals query. Base it on your
existing query that combines the 3 tables, and group by ID, show the Min of
Value. Then create the final query that links this totals query with your
existing query. Link the ID fields together and use Value=[MinOfValue] as
the criteria. I think this should work and run fairly quickly for you.

HTH!

I have 3 tables :

tbl1: ID,...
tbl2: ID, ID2
tbl3: ID2, value

1 links to 2 links to 3

When i use a select query i get the folowing result

ID ID2 value

1 1 2
1 2 1
1 3 7
2 1 3
3 1 4
3 2 6

now i want to use a subquery to select the ID with the ID2 where the value
is the lowest so something like this :

ID ID2

1 2
2 1
3 1

How can i do this ??