Lookup queried value in another table

[snobordin8]

I have two tables of data. One table looks like this

310110.111001	242
310113.111001	500
310110.111002	350
310115.111002	475

2nd table looks like this:

111001	Cash in Bank
111002	Cash in Transit

In my query, I want it to pull out the 111001 piece from table 1, and look up the item in table 2. I currently query using the mid function to pull out the 111001. I don't know how to look that value up in the 2nd table, and return it to the same query.



Output should look like this:

310110.111001	111001	Cash in Bank	242
310113.111001	111001	Cash in Bank	500
310110.111002	111002	Cash in Transit	350
310115.111002	111002	Cash in Transit	475

Any ideas? Thanks.

[rpeare]

let's say your first table is called table1 and your second table is called table2
Table1 has fields Field1, Field2
Table2 had fields Field1a, Field2a

Create this query:

Select Field1, Right(Field1,6) as Field1aCalc, Field2 FROM Table1

Save it as query1

This should give you a query that looks like:

310110.111001 111001 242
310113.111001 111001 500
310110.111002 111002 350
310115.111002 111002 475

(I think your output is in error the last two should have a 111002 in their second column)

now create a new query with QUERY1 as the base and link it to Table2 via the Field1aCalc to Field1a and dump in the field you want.

Don't do this query with a dlookup function.

[snobordin8]

You're right, my output was in error. I updated it.

I think what you describe will work for me, but I was hoping to do this all within one query. It only adds one more query to the database but I'm trying to keep this as simple as possible.

[rpeare]

You can do it in one query without using dlookup functions as well it's a bit more complicated but the net effect is the same.

Here's a link to an example:

http://stackoverflow.com/questions/2...lect-statement

It's harder to edit these select in select queries if you're not a SQL person.

If you are looking for a purely visual cleaning of your database you can hide all your queries or some of them if you want to.