Store lookup values based on BETWEEN text field name (1 Viewer)

[maw230]

Wow, what a horrible title..

I have this cross reference table that says:

For (text) codes
10021-69900 Use multiplier .15
70010-79999 use .20
0001M-0004M use .50

I want to write a query that can then reference the text codes and apply the correct multiplier. I'd like to do this without creating a separate x-ref record for each Text code.

My thought is to create two fields in the x-ref table - one for beginning text code and one for ending. MY query would then say
If Text_code between Begin and End select multiplier
but in a way that would actually work .

I'm sure this has been asked a million times before and probably even by myself. Let me know if I wasn't very clear - that wouldn't surprise me.

 

[plog]

So your table should look like this:

Multipliers
CodeStart, CodeEnd, Multiplier
10021, 69900, .15
70010, 79999, .2
0001M, 0004M, .5

Then in a query you would have to go into the SQL and modify the JOIN between the above Multipliers table and the other table (let's call it DataTable). Let's say DataTable has a field called Code which you will JOIN it on.

You're JOIN in that SQL would look like this:

INNER JOIN Multipliers ON (DataTable.Code>=Multipliers.CodeStart AND DataTable.Code<=Multipliers.CodeEnd

That links your tables and then you can bring in the Multiplier field in the SELECT clause.

A few warnings:

1. Your sample data isn't all-inclusive. If the Code=70005 then the query will not find a match and the record won't show.

2. Strings compare different than numbers. Your code is text, which means you could accidentally have CodeEnd/CodeStarts that overlap.

CodeStart, CodeEnd
0003M, 0007M
0003A, 0008A

Those two records overlap for all records from 0003M to 0007M. That's the nature of how strings are compared.

 

[maw230]

So your table should look like this:

Multipliers
CodeStart, CodeEnd, Multiplier
10021, 69900, .15
70010, 79999, .2
0001M, 0004M, .5

Then in a query you would have to go into the SQL and modify the JOIN between the above Multipliers table and the other table (let's call it DataTable). Let's say DataTable has a field called Code which you will JOIN it on.

You're JOIN in that SQL would look like this:

INNER JOIN Multipliers ON (DataTable.Code>=Multipliers.CodeStart AND DataTable.Code<=Multipliers.CodeEnd

That links your tables and then you can bring in the Multiplier field in the SELECT clause.

A few warnings:

1. Your sample data isn't all-inclusive. If the Code=70005 then the query will not find a match and the record won't show.

2. Strings compare different than numbers. Your code is text, which means you could accidentally have CodeEnd/CodeStarts that overlap.

CodeStart, CodeEnd
0003M, 0007M
0003A, 0008A

Those two records overlap for all records from 0003M to 0007M. That's the nature of how strings are compared.

Genius! I was just trying to reconcile how I would even create the join.. I was thinking about using a subquery in lieu of a join - that should work as well provided I can figure out the syntax, correct?

I should have no overlap issues in this case, but I do appreciate you mentioning it. In fact, here are my code sets below. If you see any potential issues please let me know!

0001M-0004M
10021-69990
70010-79999
80047-89398
90281-90749
90785-99199
99201-99499
99500-99607

And, there will be no Codes entered that fall outside of this range.

Thanks, again! I will start to build this out and get back to you.

Edit: Seems to be working great. Appreciate your help.