Problem When Sorting Query

S

Steve

I have a query that runs fine without sorting, but when I try to sort it, it
just runs and runs. It is a query of a subquery. Here is the subquery (named
qryC3NotesNotUsedPt1):

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.CMSID,
tblC3Notes.HotKey
FROM tblC3Notes, tblEmployees
ORDER BY tblEmployees.LastName, tblC3Notes.HotKey;

tblEmployees is a linked table

Here is the query that uses the subquery above:

SELECT [qryC3NotesNotUsedPt1].[LastName] & ", " &
[qryC3NotesNotUsedPt1].[FirstName] AS [User Name], qryC3NotesNotUsedPt1.HotKey
FROM tblC3NotesCompleted, qryC3NotesNotUsedPt1
WHERE (((qryC3NotesNotUsedPt1.HotKey) Not In (SELECT C3NoteHotKey FROM
tblC3NotesCompleted WHERE tblC3NotesCompleted.CMSID =
qryC3NotesNotUsedPt1.CMSID AND tblC3NotesCompleted.C3NoteHotKey Is Not
Null)));

Like I said, it runs fine unsorted, but if I try to add an ORDER BY clause
it just runs and runs and never opens. I have waited up to 20 minutes to let
it open and it has not opened. I am trying to sort by
[qryC3NotesNotUsedPt1].[LastName] & ", " & [qryC3NotesNotUsedPt1].[FirstName].

Any suggestions? Thanks for your assistance.

Steve
 
K

KARL DEWEY

What is the relationship between tblC3Notes and tblEmployees? Your query as
written will give you a Cartesian effect, the number of records in tblC3Notes
multiplied by the number of records in tblEmployees.

And the same for tblC3NotesCompleted and qryC3NotesNotUsedPt1.
 
S

Steve

They do not have a join. I simply have qryC3NotesNotUsedPt1 set up so I have
a list of all C3 Notes that each employee should have used. The second query
compares this to what was actually used and tells me what was NOT used by
each employee. The query/subquery returns the information I want - it just
locks up when I try to sort it using an ORDER BY clause.

Steve

KARL DEWEY said:
What is the relationship between tblC3Notes and tblEmployees? Your query as
written will give you a Cartesian effect, the number of records in tblC3Notes
multiplied by the number of records in tblEmployees.

And the same for tblC3NotesCompleted and qryC3NotesNotUsedPt1.

--
Build a little, test a little.


Steve said:
I have a query that runs fine without sorting, but when I try to sort it, it
just runs and runs. It is a query of a subquery. Here is the subquery (named
qryC3NotesNotUsedPt1):

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.CMSID,
tblC3Notes.HotKey
FROM tblC3Notes, tblEmployees
ORDER BY tblEmployees.LastName, tblC3Notes.HotKey;

tblEmployees is a linked table

Here is the query that uses the subquery above:

SELECT [qryC3NotesNotUsedPt1].[LastName] & ", " &
[qryC3NotesNotUsedPt1].[FirstName] AS [User Name], qryC3NotesNotUsedPt1.HotKey
FROM tblC3NotesCompleted, qryC3NotesNotUsedPt1
WHERE (((qryC3NotesNotUsedPt1.HotKey) Not In (SELECT C3NoteHotKey FROM
tblC3NotesCompleted WHERE tblC3NotesCompleted.CMSID =
qryC3NotesNotUsedPt1.CMSID AND tblC3NotesCompleted.C3NoteHotKey Is Not
Null)));

Like I said, it runs fine unsorted, but if I try to add an ORDER BY clause
it just runs and runs and never opens. I have waited up to 20 minutes to let
it open and it has not opened. I am trying to sort by
[qryC3NotesNotUsedPt1].[LastName] & ", " & [qryC3NotesNotUsedPt1].[FirstName].

Any suggestions? Thanks for your assistance.

Steve
Click to expand...
Click to expand...
 
J

Jerry Whittle

Try this:

Order By [qryC3NotesNotUsedPt1].[LastName],
[qryC3NotesNotUsedPt1].[FirstName]
 
S

Steve

It still locks up. I am using Access 2000 if that matters. I tried a query
with a sort on each individual field instead of all of it and I get the
problem even with individual fields. I have other queries that are set up
like this one in other Access dbs I have and they work fine. I am confused
why this is happening.

Thanks,
Steve

Jerry Whittle said:
Try this:

Order By [qryC3NotesNotUsedPt1].[LastName],
[qryC3NotesNotUsedPt1].[FirstName]

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve said:
I have a query that runs fine without sorting, but when I try to sort it, it
just runs and runs. It is a query of a subquery. Here is the subquery (named
qryC3NotesNotUsedPt1):

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.CMSID,
tblC3Notes.HotKey
FROM tblC3Notes, tblEmployees
ORDER BY tblEmployees.LastName, tblC3Notes.HotKey;

tblEmployees is a linked table

Here is the query that uses the subquery above:

SELECT [qryC3NotesNotUsedPt1].[LastName] & ", " &
[qryC3NotesNotUsedPt1].[FirstName] AS [User Name], qryC3NotesNotUsedPt1.HotKey
FROM tblC3NotesCompleted, qryC3NotesNotUsedPt1
WHERE (((qryC3NotesNotUsedPt1.HotKey) Not In (SELECT C3NoteHotKey FROM
tblC3NotesCompleted WHERE tblC3NotesCompleted.CMSID =
qryC3NotesNotUsedPt1.CMSID AND tblC3NotesCompleted.C3NoteHotKey Is Not
Null)));

Like I said, it runs fine unsorted, but if I try to add an ORDER BY clause
it just runs and runs and never opens. I have waited up to 20 minutes to let
it open and it has not opened. I am trying to sort by
[qryC3NotesNotUsedPt1].[LastName] & ", " & [qryC3NotesNotUsedPt1].[FirstName].

Any suggestions? Thanks for your assistance.

Steve
Click to expand...
Click to expand...
 
K

KARL DEWEY

Ok, then join in the second query like this --
FROM qryC3NotesNotUsedPt1 LEFT JOIN tblC3NotesCompleted ON
qryC3NotesNotUsedPt1.HotKey = tblC3NotesCompleted.HotKey

--
Build a little, test a little.


Steve said:
They do not have a join. I simply have qryC3NotesNotUsedPt1 set up so I have
a list of all C3 Notes that each employee should have used. The second query
compares this to what was actually used and tells me what was NOT used by
each employee. The query/subquery returns the information I want - it just
locks up when I try to sort it using an ORDER BY clause.

Steve

KARL DEWEY said:
What is the relationship between tblC3Notes and tblEmployees? Your query as
written will give you a Cartesian effect, the number of records in tblC3Notes
multiplied by the number of records in tblEmployees.

And the same for tblC3NotesCompleted and qryC3NotesNotUsedPt1.

--
Build a little, test a little.


Steve said:
I have a query that runs fine without sorting, but when I try to sort it, it
just runs and runs. It is a query of a subquery. Here is the subquery (named
qryC3NotesNotUsedPt1):

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.CMSID,
tblC3Notes.HotKey
FROM tblC3Notes, tblEmployees
ORDER BY tblEmployees.LastName, tblC3Notes.HotKey;

tblEmployees is a linked table

Here is the query that uses the subquery above:

SELECT [qryC3NotesNotUsedPt1].[LastName] & ", " &
[qryC3NotesNotUsedPt1].[FirstName] AS [User Name], qryC3NotesNotUsedPt1.HotKey
FROM tblC3NotesCompleted, qryC3NotesNotUsedPt1
WHERE (((qryC3NotesNotUsedPt1.HotKey) Not In (SELECT C3NoteHotKey FROM
tblC3NotesCompleted WHERE tblC3NotesCompleted.CMSID =
qryC3NotesNotUsedPt1.CMSID AND tblC3NotesCompleted.C3NoteHotKey Is Not
Null)));

Like I said, it runs fine unsorted, but if I try to add an ORDER BY clause
it just runs and runs and never opens. I have waited up to 20 minutes to let
it open and it has not opened. I am trying to sort by
[qryC3NotesNotUsedPt1].[LastName] & ", " & [qryC3NotesNotUsedPt1].[FirstName].

Any suggestions? Thanks for your assistance.

Steve
Click to expand...
Click to expand...
Click to expand...
 
J

Jerry Whittle

My next WAG would be to remove the Order By sort in the subquery. You are
sorting the data there then later on sorting the data a different way. That
would slow things down.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve said:
It still locks up. I am using Access 2000 if that matters. I tried a query
with a sort on each individual field instead of all of it and I get the
problem even with individual fields. I have other queries that are set up
like this one in other Access dbs I have and they work fine. I am confused
why this is happening.

Thanks,
Steve

Jerry Whittle said:
Try this:

Order By [qryC3NotesNotUsedPt1].[LastName],
[qryC3NotesNotUsedPt1].[FirstName]

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Steve said:
I have a query that runs fine without sorting, but when I try to sort it, it
just runs and runs. It is a query of a subquery. Here is the subquery (named
qryC3NotesNotUsedPt1):

SELECT tblEmployees.LastName, tblEmployees.FirstName, tblEmployees.CMSID,
tblC3Notes.HotKey
FROM tblC3Notes, tblEmployees
ORDER BY tblEmployees.LastName, tblC3Notes.HotKey;

tblEmployees is a linked table

Here is the query that uses the subquery above:

SELECT [qryC3NotesNotUsedPt1].[LastName] & ", " &
[qryC3NotesNotUsedPt1].[FirstName] AS [User Name], qryC3NotesNotUsedPt1.HotKey
FROM tblC3NotesCompleted, qryC3NotesNotUsedPt1
WHERE (((qryC3NotesNotUsedPt1.HotKey) Not In (SELECT C3NoteHotKey FROM
tblC3NotesCompleted WHERE tblC3NotesCompleted.CMSID =
qryC3NotesNotUsedPt1.CMSID AND tblC3NotesCompleted.C3NoteHotKey Is Not
Null)));

Like I said, it runs fine unsorted, but if I try to add an ORDER BY clause
it just runs and runs and never opens. I have waited up to 20 minutes to let
it open and it has not opened. I am trying to sort by
[qryC3NotesNotUsedPt1].[LastName] & ", " & [qryC3NotesNotUsedPt1].[FirstName].

Any suggestions? Thanks for your assistance.

Steve
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

Unexpected results when sorting multi-table query using toolbar button 5
query giving odd results 2
Error message regarding duplicate values 1
Bizarre subquery syntax problem 6
Showing most recent record for each employee in a query 2
Query Date Problem 8
Report From Form 2
Combobox Question 8

Top