Question DLookUp Help

[MikeDuffield]

Hi everyone,

I'm just doing a fairly simple DLookUp and I'm having trouble. I'm making a query that returns all the fields in a table called "NewMasterData" with an additional field called "COMPLETED". The "COMPLETED" field needs to be populated by data from a table called "WIP". What I have so far is this:

COMPLETED: DLookUp("Comp","wip","Ref")

That does what it should do: makes a field called "COMPLETED" and puts a value in it.

At the moment it's just grabbing the first value from the "Comp" field and putting it all the way down the "COMPLETED" column. To fix this, I assume I need something along the lines of:

COMPLETED: DLookUp("Comp","wip","Ref = [wip]ref")

I realise that code is wrong, but I'm guessing it's something similar to achieve what I want.

My explanation may have over complicated things so in summary, I want it to behave like a VLOOKUP does in Excel when you copy the VLOOKUP all the way down a column.

Another example would be if we did it manually - I would say "Right, I have reference 1022 in my master data, I'll look for reference 1022 in the wip table and see when it was completed. Now I have 1023 in my master data, I'll look for 1023 in my wip table and see when it was completed" and so on...

Can anyone assist?


Regards,
Mike.

 

[pr2-eugin]

Mike.. Why do you not perform a simple Join? That way it will pick up the Value of the Comp field of the WIP table for the corresponding REF.. The query will be something like..

SELECT yourTable.*, WIP.COMP 
FROM yourTable INNER JOIN WIP ON yourTable.REF = WIP.REF;

This link will give some idea on INNER Join..

 

[MikeDuffield]

Excellent, thanks Paul.

I hadn't even thought about doing that - works perfectly.

 

[pr2-eugin]

Most welcome Mike.. Glad to help..

 

[MikeDuffield]

Paul, sorry, one more thing...

Bizarrely the "wip" table has 57,000 records, the "NewMasterData" has 12,000 yet the end result of the query has only 3,900. Is that saying it's only found 3,900 matches?

Is there a way to drag in all the data from "NewMasterData" and leave blank any it can't find in the "wip" table?

 

[pr2-eugin]

Yes inner join will result only the fields that match.. to select all data from Master table use LEFT / RIGHT Join..

 

[MikeDuffield]

Bingo - got 12,000 with the LEFT.

Thanks again!

(I think I need to study those links you gave, seems like I might be able to use this more often)

 

[pr2-eugin]

Glad Mike.. Yes.. If you have properly normalized data, you should be able to get the desired results with simple JOINS and simple queries.. The links are basically for SQL, Access SQL is not much different but may not support all functionality.. like FULL join.. Good luck..