DLookup assisstance

[AndyTheAnimal]

Hi all, apologies but I have looked and looked for this. There's a wealth of Dlookup examples but none seem to work.

I would like to utilise a dlookup within a select query. I have two tables A and B.

Field 1 in table A should be used to look up against Field 1 in Table B. I want to return the value of Field 2 in Table B.

Obviously a simple table link would work, however I want to utilise dlookup to prevent me utilising the same reference table circa 30 times...



I've used this process before but it was in a form, rather than a select statement.
Any help whatsoever with where I am going wrong appreciated!

[rpeare]

Do not use domain functions in queries if you can avoid it. They are incredibly memory/time intensive.

Just take the example you stated. Just for the sake of argument let's say table A has 10 records and table B has 10 records. If you are just looking up *1* value from table B for each record in table A you will perform an evaluation of the entire data set 10 times, in other words Access will be processing 100 records (the same 10 records 10 times). If you then multiply this by 30 fields you're talking about cycling through the same 10 records 300 times or 3000 records. It is horribly, horribly inefficient.

I do not know anything about your data structure but from appearances you are not using a normalized structure or you have one table that is storing options for a variety of tables/fields but I suspect the former rather than the latter. Perhaps if you have control over the design of the tables you might consider looking at normalizing your structure so you don't have to add the same table 30 times to get what you want in your query.

[AndyTheAnimal]

Many thanks for taking the time to reply. I've included a simplified database.

Basically I want to recreate the output that I'm achieving here in my query, but don't want to have to utilise multiple table joins when utilising my ICD-10 reference table. I have other fields/reference tables that will require a similar approach.

Happy to consider other approaches but I've used dlookup previously in a report or form (can't remember which) and it worked well. The database won't become large as it's mainly for presenting data through an automated process for something.

Any help welcomed.

[rpeare]

Your query is exactly the way you should be doing it, now I understand why you've got a non normalized structure, healthcare diagnoses systems notoriously do this
I would strongly recommend you NOT break from good practice, you can alias the diagnosis tables to make them easier to deal with by right clicking them in your query window and in the ALIAS row put in (for instance) PrimDiag for the primary diagnosis linked table and so on so that visually it's easier to sort through.

If however you are determined to use dlookup you should consider changing your table/field names, you are breaking a couple of very good rules in your naming convention

1. Don't use spaces in any object name
2. Don't use any special characters in object names other than an underscore ("_")

breaking these rules makes things harder on you than they should be

The syntax to do what you want is:

DiagPrim: DLookUp("[Description ICD-10 Codes and Labels are maintained by the world]","[ICD-10]","[icd-10 Code] = '" & [diagnosis primary (ICD)] & "'")

but please don't use it!

[AndyTheAnimal]

Points noted and many thanks for the assistance.
I want to prove that I can get the function to work (to myself!) and where I was going wrong for future use. Will take a look at the alias part you mention too.

Thanks again!

[AndyTheAnimal]

Hmmmm, I have now ended up with something similar to the below and the performance is chronic. Have I done something really stupid?! I can't change the table layouts unfortuantely.
In terms of your alias mention, I can see the field in the properties but not sure how to use it and a quick Google doesn't bring up anything readily understandable.

What's the best way to achieve my output please? It's driving me mad!

[rpeare]

What you've got is what I would consider the 'correct' way to do it. If your are putting the results of this query into a report you might be able to artificially normalize your data and use subreports to show your diagnoses and procedures which might help your performance.

For instance to get an artificially normalized structure for your diagnoses you'd have something like (Assuming DMICSPELLID is the unique field in this)

SELECT DMICSpellID, [Diagnosis Primary (ICD)] FROM [FCE Output] 
UNION ALL
SELECT DMICSpellID, [Diagnosis 1st Secondary (ICD)] FROM [FCE Output]
UNION ALL
SELECT DMICSpellID, [Diagnosis 2nd Secondary (ICD)] FROM [FCE Output]
UNION ALL 
etc...

Then with this union query you'd only have to link your icd10 table one time but you would have to use subreports to show all the diagnosis codes related to a single person.

[ssanfu]

RE: DLookup()

You might want to read about a repacement for DLookup(): ELookup().

Extended DLookup()
See http://www.allenbrowne.com/ser-42.html


PS..... I am in complete agreement with rpeare.