Look up value from one table against a second table to return info

[markwilliams2004]

Hi,

This is probably way easier than what I am making it.

Essentially I have two tables I am trying to query off of.

The first contains transactional data with an account code.

The second table has three columns. The first two columns represent the upper and lower end of a range the account code would fall between. I want to return the third column from the second table to include with the transactional data.

I hope this is clear and appreciate any help.

Thanks

 

[dhookom]

Add both tables to your query and set the criteria under the account code column to:
Between [upper] and [lower]

Duane
Hook'D on Access
MS Access MVP

 

[markwilliams2004]

I understand what you are saying but the tables are like this:

Table 1 (transactional data):

Account Description Amount
5014 Example data $100

Table 2 (Lookup info)
Lower Acc Upper Acct Acct Class
1000 2000 Asset
5000 6000 Revenue

Query results
Account Account Class Description Amount
5014 Revenue Example data $100

So in the query, I am looking to bring the account class essentially into the transactional information. The problem is that the table 2 info does not currently have the information in one column which I think the Upper and Lower bound suggestion would need to work.

Any suggestions? Iam open to any suggestions on how to structure this if this cannot be done this way.

Thank you.

 

[markwilliams2004]

I think I actually figured it out. This is what I came up with thank you for your response.

SELECT TBMY2007.Account, AccountClassification.[Account Classification], TBMY2007.Month, TBMY2007.Year, TBMY2007.MonthAmt, TBMY2007.YTDAmt
FROM TBMY2007, AccountClassification
WHERE (((TBMY2007.Account) Between ([AccountClassification].[BeginAcct]) And ([AccountClassification].[EndAcct])));