Query criteria using table1 range to extract table2 data

[emmorel]

Hi, hoping someone can help.
I'm trying to create a list of codes with their description using two Access tables. Table 1 contains various code ranges From: 10021 To: 10646 and From: 20240 To:20550 and so forth. Table 2 contains all the codes from 10000 to 99999 with their description. I want the syntax for the query criteria (not the Vba code) that will extract a list of the codes in the range with their description using the two tables.
Thanks.
emmorel

 

[pbaldy]

That could work if the range table only had one record in it (and you wouldn't want the join). If you only want the records from one of the ranges, how is it supposed to know which range? Or am I misunderstanding? Can you describe exactly what the desired result would look like?

 

[theDBguy]

Hi. I wonder if this is what you wanted...

SELECT HCPCS, CODE, DESCRIPTION
FROM RVU19_Import
INNER JOIN [tbl_Code Range Table]
ON RVU19_Import.CODE BETWEEN [tbl_Code Range Table].[From Code]
AND [tbl_Code Range Table].[To Code]

 

[emmorel]

PBaldy,
Thanks for your reply; this is the outcome I'm looking for based on the first range that's in Table1.

HCPCS
DESCRIPTION10021Doc lack perm+cont+parox fib1002FAssess anginal symptom/level10030Guide cathet fluid drainage10035Perq dev soft tiss 1st imag10036Perq dev soft tiss add imag1003FLevel of activity assess10040Acne surgery1004FClin symp vol ovrld assess1005FAsthma symptoms evaluate10060Drainage of skin abscess

theDBguy,
Thanks. Your code appears to be what I'm looking for from the little Vba I know, how do I translate this into a Query Criteria?
Thanks.
emmorel

 

[theDBguy]

Hi. Assuming I got the names of your tables and fields correctly, try copying the code I posted into the SQL View of a new query and run it.

Actually, let's try this one:

SELECT [From Code], [To Code], HCPCS, CODE, DESCRIPTION 
FROM RVU19_Import 
INNER JOIN [tbl_Code Range Table] 
ON RVU19_Import.CODE 
BETWEEN [tbl_Code Range Table].[From Code] 
AND [tbl_Code Range Table].[To Code]

 

[emmorel]

theDBguy, Thanks.
I pasted your Vba code in a new query and it gave me the following error message I haven't been able to fix.
"Between operator without And in query expression 'RVU10_Import.CODE Between [tbl_Code Range Table].[From Code]

 

[theDBguy]

Hi. Can you post what you actually have? There is a AND in the code I posted.

 

[emmorel]

Here's a print screen!

 

[theDBguy]

Hi. Looks like the query interpreter thinks the end of the statement is sooner than it actually is. If you copied and pasted the code from the website, try retyping the whole thing instead.

 

[isladogs]

Try this amended version of DBG's query

SELECT [From Code], [To Code], HCPCS,

, DESCRIPTION 
FROM RVU19_Import 
INNER JOIN [tbl_Code Range Table] 
ON RVU19_Import.[CODE]=[tbl_Code Range Table].[From Code]
WHERE [RVU19_Import].[CODE]
BETWEEN [tbl_Code Range Table].[From Code] 
AND [tbl_Code Range Table].[To Code]

The ON line was incomplete previously.
I've enclosed CODE in [] as its probably a reserved word in Access.
Its also a problem using it in a forum post!

You would find life much easier if you avoided spaces in file names.
Personally I'd get rid of underscores as well but that's less important

 

[theDBguy]

Incomplete? Are you sure? Oh well...

 

[isladogs]

Oops I'm wrong.
It was so hard typing this on a tablet due to the CODE field that I made a mistake.
That's my excuse anyway...

I think it needs a cartesian join like this

SELECT [From Code], [To Code], HCPCS,

, DESCRIPTION 
FROM RVU19_Import, [tbl_Code Range Table] 
WHERE [RVU19_Import].[CODE]
BETWEEN [tbl_Code Range Table].[From Code] 
AND [tbl_Code Range Table].[To Code]

 

[theDBguy]

theDBguy, Thanks.
I pasted your Vba code in a new query and it gave me the following error message I haven't been able to fix.
"Between operator without And in query expression 'RVU10_Import.CODE Between [tbl_Code Range Table].[From Code]

Okay, it seems Access doesn't like the syntax I posted unless we tell it how to properly interpret it. So, try it this way...

SELECT [From Code], [To Code], HCPCS, CODE, DESCRIPTION  
FROM RVU19_Import  
INNER JOIN [tbl_Code Range Table]  
ON (RVU19_Import.CODE  
BETWEEN [tbl_Code Range Table].[From Code]  
AND [tbl_Code Range Table].[To Code])

PS. By the way, Colin made a good point that the above query can only be viewed using the SQL view in the query designer because a non-equi join cannot be graphically represented in the query designer. Cheers!

 

[emmorel]

theDBguy,
I tried your last code and got a different error. "Syntax Error in Join Operation". Thanks for your all your efforts.

Colin (isladogs),
I tried your revised SQL code and it worked. I am getting descriptions from the RVU19_Import table for the codes within the ranges specified in tbl_Code Range Table. I'm posting an image of the results in the event anyone else can benefit from this. Thank you so much! emmorel

 

[isladogs]

I have to say I'm surprised my revised code works but then it depends on what results you wanted.
As you may have realised, DBG and I discussed this by email
His only works with the () brackets after ON and at the end as I found out when I omitted them.

Here is his other version which works without the brackets

SELECT [From Code], [To Code], HCPCS, CODE, DESCRIPTION  
FROM RVU19_Import  
INNER JOIN [tbl_Code Range Table]  
ON RVU19_Import.CODE  
>= [tbl_Code Range Table].[From Code]  
AND RVU19_Import.CODE <= [tbl_Code Range Table].[To Code])

Looking at the screenshot you will notice that you have one record for each combination of records in both tables allowing for the filtering of dates. That is the effect of the Cartesian join.
Are you SURE that you want that?

Do look at DBG's versions again just in case.

 

[theDBguy]

Hi Colin. I could be off but I think that was supposed to be:

SELECT [From Code], [To Code], HCPCS, CODE, DESCRIPTION   
FROM RVU19_Import   
INNER JOIN [tbl_Code Range Table]   
ON RVU19_Import.CODE [COLOR=Red][B]>=[/B][/COLOR] [tbl_Code Range Table].[From Code]   
AND RVU19_Import.CODE [COLOR=red][B]<=[/B][/COLOR] [tbl_Code Range Table].[To Code])

 

[isladogs]

Doh! A bad workman blames the use of a tablet (AGAIN)
I've corrected my original code. Thanks for spotting it.