DLOOKUP returning same data for all results?

[Bumpthis]

Hi
I have two tables, one with employee data, [Name], [EmployeeID]. A second with [EmployeeID],[ROWID],[NOTES]
I'm trying to return all Notes from the second table into a query with the first table where [ROWID] equals 1 or 2 but laid out in single rows [TABLE1]![Name], [TABLE2]![NOTES] AS NOTES1, where table2 rowid =1 and a last column with [TABLE2]![NOTES] AS NOTES2, where table2 rowid =2.

I've tried using a dlookup rather than linking the tables so that i get a unique row of data rather than a row of data multiplied by the the number of rowid's they have in the 2nd table. Unfortunately my dlookup is returning notes in columns 1 and 2 against all employees regardless of whether they have noteid's 1 & 2 or just 1

My dlookup is DLookUp("[Employees2]![Notes]","Employees2","[Employees2]![RowID]=1")

 

[ZmrAbdulla]

Your DLookup code is wrong sample syntax below.

DLookup("[FieldName]", "TableName", "[IDFeildName] = 1")

So it should be

DLookUp("[Notes]","Employees2","[RowID]=1")


________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[Bumpthis]

Thanks Zameer
It appears to be giving the notes from employee number 1 rowid 1 to all employees?
An example of my tables are:
Table1
Name EmployeeID
A 1
B 2
C 3
D 4

Table2
EmployeeID ROWID NOTES
1 1 Test1
3 1 Test2
4 1 Test3
4 2 Test4

Unfortunately every employee is getting Test1 in the dlookup meant for rowid =1 including those not present in table2?

 

[ZmrAbdulla]

Did you try to create a query with criteria to ROWID=1 ? Try & See what is the result?

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[Bumpthis]

Sorry but its still returning the same result doing this??

 

[ZmrAbdulla]

Can you please post the SQL of your query? Also sample result of th query.

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[Bumpthis]

Thanks for your help Zameer
The data in the Note field for each employee is just a concatenation of "Test" and the employee ID

SELECT Employees.LastName, Employees.EmployeeID, DLookUp("[Note]","Employees2","[RowID]=1") AS Expr3
FROM Employees;

LastName EmployeeID Expr3
Employee1 1 Test1
Employee2 2 Test1
Employee3 3 Test1
Employee4 4 Test1
Employee5 5 Test1

 

[ZmrAbdulla]

This is a sample SQL for your query (tblEmployees, tblNotes)

SELECT tblEmployees.EmployeeName, tblNotes.EmployeeID , tblNotes.RowID, tblNotes.Notes
FROM tblEmployees INNER JOIN tblNotes ON tblEmployees.EmployeeID = tblNotes.EmployeeID
WHERE (((tblNotes.RowID)="1"));

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[Bumpthis]

Thanks zameer, i'd already tried that, i don't think i've been very specific in my problem, basically i want to use dlookup to create a query which takes table 1 data
Employee Employee ID
A 1
B 2
C 3

Adds table 2 data to it where the rowid is either a 1 & 2 or just 1
Employee ID ROWID NOTE
1 1 Test1
1 2 Test2
1 3 Test3
2 1 Test2

Creating a query which finishes like this
Employee Employee ID Note1 Note2
A 1 Test1 Test2
B 2 Test2
C 3

Not sure if dlookup is the bad boy i need?

 

[ZmrAbdulla]

It is something like crosstab query. But I don't know how to accomplish this. My be some one else can help you or may be not possible!!

________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.

 

[PHV]

You may try something like this:
SELECT E.LastName, E.EmployeeID , A.Notes AS Note1, B.Notes AS Note2
FROM (tblEmployees AS E
LEFT JOIN Employees2 AS A ON E.EmployeeID = A.EmployeeID)
LEFT JOIN Employees2 AS B ON E.EmployeeID = B.EmployeeID
WHERE Nz(A.RowID,1)=1 AND Nz(B.RowID,2)=2;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bumpthis]

Excellent thanks for your help PHV
Thanks for your help on dlookup also Zameer

 

[ZmrAbdulla]

As always PHV won the race...
I believe PHV speaks SQL better than French!!


________________________________________
Zameer Abdulla
Visit Me
If you have never been hated by your child, you have never been a parent.