Return just the first record that fits the parameter

[BruceM]

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by entering
"ABC 10" at the parameter prompt. How can I limit the result to just one of
those records?

Maybe I should mention that there is more to this query than I have showed
here, but I think the bare-bones version addresses my question. If not I
can include more details.

I get the same results when using RIGHT instead of INNER in both instances
in the SQL. Could that indicate a problem with the query's design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still get
10-2 and 10-21 due to the wildcard in the criteria. Any ideas on how to
restrict the result in that case? If it helps, my plan is to use an unbound
form to provide the criteria, so that [Enter Process] will instead be
something like [Forms]![frmPrompt]![cboProc].

 

[John Spencer]

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jerry Whittle]

You need to normalize your data. ProcName should only contain something like
"ABC 10". In another table you would have a ProcName foriegn key field and
something like a ProcNameSub field. The table would look something like:

ProcNameFK ProcNameSub
ABC 10 2
ABC 10 21
ABC 10 3

ProcNameSub should be a text field otherwise 3 is seen as larger than 21.

 

[BruceM]

I thought about doing just as you described, but of maybe 40 processes this
is the only one that has subsets, and it has three and only three of those.
In terms of the user interface I believe it would mean losing the ability to
list the processes in a continuous subform (in another part of the project),
since only the last level in nested subforms can be a continuous form. In
any case, maybe I used the term "subset" inadvisedly. The material (ABC 10)
is the same general category of stuff for all three subsets, but is actually
a different formulation and known by a different name for each of the three;
also, the processes are quite different.

I don't know if this sheds any light on things, but the fact is that some
very distinct processes start with the same letters in several other cases,
but are at least as different as, say, painting and pressure treating wood.
Carried to the extreme, ABC would be the first level, 10 the second, and -21
the third. Organizing in that way simply does not have anything to do with
the materials and processes involved.

You need to normalize your data. ProcName should only contain something
like
"ABC 10". In another table you would have a ProcName foriegn key field and
something like a ProcNameSub field. The table would look something like:

ProcNameFK ProcNameSub
ABC 10 2
ABC 10 21
ABC 10 3

ProcNameSub should be a text field otherwise 3 is seen as larger than 21.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName

1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering
"ABC 10" at the parameter prompt. How can I limit the result to just one
of
those records?

Maybe I should mention that there is more to this query than I have
showed
here, but I think the bare-bones version addresses my question. If not I
can include more details.

I get the same results when using RIGHT instead of INNER in both
instances
in the SQL. Could that indicate a problem with the query's design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still
get
10-2 and 10-21 due to the wildcard in the criteria. Any ideas on how to
restrict the result in that case? If it helps, my plan is to use an
unbound
form to provide the criteria, so that [Enter Process] will instead be
something like [Forms]![frmPrompt]![cboProc].

 

[BruceM]

Perhaps I was too brief in my explanation. If I was not using parameters
(in some cases I would like the option of returning all records) it would
look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least one case
out of nothing but coincidence, but are very different. It is quite
complicated to explain. I am pretty sure that if the problem cannot be
solved with this simplified version of the data then it cannot be solved at
all except by using exact matches rather than LIKE. This is not ideal, but
it's not bad, and may be the best option for now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the result to
just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my question.
If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the query's
design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still
get 10-2 and 10-21 due to the wildcard in the criteria. Any ideas on how
to restrict the result in that case? If it helps, my plan is to use an
unbound form to provide the criteria, so that [Enter Process] will
instead be something like [Forms]![frmPrompt]![cboProc].

 

[John Spencer]

You explanation has me confused.

In the short selection of records you display to you want Row 1, 3, and 4
returned? If not, what rows would be returned? How do I determine that you
want rows 1 and 3? I want the explanation as if you were telling your
grandmother to select the things from this stack that meet the criteria.

Also, do you have a primary key on the records?

The solution you seek may simply involve a coordinated sub-query using Top
1, but since I don't understand your rules, I am unable to see a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Perhaps I was too brief in my explanation. If I was not using parameters
(in some cases I would like the option of returning all records) it would
look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least one
case out of nothing but coincidence, but are very different. It is quite
complicated to explain. I am pretty sure that if the problem cannot be
solved with this simplified version of the data then it cannot be solved
at all except by using exact matches rather than LIKE. This is not ideal,
but it's not bad, and may be the best option for now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the result
to just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my question.
If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the query's
design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still
get 10-2 and 10-21 due to the wildcard in the criteria. Any ideas on
how to restrict the result in that case? If it helps, my plan is to use
an unbound form to provide the criteria, so that [Enter Process] will
instead be something like [Forms]![frmPrompt]![cboProc].

 

[BruceM]

My apologies again for the excessive brevity of my explanation. In other
postings about this database I provided a lot of information and either
received no response or was told that I had presented too much information.
In my attempt to be succinct I may have strayed too far in the other
direction.

In this example:

PlanID.....PlanNum.....ProcName
1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

I want rows one and three. In other words, one instance of each PlanNum.
If it matters, the next row may show:

7.............06-22..........XYZ 999

This is the only record for Plan 06-22.

PlanID is a foreign key field in the junction table tjctPlanProc. It is
Long Integer, related to the autonumber PK PlanID in tblPlan. The
autonumber PK field (ProcPlanID) from tjctPlanProc (the junction table) does
not appear in the query, but if it is there it changes nothing, so it could
be included if that helps in any way.

In brief, each Plan consists of several Processes (let's say Grind, Polish,
etc.), and each Process may be part of several different Plans, thus the
junction table tjctPlanProc. It would be convenient to be able to use "ABC
10" as the criteria for ProcName, and return all Plans that include that
Process. Let's say I am performing a Count of the number of Plans that
include a Process with a name beginning with "ABC 10". In the four-item
list above I need the count to be 2, since there are only two different
PlanNum values.

Any of the processes ABC 10-2, 10-3, and 10-21 may appear alone on a Plan,
or in combination with either or both of the others. That is a total of six
combinations. In some cases it is convenient to lump them together, and to
perform a count of all Plans that involve at least one of the processes.
That is not exactly what I am doing, but perhaps it is a better explanation
of what I seek.

I am definitely interested in hearing more about a coordinated sub-query,
expecially since you say "simply a matter...". It makes it sound as if I
may be able to understand it.

You explanation has me confused.

In the short selection of records you display to you want Row 1, 3, and 4
returned? If not, what rows would be returned? How do I determine that
you want rows 1 and 3? I want the explanation as if you were telling your
grandmother to select the things from this stack that meet the criteria.

Also, do you have a primary key on the records?

The solution you seek may simply involve a coordinated sub-query using Top
1, but since I don't understand your rules, I am unable to see a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Perhaps I was too brief in my explanation. If I was not using parameters
(in some cases I would like the option of returning all records) it would
look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least one
case out of nothing but coincidence, but are very different. It is quite
complicated to explain. I am pretty sure that if the problem cannot be
solved with this simplified version of the data then it cannot be solved
at all except by using exact matches rather than LIKE. This is not
ideal, but it's not bad, and may be the best option for now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the result
to just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my question.
If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the query's
design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still
get 10-2 and 10-21 due to the wildcard in the criteria. Any ideas on
how to restrict the result in that case? If it helps, my plan is to
use an unbound form to provide the criteria, so that [Enter Process]
will instead be something like [Forms]![frmPrompt]![cboProc].

 

[John Spencer]

Perhaps the following will work. Be warned that coordinate subqueries
are slow since they run the query one time for each record in the main
query. The only way to make them run faster is to make sure you have
indexes on the fields involved in the relationship and the search criteria.

As I said, perhaps the following will work. I have no way to test for
syntax or logic errors.

SELECT tjctPlanProc.PlanID
, tblPlan.PlanNum
, tblProcess.ProcName
FROM tblProcess INNER JOIN
(tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE tblProcess.ProcName Like [Enter Process] & "*"
AND tblProcess.ProcName =
(
SELECT TOP 1 PR.ProcName
FROM tblProcess as PR
INNER JOIN (tblPlan as PL1 INNER JOIN tjctPlanProc
ON PL1.PlanID = tjctPlanProc.PlanID)
ON PR.ProcessID = tjctPlanProc.ProcessID
WHERE PL1.PlanNum = tblPlan.PlanNum
ORDER BY PR.ProcName ASC)



---
John Spencer
Access MVP 2001-2005, 2007

My apologies again for the excessive brevity of my explanation. In other
postings about this database I provided a lot of information and either
received no response or was told that I had presented too much information.
In my attempt to be succinct I may have strayed too far in the other
direction.

In this example:

PlanID.....PlanNum.....ProcName
1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

I want rows one and three. In other words, one instance of each PlanNum.
If it matters, the next row may show:

7.............06-22..........XYZ 999

This is the only record for Plan 06-22.

PlanID is a foreign key field in the junction table tjctPlanProc. It is
Long Integer, related to the autonumber PK PlanID in tblPlan. The
autonumber PK field (ProcPlanID) from tjctPlanProc (the junction table) does
not appear in the query, but if it is there it changes nothing, so it could
be included if that helps in any way.

In brief, each Plan consists of several Processes (let's say Grind, Polish,
etc.), and each Process may be part of several different Plans, thus the
junction table tjctPlanProc. It would be convenient to be able to use "ABC
10" as the criteria for ProcName, and return all Plans that include that
Process. Let's say I am performing a Count of the number of Plans that
include a Process with a name beginning with "ABC 10". In the four-item
list above I need the count to be 2, since there are only two different
PlanNum values.

Any of the processes ABC 10-2, 10-3, and 10-21 may appear alone on a Plan,
or in combination with either or both of the others. That is a total of six
combinations. In some cases it is convenient to lump them together, and to
perform a count of all Plans that involve at least one of the processes.
That is not exactly what I am doing, but perhaps it is a better explanation
of what I seek.

I am definitely interested in hearing more about a coordinated sub-query,
expecially since you say "simply a matter...". It makes it sound as if I
may be able to understand it.

You explanation has me confused.

In the short selection of records you display to you want Row 1, 3, and 4
returned? If not, what rows would be returned? How do I determine that
you want rows 1 and 3? I want the explanation as if you were telling your
grandmother to select the things from this stack that meet the criteria.

Also, do you have a primary key on the records?

The solution you seek may simply involve a coordinated sub-query using Top
1, but since I don't understand your rules, I am unable to see a solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Perhaps I was too brief in my explanation. If I was not using parameters
(in some cases I would like the option of returning all records) it would
look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least one
case out of nothing but coincidence, but are very different. It is quite
complicated to explain. I am pretty sure that if the problem cannot be
solved with this simplified version of the data then it cannot be solved
at all except by using exact matches rather than LIKE. This is not
ideal, but it's not bad, and may be the best option for now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the result
to just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my question.
If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the query's
design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I still
get 10-2 and 10-21 due to the wildcard in the criteria. Any ideas on
how to restrict the result in that case? If it helps, my plan is to
use an unbound form to provide the criteria, so that [Enter Process]
will instead be something like [Forms]![frmPrompt]![cboProc].

 

[BruceM]

Thanks so much for your help with this. It worked using your syntax once I
changed all of the names to their actual values (I had simplified them for
purposes of the post). My actual query is somewhat more complex, so it will
probably take some experimenting before I get that to work, but I think I
understand (more or less) how this works.
I appreciate your making the point about coordinate subqueries slowing
things down, so I will check the indexing first, and in general will use
this technique with discretion. In most cases this technique will not be
necessary (filtering for part number, for instance, or for a specific
process), but it's good to know it's there.
Thanks again for staying with me on this one. This has been a great help
not only with the current project but as something I can use in the future.

Perhaps the following will work. Be warned that coordinate subqueries are
slow since they run the query one time for each record in the main query.
The only way to make them run faster is to make sure you have
indexes on the fields involved in the relationship and the search
criteria.

As I said, perhaps the following will work. I have no way to test for
syntax or logic errors.

SELECT tjctPlanProc.PlanID
, tblPlan.PlanNum
, tblProcess.ProcName
FROM tblProcess INNER JOIN
(tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE tblProcess.ProcName Like [Enter Process] & "*"
AND tblProcess.ProcName =
(
SELECT TOP 1 PR.ProcName
FROM tblProcess as PR
INNER JOIN (tblPlan as PL1 INNER JOIN tjctPlanProc
ON PL1.PlanID = tjctPlanProc.PlanID)
ON PR.ProcessID = tjctPlanProc.ProcessID
WHERE PL1.PlanNum = tblPlan.PlanNum
ORDER BY PR.ProcName ASC)



---
John Spencer
Access MVP 2001-2005, 2007

My apologies again for the excessive brevity of my explanation. In other
postings about this database I provided a lot of information and either
received no response or was told that I had presented too much
information. In my attempt to be succinct I may have strayed too far in
the other direction.

In this example:

PlanID.....PlanNum.....ProcName
1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

I want rows one and three. In other words, one instance of each PlanNum.
If it matters, the next row may show:

7.............06-22..........XYZ 999

This is the only record for Plan 06-22.

PlanID is a foreign key field in the junction table tjctPlanProc. It is
Long Integer, related to the autonumber PK PlanID in tblPlan. The
autonumber PK field (ProcPlanID) from tjctPlanProc (the junction table)
does not appear in the query, but if it is there it changes nothing, so
it could be included if that helps in any way.

In brief, each Plan consists of several Processes (let's say Grind,
Polish, etc.), and each Process may be part of several different Plans,
thus the junction table tjctPlanProc. It would be convenient to be able
to use "ABC 10" as the criteria for ProcName, and return all Plans that
include that Process. Let's say I am performing a Count of the number of
Plans that include a Process with a name beginning with "ABC 10". In the
four-item list above I need the count to be 2, since there are only two
different PlanNum values.

Any of the processes ABC 10-2, 10-3, and 10-21 may appear alone on a
Plan, or in combination with either or both of the others. That is a
total of six combinations. In some cases it is convenient to lump them
together, and to perform a count of all Plans that involve at least one
of the processes. That is not exactly what I am doing, but perhaps it is
a better explanation of what I seek.

I am definitely interested in hearing more about a coordinated sub-query,
expecially since you say "simply a matter...". It makes it sound as if I
may be able to understand it.

You explanation has me confused.

In the short selection of records you display to you want Row 1, 3, and
4 returned? If not, what rows would be returned? How do I determine
that you want rows 1 and 3? I want the explanation as if you were
telling your grandmother to select the things from this stack that meet
the criteria.

Also, do you have a primary key on the records?

The solution you seek may simply involve a coordinated sub-query using
Top 1, but since I don't understand your rules, I am unable to see a
solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Perhaps I was too brief in my explanation. If I was not using
parameters (in some cases I would like the option of returning all
records) it would look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least one
case out of nothing but coincidence, but are very different. It is
quite complicated to explain. I am pretty sure that if the problem
cannot be solved with this simplified version of the data then it
cannot be solved at all except by using exact matches rather than LIKE.
This is not ideal, but it's not bad, and may be the best option for
now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the
result to just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my
question. If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the query's
design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I
still get 10-2 and 10-21 due to the wildcard in the criteria. Any
ideas on how to restrict the result in that case? If it helps, my
plan is to use an unbound form to provide the criteria, so that
[Enter Process] will instead be something like
[Forms]![frmPrompt]![cboProc].

 

[John Spencer]

Great. It is always nice to hear that someone has benefited from the time
and effort. Especially nice when they are able to generalize a solution for
future use.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks so much for your help with this. It worked using your syntax once
I changed all of the names to their actual values (I had simplified them
for purposes of the post). My actual query is somewhat more complex, so
it will probably take some experimenting before I get that to work, but I
think I understand (more or less) how this works.
I appreciate your making the point about coordinate subqueries slowing
things down, so I will check the indexing first, and in general will use
this technique with discretion. In most cases this technique will not be
necessary (filtering for part number, for instance, or for a specific
process), but it's good to know it's there.
Thanks again for staying with me on this one. This has been a great help
not only with the current project but as something I can use in the
future.

Perhaps the following will work. Be warned that coordinate subqueries
are slow since they run the query one time for each record in the main
query. The only way to make them run faster is to make sure you have
indexes on the fields involved in the relationship and the search
criteria.

As I said, perhaps the following will work. I have no way to test for
syntax or logic errors.

SELECT tjctPlanProc.PlanID
, tblPlan.PlanNum
, tblProcess.ProcName
FROM tblProcess INNER JOIN
(tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE tblProcess.ProcName Like [Enter Process] & "*"
AND tblProcess.ProcName =
(
SELECT TOP 1 PR.ProcName
FROM tblProcess as PR
INNER JOIN (tblPlan as PL1 INNER JOIN tjctPlanProc
ON PL1.PlanID = tjctPlanProc.PlanID)
ON PR.ProcessID = tjctPlanProc.ProcessID
WHERE PL1.PlanNum = tblPlan.PlanNum
ORDER BY PR.ProcName ASC)



---
John Spencer
Access MVP 2001-2005, 2007

My apologies again for the excessive brevity of my explanation. In
other postings about this database I provided a lot of information and
either received no response or was told that I had presented too much
information. In my attempt to be succinct I may have strayed too far in
the other direction.

In this example:

PlanID.....PlanNum.....ProcName
1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

I want rows one and three. In other words, one instance of each
PlanNum. If it matters, the next row may show:

7.............06-22..........XYZ 999

This is the only record for Plan 06-22.

PlanID is a foreign key field in the junction table tjctPlanProc. It is
Long Integer, related to the autonumber PK PlanID in tblPlan. The
autonumber PK field (ProcPlanID) from tjctPlanProc (the junction table)
does not appear in the query, but if it is there it changes nothing, so
it could be included if that helps in any way.

In brief, each Plan consists of several Processes (let's say Grind,
Polish, etc.), and each Process may be part of several different Plans,
thus the junction table tjctPlanProc. It would be convenient to be able
to use "ABC 10" as the criteria for ProcName, and return all Plans that
include that Process. Let's say I am performing a Count of the number
of Plans that include a Process with a name beginning with "ABC 10". In
the four-item list above I need the count to be 2, since there are only
two different PlanNum values.

Any of the processes ABC 10-2, 10-3, and 10-21 may appear alone on a
Plan, or in combination with either or both of the others. That is a
total of six combinations. In some cases it is convenient to lump them
together, and to perform a count of all Plans that involve at least one
of the processes. That is not exactly what I am doing, but perhaps it is
a better explanation of what I seek.

I am definitely interested in hearing more about a coordinated
sub-query, expecially since you say "simply a matter...". It makes it
sound as if I may be able to understand it.

You explanation has me confused.

In the short selection of records you display to you want Row 1, 3, and
4 returned? If not, what rows would be returned? How do I determine
that you want rows 1 and 3? I want the explanation as if you were
telling your grandmother to select the things from this stack that meet
the criteria.

Also, do you have a primary key on the records?

The solution you seek may simply involve a coordinated sub-query using
Top 1, but since I don't understand your rules, I am unable to see a
solution.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.

Perhaps I was too brief in my explanation. If I was not using
parameters (in some cases I would like the option of returning all
records) it would look more like this:

1.............05-02..........ABC 10-2
1.............05-02..........ABC 10-21
2.............05-04..........ABC 10-2
2.............05-04..........ABC 10-3

As I understand, Top 1 will return only one record. As I explained to
Jerry, there are about 40 processes. Some start with the same letters
because of the connection with a particular company, or in at least
one case out of nothing but coincidence, but are very different. It
is quite complicated to explain. I am pretty sure that if the problem
cannot be solved with this simplified version of the data then it
cannot be solved at all except by using exact matches rather than
LIKE. This is not ideal, but it's not bad, and may be the best option
for now.

I suggest that you use Top 1 or use an exact match instead of Like.

SELECT TOP 1 tjctPlanProc.PlanID, tblPlan.PlanNum,
tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"))
ORDER BY tblProcess.ProcName ASC,

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

This is the SQL:

SELECT tjctPlanProc.PlanID, tblPlan.PlanNum, tblProcess.ProcName
FROM tblProcess
INNER JOIN (tblPlan INNER JOIN tjctPlanProc
ON tblPlan.PlanID = tjctPlanProc.PlanID)
ON tblProcess.ProcessID = tjctPlanProc.ProcessID
WHERE (((tblProcess.ProcName) Like [Enter Process] & "*"));

It returns something like this when the query is run:

PlanID.....PlanNum.....ProcName



1.............05-02..........ABC 10-2

1.............05-02..........ABC 10-21

ABC 10-2 and ABC 10-21 are subsets of the same process, obtained by
entering "ABC 10" at the parameter prompt. How can I limit the
result to just one of those records?

Maybe I should mention that there is more to this query than I have
showed here, but I think the bare-bones version addresses my
question. If not I can include more details.

I get the same results when using RIGHT instead of INNER in both
instances in the SQL. Could that indicate a problem with the
query's design?

When I am looking for just ABC 10-2, even if I enter "ABC 10-2" I
still get 10-2 and 10-21 due to the wildcard in the criteria. Any
ideas on how to restrict the result in that case? If it helps, my
plan is to use an unbound form to provide the criteria, so that
[Enter Process] will instead be something like
[Forms]![frmPrompt]![cboProc].