Access Query with Conditional Lookup. How?

[jason.teen]

Hi,

I have this scenario
- 2 separate db's (filename = dbOne.mdb" and "dbTwo.mdb") both with a
table called tblMapped in it

dbOne.tblMapped looks like:
----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbOne | ABC123
2 | dbOne | ASD234
3 | dbOne | JSH134
dbTwo.tblMapped looks like:
----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbTwo | ZZZ123
2 | dbTwo | GFR456
3 | dbTwo | HTER34

I then have 1 other db (consolidate.mdb) which consolidates all of this
into one tblMapped_final which looks like this.

----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbOne | ABC123
2 | dbOne | ASD234
3 | dbOne | JSH134
1 | dbTwo | ZZZ123
2 | dbTwo | GFR456
3 | dbTwo | HTER34
Now I want to run a query from this tblMapped_final but depending on
the "source_system" value, I want it to look in different tables. ie.
if source_system is "dbOne" then I want it to find the record with ID =
1 from dbOne, not from dbTwo.

Is something like this possible from within MS Access or even writing
some VBA codes?

Thanks.

[Allen Browne]

A DLookup() could do it, using the value in the Source_System field for the
name of the table:

DLookup("Description", [Source_System], "ID = 1")

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ja********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

>
I have this scenario
- 2 separate db's (filename = dbOne.mdb" and "dbTwo.mdb") both with a
table called tblMapped in it

dbOne.tblMapped looks like:
----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbOne | ABC123
2 | dbOne | ASD234
3 | dbOne | JSH134
dbTwo.tblMapped looks like:
----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbTwo | ZZZ123
2 | dbTwo | GFR456
3 | dbTwo | HTER34

I then have 1 other db (consolidate.mdb) which consolidates all of this
into one tblMapped_final which looks like this.

----------------------------------------------------------------------------------------
ID (autonumber) | Source_System | Description
---------------------------------------------------------------------------------------
1 | dbOne | ABC123
2 | dbOne | ASD234
3 | dbOne | JSH134
1 | dbTwo | ZZZ123
2 | dbTwo | GFR456
3 | dbTwo | HTER34
Now I want to run a query from this tblMapped_final but depending on
the "source_system" value, I want it to look in different tables. ie.
if source_system is "dbOne" then I want it to find the record with ID =
1 from dbOne, not from dbTwo.

Is something like this possible from within MS Access or even writing
some VBA codes?

Thanks