I Need a subquery to replace Dlookup

E

Ed

I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic Location
No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
 
T

Tom Ellison

Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other assistance.

Are the two columns MELID and MEL ID indexed? This could be important for
performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this? Just
to eliminate those not found? If so, the above should be correct in that
respect.

Tom Ellison
 
E

Ed

This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a field

However, this worked as a query but this creates a non-updatable query. I
want to be able to update field Location from field [Basic Location No]

Ed

Tom Ellison said:
Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other assistance.

Are the two columns MELID and MEL ID indexed? This could be important for
performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be correct in
that respect.

Tom Ellison


Ed said:
I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Ed:

May I suggest you open the spreadsheet from Tables in the Database menu and
see if it is updatable.

Tom Ellison


Ed said:
This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a field

However, this worked as a query but this creates a non-updatable query. I
want to be able to update field Location from field [Basic Location No]

Ed

Tom Ellison said:
Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other
assistance.

Are the two columns MELID and MEL ID indexed? This could be important
for performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be correct in
that respect.

Tom Ellison


Ed said:
I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
Click to expand...
 
E

Ed

The spreadsheet (Valves) is not updatable, but I do not want to update the
spreadsheet, only tblEquipment. My initial query does that but it is very
slow.

Tom Ellison said:
Dear Ed:

May I suggest you open the spreadsheet from Tables in the Database menu
and see if it is updatable.

Tom Ellison


Ed said:
This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a field

However, this worked as a query but this creates a non-updatable query. I
want to be able to update field Location from field [Basic Location No]

Ed

Tom Ellison said:
Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other
assistance.

Are the two columns MELID and MEL ID indexed? This could be important
for performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be correct
in that respect.

Tom Ellison


I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Ed:

Built and tested:

UPDATE Valves AS V
INNER JOIN tblEquipment AS E
ON V.[MEL ID] = E.MELID
SET E.Location = V.[Basic Location No];

Tom Ellison


Ed said:
The spreadsheet (Valves) is not updatable, but I do not want to update the
spreadsheet, only tblEquipment. My initial query does that but it is very
slow.

Tom Ellison said:
Dear Ed:

May I suggest you open the spreadsheet from Tables in the Database menu
and see if it is updatable.

Tom Ellison


Ed said:
This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a
field

However, this worked as a query but this creates a non-updatable query.
I want to be able to update field Location from field [Basic Location
No]

Ed

Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other
assistance.

Are the two columns MELID and MEL ID indexed? This could be important
for performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be correct
in that respect.

Tom Ellison


I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
Click to expand...
 
E

Ed

Works great and is FAST. Thanks
Ed

Tom Ellison said:
Dear Ed:

Built and tested:

UPDATE Valves AS V
INNER JOIN tblEquipment AS E
ON V.[MEL ID] = E.MELID
SET E.Location = V.[Basic Location No];

Tom Ellison


Ed said:
The spreadsheet (Valves) is not updatable, but I do not want to update
the spreadsheet, only tblEquipment. My initial query does that but it is
very slow.

Tom Ellison said:
Dear Ed:

May I suggest you open the spreadsheet from Tables in the Database menu
and see if it is updatable.

Tom Ellison


This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a
field

However, this worked as a query but this creates a non-updatable query.
I want to be able to update field Location from field [Basic Location
No]

Ed

Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other
assistance.

Are the two columns MELID and MEL ID indexed? This could be important
for performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be correct
in that respect.

Tom Ellison


I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
Click to expand...
 
T

Tom Ellison

Dear Ed:

This is actually the simple and direct solution. Simple is good, eh?

Tom Ellison


Ed said:
Works great and is FAST. Thanks
Ed

Tom Ellison said:
Dear Ed:

Built and tested:

UPDATE Valves AS V
INNER JOIN tblEquipment AS E
ON V.[MEL ID] = E.MELID
SET E.Location = V.[Basic Location No];

Tom Ellison


Ed said:
The spreadsheet (Valves) is not updatable, but I do not want to update
the spreadsheet, only tblEquipment. My initial query does that but it is
very slow.

Dear Ed:

May I suggest you open the spreadsheet from Tables in the Database menu
and see if it is updatable.

Tom Ellison


This did not work. Perhaps you meant:

SELECT E.MELID, E.Location, B.[Basic Location No]
FROM tblEquipment E
INNER JOIN Valves B
ON B.[MEL ID] = E.MELID

Valves is a a linked Excel spreadsheet and [Basic Location No] is a
field

However, this worked as a query but this creates a non-updatable
query. I want to be able to update field Location from field [Basic
Location No]

Ed

Dear Ed:

Could it be:

SELECT E.MELID, E.Location, B.Valves
FROM tblEquipment E
INNER JOIN [Basic Location No] B
ON B.[MEL ID] = E.MELID

Please let me know if this helps, and if I can be of any other
assistance.

Are the two columns MELID and MEL ID indexed? This could be
important for performance.

You used Nz(MELID, 0) in the criterion. What is the reason for this?
Just to eliminate those not found? If so, the above should be
correct in that respect.

Tom Ellison


I have two tables:
tblEquipment - a standard MS Access table
Valves - a link excel spreadsheet

Fields MELID and MEL ID have a primary/forign key relationship
A standard join returns a non-updatable query
I want to put Basic Location No into Location

I think I need a subquery to replace the DLookUp in the query below:

This query does the job but is very slooooooow.
SELECT tblEquipment.MELID, tblEquipment.Location, DLookUp("[Basic
Location No]","Valves","[MEL ID] = " & nz([MELID],0)) AS Expr1
FROM tblEquipment
WHERE (((DLookUp("[Basic Location No]","Valves","[MEL ID] = " &
nz([MELID],0))) Is Not Null));

Thanks,
Ed
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Is a query with a field subquery updatable? 8
Subquery doesn't return GUIDs 1
DlookUp in a Query help 2
SubQuery vs. DLookup() 2
Subquery not integrating to query to display previous sum of quantities 3
using dlookup in a query 2
Multi-level Group By Clause is not allowed in a subquery 4
is this query with a subquery, a union query, normal? 1

Top