Dlookup In Query . .

[Evagrius]

Hello!

I am not sure how to go about doing this - though I am sure it is an easy issue.

I have a Query with hundreds of rows. One of the Fields is Cost Center. In a table, I have two fields, Cost Center and Cost Center Number.

I would like to add "Cost Center Number" to the Query. How can I bring the Cost Center Number from the table to the Query. Can I use Dlookup? I would be grateful if anyone can advice. Thanks!

 

[vbaInet]

Use a query, select the two tables and join them by Cost Center. No need to duplicate data.

 

[Evagrius]

Hi vbaInet - I edited my post. If what you suggested is still valid - can you give more detail? Thanks! Also, just to learn, would Dlookup work for this?

 

[vbaInet]

Create another query (in design view, not the wizard), add your original query to the grid and add the table. Create the link between the query and the table using the Cost Center field - i.e. double click (and hold) the field in either side and pull it to the other side. Now they are linked and Cost Center Number will show correctly.

Yes a DLookup() will work but it's too costly. You can try both approaches.

 

[ions]

Join the Cost Center field in the first table to the second Table and drag the field you want from the second table to the query grid. This assumes that the Second Table has Unique Cost Center Names.

Hello!

I am not sure how to go about doing this - though I am sure it is an easy issue.

I have a Query with hundreds of rows. One of the Fields is Cost Center. In a table, I have two fields, Cost Center and Cost Center Number.

I would like to add "Cost Center Number" to the Query. How can I bring the Cost Center Number from the table to the Query. Can I use Dlookup? I would be grateful if anyone can advice. Thanks!

 

[ions]

Is DLookup ever a viable option in a Query? It seems strange to use it ever in a Query?

 

[vbaInet]

Join the Cost Center field in the first table to the second Table and drag the field you want from the second table to the query grid. This assumes that the Second Table has Unique Cost Center Names.

Pretty much what was said in my two posts. But the first is a query and the second is a table.

A DLookup() is a good tool for beginners.

 

[ions]

VBAInet,

Thanks for your response. When I wrote my response I didn't see that you already answered the question.

 

[vbaInet]

VBAInet,

Thanks for your response. When I wrote my response I didn't see that you already answered the question.

We're on the same track

 

[Evagrius]

vbaInet - you sir dwell in the stratosphere among the legends!! It works great!!

A final question - given the scenario you have resolved; this is the best way to go about it? Why do you think Dlookup is too costly? I am very happy with the solution you have provided, but would you be willing to demonstrate how I would use the dlookup - I just would like to learn? My Query name is MyQry and My table name is MyFinTable.

Thank you again vbaInet!! You have saved me again!!

 

[vbaInet]

Maybe I could leave that as an exercise for you. Here are some links to support your research:

http://www.mvps.org/access/general/gen0018.htm

http://www.techonthenet.com/access/functions/domain/dlookup.php

See if you can solve it.

Aggregate functions are just generally slow. There's no rocket science to it really.

 

[Evagrius]

That works as well Thank you!

 

[vbaInet]

Good man! Great job!