Use of DLookup in Query

[BarbaraT]

I have a situation where I have clients, and each client has two brokers. I have a client table (showing the client ID, client name, address etc, ID# of their first broker, and ID# of their 2nd broker). I also have a Broker table, with a Broker ID as the key ,and then info about that broker (name, address).

Example:

ClientTable:

Client ID	Client Name	BrID1	BrID2
123	FirstClient	A	B
456	SecondClient	B	C

<tbody>
</tbody>

Where BrID1 and BrID2 are the ID numbers of the Client's two brokers.

BrokerTable:

BrokerID	Broker Name	Broker's Address
A	Smith	Main St
B	Jones	Elm St
C	Brown	Washington St

<tbody>
</tbody>

I'm trying to generate a query where I list each of the two brokers for each client.

Desired Query Output:

Client ID	Client Name	Name of Client's First Broker	Name of Client's 2nd Broker
123	FirstClient	Smith	Jones
456	SecondClient	Jones	Brown

<tbody>
</tbody>

I can do this in a report, using the DLookup function to pull the names of the brokers out of the broker table (the report pulls from a query generated only by the Client table).

I feel like I should be able to use the DLookup function in a query to do the same thing, but I can't make it work.

I don't think I can do a "normal" query where I link the two tables and pull from the combination, because there would need to be two links between the tables linking two separate fields in the Client table with the same link on the BrokerTable - one between [ClientTable]![BrID1] and [BrokerTable]![BrokerID] and one between [ClientTable]![BrID2] and [BrokerTable]![BrokerID]. I'm not aware that I can do such a V-shaped link, and I get error messages when I try.

If using the Dlookup function in a query is the best way to do it, what is the syntax? Do I include the BrokerTable in the "FROM" section of the query, and, if so, with what kind of link?

This example is, of course, simplified, but the real data isn't that large - I'm not concerned with a DLookup function slowing down the performance of the query running.

Thanks.

 

[Micron]

have you looked into a crosstab query? They're often used to transpose rows to columns, which it looks like you are doing. See the illustrations and explanations here to check out if this is what you need. I really struggle with them though.
https://support.office.com/en-au/ar...cc8-ba60-2cd8484667e8?ui=en-US&rs=en-AU&ad=AU

 

[BarbaraT]

Thanks for the suggestion, but I don't think a cross-tab query is the solution.

The difficulty lies in that I'm trying to match up two fields (BrID1 and BrID2) in one table with a single field (BrokerID) in another table.

 

[Joe4]

Just add the BrokerTable to your query twice (you will probably use Aliases to do that - by default, it will assign one for you, though you can change it).
On the first one, link BrID1 to the BrokerID from the first BrokerTable.
On the second one, link BrID2 to the BrokerID from the second BrokerTable.

Then return the Broker name from each of the two BrokerTables.

The SQL code for that query would look something like this:

SELECT 
    ClientTable.[Client ID], 
    ClientTable.[Client Name], 
    BrokerTable.[Broker Name], 
    BrokerTable_1.[Broker Name]
FROM 
    (ClientTable 
INNER JOIN 
    BrokerTable 
ON 
    ClientTable.BrID1 = BrokerTable.BrokerID) 
INNER JOIN 
    BrokerTable AS BrokerTable_1 
ON 
    ClientTable.BrID2 = BrokerTable_1.BrokerID;

 

[BarbaraT]

Joe4 -

This did the trick. Thanks.