Using Between or other Logic

[hawg9]

I have one table (Table A) that has a specific id number and then I have another table (Table B) that has multiple records that have ranges of id numbers. (Ex: Value in Table A = 123 Range in Table B = 000 - 120, 125 - 150). Using Access 97, how can I run a query to verify the values in Table A do / do not fall into the ranges on Table B?

 

[SkipVought]

Hi,

It's either they match or they don't

Select whatever 
From TableA A, TableB B
Where A.ID=B.ID

Select whatever 
From TableA A, TableB B
Where A.ID<>B.ID

Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[hawg9]

How can you run a match where you have a range of numbers? Not all of the numbers in the range are spelled out. For example, table b has two fields (beginning number of range and end number of range). Please explain.

 

[PHV]

SELECT A.*, B.*
FROM TableA A, TableB B
WHERE A.ID Between Val(Left(B.Range,3)) AND Val(Right(B.Range,3))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Please post some sample data.

You said ID.

ID's are usually unique values.

???


Skip,

_{[red]Be Advised![/red]
A wee deranged psychic may be runnin' around out there!
SMALL MEDUIM @ LARGE}

 

[PHV]

SELECT A.*, B.*
FROM TableA A, TableB B
WHERE A.ID Between B.[beginning number of range] AND B.[end number of range]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[hawg9]

Table A has a value of 2084510010 and table B has a field (begin number) with a value of 2084510000 and a field (end number) with a value of 2084510100. I have multiple values in Table A along with multiple ranges in table b. I need to see which values in table a do not fall into one of the ranges on table b.

Thanks for your help.

 

[PHV]

I need to see which values in table a do not fall into one of the ranges on table b
SELECT * FROM TableA A
WHERE NOT EXISTS (SELECT * FROM TableB WHERE A.ID BETWEEN [begin number] And [end number])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886