New User: Query based on value between two fields

[david1-b]

Hello,

I am a new access user using access 2010. I have two tables, an example of their structure is in the attached PDF, and in text below. I want to be able to join/ answer question between the tables like the one outlined below.

Reference Table: A table with the most likely pet based on income and location


Header: Country, State, Income Min, Income Max, Pet
Record: US , NY , 1 , 5 , Cat
Record: US , NY , 5 , 9 , Dog

Question Table: A table with income and location
Header: Country, State, Income
Record: US , NY , 5

What type of pet will the people in the question table have?
Answer: Header: Country, State, Income, Pet
Record: US , NY , 5 , Cat

The logic works like this:
Question Country = Reference Country
Question State = Reference State
Question Income >= Reference Income Min AND Question Income <=Reference Income Max


Obviously I am not working with cats and dogs but this is my table structure.

First I guess I need to know is Access the right tool to answer these questions? It seems to me like it is. Also, if anyone can provide information on query structure and what I will need to do for relations to make it happen that would be great?

Thanks for any advice




Table Example.pdf

[ranman256]

The Question.INCOME is filtered BETWEEN the IncRng.Max and IncRng.min.

[david1-b]

ranman256,

Thank you so much. I searched a ton of examples and they were not answering my questions. You went out of your way to answer it and did a great job. I know it was a simple problem but I really appreciate it.

Thanks
David

[ranman256]

You're welcome.