select query based on values in other records

[fishysheep]

classic two table db. invoice + multiple order line setup. Linked on invoice ID.

What i need to do is to select 2 rows from the order lines, BUT.... I only need to select the second line if the first row fufills certain conditions.

I've tried a nested IIF but the problem is that I can't specify the odds on the ind row.

thanks

 

[GingerR]

Please list your table structure and some sample data, as well as the output you desire. Thanks!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[fishysheep]

ok, simplest structure

TABLE-1
ID
==
1
2
3

TABLE-2
ID CODE VALUE
==================
1 A 100
1 B 101
1 C 102
2 A 200
2 B 201
2 C 001
3 A 300
3 B 301
3 C 999

query should examine all A Code records and if the VALUE is > 199 then it should examine record C CODE and if the VALUE is > 100 then records A + C should be returns.

So, using above data the query should return:

ID CODE VALUE
==================
3 A 300
3 C 999



ta

 

[GingerR]

Table Name = "Table1"

SELECT Table1.ID, Table1_1.Code, Table1_1.Value
FROM Table1 INNER JOIN Table1 AS Table1_1 ON Table1.ID = Table1_1.ID
WHERE (((Table1.Code)="A") AND ((Table1.Value)>199)) OR (((Table1.Code)="C") AND ((Table1.Value)>100))
GROUP BY Table1.ID, Table1_1.Code, Table1_1.Value
HAVING (((Count(Table1.ID))=2)) OR (((Count(Table1.ID))=2));

So what I did is get records where A>199 or C>100. For ID count = 2 (means that both an A and a C record fit the bill), I get the info for that ID number.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html