Joining Tables Via Ranges / Wildcard

[natecummins]

Access Group,

I've searched around and could not find the solution to my problem
which has been bugging me for quite some time, I apologize if my
questions have been addressed before.

My MS Access knowledge is beginner to intermediate - depending on who
you compare me to I am using MS Access 2003 and all fields in
question are text fields.

Table 1 is named "Range" and contains the fields "Order", "Value Low",
and "Value High"
Table 2 is namd "List" and contains the fields "Value" and "Value
Description"

Sample data could include:

Table "Range"
Order = 1, Value Low = A, Value High = [NULL]
Order = 2, Value Low = A*, Value High = [NULL]
Order = 3, Value Low = A, Value High = C

Table "List"
Value = A1, Value Description = Sample 1
Value = A2, Value Description = Sample 2
Value = B1, Value Description = Sample 3

I have 2 scenarios which are causing me problems, scenario 1 involves
joing the two tables on the value field when it contains a wildcard
(*) character

I thought I could join the tables using the following SQL:

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value
Description]
FROM Range INNER JOIN List
ON Range.[Value Low] LIKE List.Value;

However, this statement returns no results even though there is data
within the tables to provide results. I would expect the [Value Low]
field A* to match table "List" on A1 and A2.

The second scenario is extracting data from the "List" table between
the [Value Low] and [Value High] fields in the "Range" table. I am
not sure if this can be done from a query or if I need a VBA
function? Using the sample data, if [Value Low] = A and [Value High]
= C, I would like to return the [Value Descriptions] for everything A
through C from the "List" table.

I thank you in advance for any help.

 

[Stefan Hoffmann]

hi Nate,

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value
Description]
FROM Range INNER JOIN List
ON Range.[Value Low] LIKE List.Value;

If its possible, than it must be:

ON List.Value LIKE Range.[Value Low]

as the wildcard string must be on the right side of LIKE.


mfG
--> stefan <--

 

[TDCumm16]

Thanks Stefan and Kingston. Changing the order of the tables in the
join statement solved my problem with the * character.

Regarding the query results for the range - I would like to return the
[Value Description] for all values between [Value Low] and [Value
High]. I am basically at step 1 for this query as I am not sure how
to attack the problem.

In table "Range" I have the data:
Order = 3, Value Low = A, Value High = C

In table "List" I have the data:
Value = A, Value Description = Sample 1
Value = B, Value Description = Sample 2
Value = C, Value Description = Sample 3

By utilizing the "List" table, I would like to return the "Value" and
"Value Description" fields for all data within the [Value Low] and
[Value High] fields for Order 3. The results would appear as:

Order = 3, Value = A, Value Description = Sample 1
Order = 3, Value = B, Value Description = Sample 2
Order = 3, Value = C, Value Description = Sample 3

I am not sure how I would use the BETWEEN expression? Can I join the
"Range" and "List" table using 2 different fields ([Value Low] and
[Value High]) and the BETWEEN expression?

Thanks for your help

Try modifying your use of LIKE (since the wildcard is in the field [Value Low]
):

..ON List.Value LIKE Range.[Value Low];

What exactly do you want the result of the second query to be? To filter
within a range, you'd use a criteria like:

Between Range.[Value Low] and Range.[Value High]

Access Group,

I've searched around and could not find the solution to my problem
which has been bugging me for quite some time, I apologize if my
questions have been addressed before.

My MS Access knowledge is beginner to intermediate - depending on who
you compare me to I am using MS Access 2003 and all fields in
question are text fields.

Table 1 is named "Range" and contains the fields "Order", "Value Low",
and "Value High"
Table 2 is namd "List" and contains the fields "Value" and "Value
Description"

Sample data could include:

Table "Range"
Order = 1, Value Low = A, Value High = [NULL]
Order = 2, Value Low = A*, Value High = [NULL]
Order = 3, Value Low = A, Value High = C

Table "List"
Value = A1, Value Description = Sample 1
Value = A2, Value Description = Sample 2
Value = B1, Value Description = Sample 3

I have 2 scenarios which are causing me problems, scenario 1 involves
joing the two tables on the value field when it contains a wildcard
(*) character

I thought I could join the tables using the following SQL:

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value
Description]
FROM Range INNER JOIN List
ON Range.[Value Low] LIKE List.Value;

However, this statement returns no results even though there is data
within the tables to provide results. I would expect the [Value Low]
field A* to match table "List" on A1 and A2.

The second scenario is extracting data from the "List" table between
the [Value Low] and [Value High] fields in the "Range" table. I am
not sure if this can be done from a query or if I need a VBA
function? Using the sample data, if [Value Low] = A and [Value High]
= C, I would like to return the [Value Descriptions] for everything A
through C from the "List" table.

I thank you in advance for any help.

 

[Pat Hartman \(MVP\)]

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value
Description]
FROM Range INNER JOIN List
ON List.Value Between Range.[Value Low] AND Range.[Value High];

BUT - to make this work, you will need to set the High value to the real
high value not null AND your ranges CANNOT overlap. If the ranges overlap,
the join will return multiple rows. If there are gaps in the ranges, no
rows will be returned for that value.

 

[krissco]

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value

Description]
FROM Range INNER JOIN List
ON List.Value Between Range.[Value Low] AND Range.[Value High];

I can't tell you much I dislike MS's join syntax - how goofy.

Aah, good old fashioned SQL:
FROM Range, List
Where List.Value Between Range.[Value Low] AND Range.[Value High];

Pat has a good point - watch your Nulls. Depending on what you want to
do with them, you can wrap [Value High] in the NZ function to replace
them.

Replace w/ "High" value:
NZ([Value High], "ZZZZZ")

Replace w/ starting value:
NZ([Value High], [Value Low])

Something else to consider: Watch out for reserved words - they will
haunt your dreams and newsgroup postings! It seems like Range, List,
Order, and Value are all poor choices for table/field names. If you
are not in too deep (several hundred queries, tons of code, forms, and
reports already existing) then consider renaming your tables/fields
while it is convenient.

-Kris

 

[TDCumm16]

Pat and Kris - thank you for the input, I think I am getting close to
solving this.

Kris - thanks for the suggestion with the NZ function, and thankfully
my table and field names are for illustrative purposes only and not
the actual tables/fields I am working with.

I am trying to utilize the statement recommended by Pat:

SELECT Range.Order, List.Value, List.[Value Description]
FROM Range INNER JOIN List
ON List.Value Between Range.[Value Low] AND Range.[Value High];

With the tables containing the below data only:

In table "Range":
Order = 3, Value Low = A, Value High = C

In table "List":
Value = A, Value Description = Sample 1
Value = B, Value Description = Sample 2
Value = C, Value Description = Sample 3

When trying to execute the query, I receive the following message ~
Between operator without And in query expression 'List.Value Between
Range.[Value Low]'.

I have tried to modifiy the statement and put "Range.[Value Low] AND
Range.[Value High]" in ( ), but the error message remained.

Any further input would be greatly appreciated. Thanks.

SELECT Range.Order, Range.[Value Low], List.Value, List.[Value
Description]
FROM Range INNER JOIN List
ON List.Value Between Range.[Value Low] AND Range.[Value High];

I can't tell you much I dislike MS's join syntax - how goofy.

Aah, good old fashioned SQL:
FROM Range, List
Where List.Value Between Range.[Value Low] AND Range.[Value High];

Pat has a good point - watch your Nulls. Depending on what you want to
do with them, you can wrap [Value High] in the NZ function to replace
them.

Replace w/ "High" value:
NZ([Value High], "ZZZZZ")

Replace w/ starting value:
NZ([Value High], [Value Low])

Something else to consider: Watch out for reserved words - they will
haunt your dreams and newsgroup postings! It seems like Range, List,
Order, and Value are all poor choices for table/field names. If you
are not in too deep (several hundred queries, tons of code, forms, and
reports already existing) then consider renaming your tables/fields
while it is convenient.

-Kris

 

[krissco]

Kris - thanks for the suggestion with the NZ function, and thankfully

my table and field names are for illustrative purposes only and not
the actual tables/fields I am working with.

You're welcome. Good thing about the field names - that would drive me
crazy (and has driven me crazy before I learned better).

SELECT Range.Order, List.Value, List.[Value Description]
FROM Range INNER JOIN List
ON List.Value Between Range.[Value Low] AND Range.[Value High];

I have never seen this syntax. Use standard SQL syntax instead (get
rid of all that 'inner join' crap).

SELECT Range.Order, List.Value, List.[Value Description]
FROM Range, List
WHERE List.Value Between Range.[Value Low] AND Range.[Value High];


-Kris