Re: Return just the first record that fits the parameter

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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


BruceM wrote:
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.

"John Spencer" <spencer@xxxxxxxxx> wrote in message news:%23AGrujKRHHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
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
.

"BruceM" <bamoob@xxxxxxxxxxxxxxxxx> wrote in message news:evPC87JRHHA.2124@xxxxxxxxxxxxxxxxxxxxxxx
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.

"John Spencer" <spencer@xxxxxxxxx> wrote in message news:OprrOPJRHHA.4916@xxxxxxxxxxxxxxxxxxxxxxx
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

"BruceM" <bamoob@xxxxxxxxxxxxxxxxx> wrote in message news:O7iUD2IRHHA.4632@xxxxxxxxxxxxxxxxxxxxxxx
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].







.

Relevant Pages

  • Re: Return just the first record that fits the parameter
    ... My actual query is somewhat more complex, ... FROM tblProcess INNER JOIN ... INNER JOIN (tblPlan as PL1 INNER JOIN tjctPlanProc ... In brief, each Plan consists of several Processes (let's say Grind, ...
    (microsoft.public.access.queries)
  • Re: Return just the first record that fits the parameter
    ... slow since they run the query one time for each record in the main query. ... FROM tblProcess INNER JOIN ... INNER JOIN (tblPlan as PL1 INNER JOIN tjctPlanProc ... In brief, each Plan consists of several Processes (let's say Grind, ...
    (microsoft.public.access.queries)
  • Re: Code runs differently in Query Analyzer & Stored Procedure
    ... Sounds like a bad plan is stored. ... When I run the query in Query analyzer, ... > Into #AllAcres ... FROM T_LEASE_SDIV B INNER JOIN T_LEASE D ...
    (microsoft.public.sqlserver.server)
  • Re: Optimizer to scan free text
    ... the database is huge and it takes hours for the query to come back. ... explain plan for it, but I am only querying one table so there is no ... Commnets like 'ABC' ... spool myplan.lst ...
    (comp.databases.oracle.misc)
  • Re: Return just the first record that fits the parameter
    ... INNER JOIN (tblPlan INNER JOIN tjctPlanProc ... ABC 10-2 and ABC 10-21 are subsets of the same process, ... Maybe I should mention that there is more to this query than I have ... get 10-2 and 10-21 due to the wildcard in the criteria. ...
    (microsoft.public.access.queries)