Set value in query based on data in unrelated table

[dtay1132]

It has been a while since I did any serious work with queries and then of course my machine was upgraded to MS Access 2007 so there is probably some newer way to do what I need to accomplish.

I have two tables, and need to use the value in Table A to return a value from table B to a query that has other information from Table A.
In table A there is a field with a text string called Code. It is a 5 digit text field (text because the leading character can be alpha or numeric). There are other fields in this table, but Code is the one that needs to be used to pull the value from Table B.

Table B has 3 fields in it StartCode, EndCode and Category. There are 10 records in this table that have different categories for each of the StartCode / EndCode ranges. I need to use the value in TableA.Code to check whether it falls within the range of TableB.StartCode and TableB.EndCode to return TableB.Category to my query.

How can I return the Category from Table B to a query that has the data from Table A in it?

Appreciate the help.

 

[dhookom]

The solution depends on whether you need the query to be updateable or is read-only ok.

Duane
Hook'D on Access
MS Access MVP

 

[dtay1132]

Read only should be ok. I need to export the results to excel to do some pivot stuff that is beyond what I can do in access.

 

[dhookom]

Try something like:

SELECT TableA.*, TableB.Category
FROM TableA, TableB
WHERE TableA.Code BETWEEN TableB.StartCode and TableB.EndCode

Duane
Hook'D on Access
MS Access MVP