Dlookup in query 2

[lars7]

Hi Guys,
This query works fine if there is only one record in tblRecSource but is there a way to do it if there are more than one

SELECT StaffList.[Division code], *
FROM StaffList
WHERE (((StaffList.[Division code])=DLookUp("ServiceCode","tblRecSource")));

or is there another way to do the same thing, if I link the table it takes to long to run.

than for advance

 

[Ed2020]

Are the two fields you are joining indexed? How many records do you have in each table?

Ed Metcalfe.

Please do not feed the trolls.....

 

[lars7]

Hi Ed,

Thanks for replying,

StaffList in on a server and holds 52,000 records.

PayNumber (PK)
I tried to make Division code Indexed but it would not let me maybe because there are nulls.

tblRecSource is a small local table with 1 field ServiceCode(PK) and will only hold around 4 records maximum.

I use this to populate Servicecode from the splash screen


strSQL1 = "Delete *" & _
"FROM tblRecSource "

CurrentDb.Execute strSQL1, dbFailOnError


strSQL2 = "INSERT INTO tblRecSource ( ServiceCode ) " & _
"SELECT tbluser.ServiceCode " & _
"FROM tbluser " & _
"WHERE tbluser.UserName ='" & [Forms]![FrmSplash]![TextUserName] & "'"

CurrentDb.Execute strSQL2, dbFailOnError


Users will only be allow to see the records for their own staff from Stafflist so that is why I need to use the filter but it takes around 10 second for the form to open.

 

[Ed2020]

Lars,

So if the query is based on the two tables (linked) what SQL do you have?

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

What about this ?
SELECT StaffList.*
FROM StaffList INNER JOIN tblRecSource ON StaffList.[Division code] = tblRecSource.ServiceCode

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Hi Ed,

Sorry this is the SQL:

SELECT *
FROM StaffList INNER JOIN tblRecSource ON StaffList.[Division code] = tblRecSource.ServiceCode;

that take around 10 seconds to open the form and this one opens instantly.

SELECT StaffList.[Division code], *
FROM StaffList
WHERE (((StaffList.[Division code])=DLookUp("ServiceCode","tblRecSource")));

 

[Ed2020]

In which case tblStaffList.[Division Code] will need to be indexed...

Ed Metcalfe.

Please do not feed the trolls.....

 

[lars7]

Hi Ph,

I tried yours but it wasn't much quicker

 

[Ed2020]

Lars,

If you index the Division Code field you will see a considerable improvement in execution time for PHV's suggestion. Selecting the "Indexed: Yes (duplicates OK)" option should be the correct option.

I'm not sure what Division Code actually contains but I suspect it should not contain nulls for any records. If this is the case I'd suggest doing a data clean-up at some point to populate the missing values...

Ed Metcalfe.

Please do not feed the trolls.....

 

[lars7]

Hi Ed,
I'm new to servers so I had to work out how to set the index properly and it worked great thanks.

 

[lars7]

and of course thanks PHV