Problem When Sorting Query

  • Thread starter Thread starter Steve
  • Start date Start date
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
 
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.
 
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...
 
Try this:

Order By [qryC3NotesNotUsedPt1].[LastName],
[qryC3NotesNotUsedPt1].[FirstName]
 
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...
 
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...
 
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...
 
Back
Top