Access query challenge for you experts

[Mark]

I am relatively new to Access. I can join tables and run queries but I am
stumped on this one. I have a SAP table downloaded called Roles that contain
a range of values for transactions. There is another table with all the
transaction values. The Roles table has a value From and To that can contain
wildcards such as "*". The "F-52,54,57" etc. type transactions are actual
values. So, F-54 to F-57 would be F-54, F-55, F-56, F-57. My dilemma is I
need to decode the From To so that I can get the appropriate values from my
Transaction table. This has to be clever enough to decode the range and
lookup any values that fall within that range. Here is what one role has for
ranges. Any ideas? Thanks

ROLE FROM TO
50000060 0
AL09
50000060 AL11
AL13
50000060 AL16
CKMD
50000060 DB05
DB07
50000060 DB12
F-52
50000060 F-54
F-57
50000060 F-59
F109
50000060 F111
FD00
50000060 FD03
FD31
50000060 FD33
FK00
50000060 FK03
FY1R
50000060 FY1R
F109
50000060 FYLT
HQ*
50000060 HS*
KP05

[Kc-Mass]

I assume the first row is supposed to be
50000060 0 Al09
second is
50000060 AL11 Al13

Yes?

When you have something like FD33 FK00

How do we know how high the FD series goes ?

Kevin C

"Mark" <m.*******@snet.netwrote in message
news:IA****************@newssvr12.news.prodigy.com ...

>I am relatively new to Access. I can join tables and run queries but I am
stumped on this one. I have a SAP table downloaded called Roles that
contain a range of values for transactions. There is another table with all
the transaction values. The Roles table has a value From and To that can
contain wildcards such as "*". The "F-52,54,57" etc. type transactions are
actual values. So, F-54 to F-57 would be F-54, F-55, F-56, F-57. My dilemma
is I need to decode the From To so that I can get the appropriate values
from my Transaction table. This has to be clever enough to decode the range
and lookup any values that fall within that range. Here is what one role
has for ranges. Any ideas? Thanks

ROLE FROM TO
50000060 0 AL09
50000060 AL11 AL13
50000060 AL16 CKMD
50000060 DB05 DB07
50000060 DB12 F-52
50000060 F-54 F-57
50000060 F-59 F109
50000060 F111 FD00
50000060 FD03 FD31
50000060 FD33 FK00
50000060 FK03 FY1R
50000060 FY1R F109
50000060 FYLT HQ*
50000060 HS* KP05

[Mark]

Kevin, you are correct. I don't know how to cut and paste from Excel into
this forum without the spacing getting all goofy. Maybe I should just use
Notepad next time.

Good question. We have to assume and it won't be the case, but assume FD00
to FD99. All transaction values usually start with two alpha characters: AA,
MM, VA, FB or one, such as F- or V-. Should never start with a number. Would
it be easier if I sent a text file with all the values?

Thanks for looking.

Mark

"Kc-Mass" <co********@comcast.netwrote in message
news:ib******************************@comcast.com. ..

>I assume the first row is supposed to be
50000060 0 Al09
second is
50000060 AL11 Al13

Yes?

When you have something like FD33 FK00

How do we know how high the FD series goes ?

Kevin C

"Mark" <m.*******@snet.netwrote in message
news:IA****************@newssvr12.news.prodigy.com ...

>>I am relatively new to Access. I can join tables and run queries but I am
stumped on this one. I have a SAP table downloaded called Roles that
contain a range of values for transactions. There is another table with
all the transaction values. The Roles table has a value From and To that
can contain wildcards such as "*". The "F-52,54,57" etc. type transactions
are actual values. So, F-54 to F-57 would be F-54, F-55, F-56, F-57. My
dilemma is I need to decode the From To so that I can get the appropriate
values from my Transaction table. This has to be clever enough to decode
the range and lookup any values that fall within that range. Here is what
one role has for ranges. Any ideas? Thanks

ROLE FROM TO
50000060 0 AL09
50000060 AL11 AL13
50000060 AL16 CKMD
50000060 DB05 DB07
50000060 DB12 F-52
50000060 F-54 F-57
50000060 F-59 F109
50000060 F111 FD00
50000060 FD03 FD31
50000060 FD33 FK00
50000060 FK03 FY1R
50000060 FY1R F109
50000060 FYLT HQ*
50000060 HS* KP05

[Kc-Mass]

Send the two tables in an MDB file with nothing else.

"Mark" <m.*******@snet.netwrote in message
news:dU****************@newssvr12.news.prodigy.com ...

Kevin, you are correct. I don't know how to cut and paste from Excel into
this forum without the spacing getting all goofy. Maybe I should just use
Notepad next time.

Good question. We have to assume and it won't be the case, but assume FD00
to FD99. All transaction values usually start with two alpha characters:
AA, MM, VA, FB or one, such as F- or V-. Should never start with a number.
Would it be easier if I sent a text file with all the values?

Thanks for looking.

Mark

"Kc-Mass" <co********@comcast.netwrote in message
news:ib******************************@comcast.com. ..

>>I assume the first row is supposed to be
50000060 0 Al09
second is
50000060 AL11 Al13

Yes?

When you have something like FD33 FK00

How do we know how high the FD series goes ?

Kevin C

"Mark" <m.*******@snet.netwrote in message
news:IA****************@newssvr12.news.prodigy.co m...

>>>I am relatively new to Access. I can join tables and run queries but I am
stumped on this one. I have a SAP table downloaded called Roles that
contain a range of values for transactions. There is another table with
all the transaction values. The Roles table has a value From and To that
can contain wildcards such as "*". The "F-52,54,57" etc. type
transactions are actual values. So, F-54 to F-57 would be F-54, F-55,
F-56, F-57. My dilemma is I need to decode the From To so that I can get
the appropriate values from my Transaction table. This has to be clever
enough to decode the range and lookup any values that fall within that
range. Here is what one role has for ranges. Any ideas? Thanks

ROLE FROM TO
50000060 0 AL09
50000060 AL11 AL13
50000060 AL16 CKMD
50000060 DB05 DB07
50000060 DB12 F-52
50000060 F-54 F-57
50000060 F-59 F109
50000060 F111 FD00
50000060 FD03 FD31
50000060 FD33 FK00
50000060 FK03 FY1R
50000060 FY1R F109
50000060 FYLT HQ*
50000060 HS* KP05