Query between two selected records

[jinu4ever]

Hi Experts.

I have a query question..can't seem to figure this out....:banghead:

I have two tables A and B.. In Table A, I would like to trim out rows not in between Min and Max numbers in Table B. The resulting Table should be Table C. Could anyone tell me what the SQL or how to design the query in Access?


Table A
Client____Product____Number
100________X________1
100________X________2
100________X________3
100________X________4
100________X________5
100________Y________1
100________Y________2
100________Y________3
100________Y________4
100________Y________5

Table B
Client_____Product___Min Number___Max Number
100________X__________2____________4
100________Y__________3____________4
200________X__________1____________2

Table C
Client____Product____Number
100________X________2
100________X________3
100________X________4
100________Y________3
100________Y________4

 

[recyan]

Till someone comes along, just check out if below gives some guidelines :

SELECT 
	Table2.CLIENT, 
	Table2.PRODUCT, 
	Table2.MIN_NUMBER, 
	Table2.MAX_NUMBER, 
	Table1.Client, 
	Table1.Product, 
	Table1.Number, 
	IIf([Number] Between [MIN_NUMBER] And [MAX_NUMBER],[Number],0) AS ValidNumber
FROM 
	Table2 
	LEFT JOIN 
	Table1 
	ON 
	(Table2.PRODUCT = Table1.Product) 
	AND 
	(Table2.CLIENT = Table1.Client);

Thanks

 

[jinu4ever]

Wow, yes, this gives me a perfect starting point as to what i want to achieve.

Many thanks.
Andrew

 

[recyan]

Glad you found it helpful.

Thanks