DLookup or Other Solution

[IFA Stamford]

Thanks for the help on my previous post. I am now looking for a solution on the above subject.



For example I have a table that has two columns of data. The first column will be my reference column to compare to another table. I would like to use the Dlookup function in my query to take any record that matches and return what would be in the second column.

For instance

Table 1
A
B
C
D

Table 2
A 45
B 62
C 14
D 1

How could I write the Dlookup such that in the 64,000 records any match that comes across as example A in table one, would create a new record that would show a match from table two and return the 45?

[ajetrumpet]

be wary of using the DL() function with large tables like that. I recently ran a similar function in a query (using dlookup) on a table 160K records and it took something like 1 min to execute. So if you break that down statistically, that's 160000/60 seconds = 2700 records a second. And a normal query that doesn't execute any built in functions, I'm sure can run more than 50000 records a second (OK, maybe that's pushing it a bit, but you get the idea).

a better way would certainly be similar to the example I posted here: https://www.accessforums.net/queries...html#post42905

That runs almost instantaneous on large sets, as is a great alternative to using lookups. It can be modified of course.

[IFA Stamford]

be wary of using the DL() function with large tables like that. I recently ran a similar function in a query (using dlookup) on a table 160K records and it took something like 1 min to execute. So if you break that down statistically, that's 160000/60 seconds = 2700 records a second. And a normal query that doesn't execute any built in functions, I'm sure can run more than 50000 records a second (OK, maybe that's pushing it a bit, but you get the idea).

a better way would certainly be similar to the example I posted here: https://www.accessforums.net/queries...html#post42905

That runs almost instantaneous on large sets, as is a great alternative to using lookups. It can be modified of course.

The thread you sent me to was an Update Table thread. I am trying more to have information retrieve not overwrite an existing table.

Here is what the logic currently looks like:

IIf([Rev '10 Pt II]![Product Level 2]="Solution Sales" And [Rev '10 Pt II]![DeDup]="Dedup of Corporate Bond Cross Sell","Solution Sales",IIf([Rev '10 Pt II]![Product Level 1]="Non Core","Exclude",IIf([Rev '10 Pt II]![Product Level 1]="Portfolio" And [Rev '10 Pt II]![Fact Type Desc]="TLP","TLP",IIf([Rev '10 Pt II]![Product Level 2]="Markets" And [Rev '10 Pt II]![Fact Type Desc]="Franchise Offset","Exclude",IIf([Rev '10 Pt II]![DeDup]="50% Bond Dedup" And [Rev '10 Pt II]![Product Level 2]="DCM","Exclude",IIf([Rev '10 Pt II]![Fact Type Desc]="Franchise Offset" And [Rev '10 Pt II]![Product Level 2]="DCM","Exclude",DLookUp([Khalix Hier Lvl 8 Desc],[Khalix Hier Lvl 8 Desc]![C Mi Desc],[Rev '10 Pt II]![Khalix Hier Lvl 8 Desc]=[Khalix Hier Lvl 8 Desc]![Khalix Hier Lvl 8 Desc])))))))

The piece in red was my Dlookup. I was essentially trying to have it

a) Lookup the values that come up in the column (Khalix Hier Lvl 8 Desc)
b) Compare with another table (Khalix Hier Lvl 8 Desc (C MI Desc)
c) Identify where the records match between the two tables.

[ajetrumpet]

IFA,

I know it was an update query. I said it could be modified. I expect people to be able to interpret what I post and make an effort to help themselves. E.G. - not look at my advice as black and white and expect it to be a spoonfeed, as it never is. And the world never is either, as a matter of fact.

But as far as your statement, I'm sorry but I don't have the time right now to disect it into a readable form. I will take a look at it again if you can linefeed it or parse it out and post it in a more readable form instead of one 5-line chunk of sql.

One thing that I do notice though, is that you're using the bang (!) to reference other table values, which I don't think works, but I could be wrong. At the very least though, it's bad practice because it is not congruent among all entities in Access that can utilize absolute and/or relative references.

Please parse it out so people can read it. The placement of linefeeds don't matter, but it has to be distiguishible.