excluding the original criteria from the Query result?

G

Guest

I have a (complex) query that uses a [Run_No] for it's criteria, which when
entered, it finds any matching Run_No's (that are related via other
criteria). All works well, except I also get the inputted Criteria Run_No in
the result too. I want to be able to get all the other Run_No's but exclude
the original criteria input. Can this be done?

Here is the example of my QBE which I use to input the Criteria.

Field: Run_No
Table: B
Total: Where
Criteria: [Find Run No]

Here is the full QBE:
SELECT A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No AS
Run_No_Entry, B.Run_From_Postcode AS User_FromPCode, B.Run_To_Postcode AS
User_ToPCode, B.Run_From AS UserFrom, B.Run_To AS UserTo
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Find Run No]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No,
B.Run_From_Postcode, B.Run_To_Postcode, B.Run_From, B.Run_To
ORDER BY A.Run_No;
 
J

Jeff Boyce

I'm not following you. It sounds like you are saying that you get the RunNo
data that you tell the query to use for selection purposes. Isn't that what
you wanted?

If you want to get all of the RunNo's EXCEPT the value you enter as a
selection criterion, you would use:

Not [Enter Run_No]

for the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
G

Guest

Jeff,

As i mentioned, it is a complex query. The way it works is that I enter a
Run_No, (which represents an address with postcode) the query then looks for
all other Run_no's with matching postcodes. Which I then run a Report from,
but at the top of the report is the Original Run_No to be matched, but
because it also appears in the query results means that it appears in the
report as cross-referencing itself. So...

'I want to be able to get all the other Run_No's but exclude the original
input criteria . Can this be done?'

But your solution doesn't do the job because it seems to use your criteria
Not [Find Run_No] as a secondary criteria and produces a blank sheet.




If you cut n paste my SQL, you may get an idea of what I mean.


Jeff Boyce said:
I'm not following you. It sounds like you are saying that you get the RunNo
data that you tell the query to use for selection purposes. Isn't that what
you wanted?

If you want to get all of the RunNo's EXCEPT the value you enter as a
selection criterion, you would use:

Not [Enter Run_No]

for the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

efandango said:
I have a (complex) query that uses a [Run_No] for it's criteria, which when
entered, it finds any matching Run_No's (that are related via other
criteria). All works well, except I also get the inputted Criteria Run_No
in
the result too. I want to be able to get all the other Run_No's but
exclude
the original criteria input. Can this be done?

Here is the example of my QBE which I use to input the Criteria.

Field: Run_No
Table: B
Total: Where
Criteria: [Find Run No]

Here is the full QBE:
SELECT A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No AS
Run_No_Entry, B.Run_From_Postcode AS User_FromPCode, B.Run_To_Postcode AS
User_ToPCode, B.Run_From AS UserFrom, B.Run_To AS UserTo
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Find Run No]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No,
B.Run_From_Postcode, B.Run_To_Postcode, B.Run_From, B.Run_To
ORDER BY A.Run_No;
Click to expand...
Click to expand...
 
G

Guest

Jeff, following my reply to your post, i had an idea to create a 2nd query
linked to the first query, using the 'Not' function for [Run_No], which
worked, but only after the user has to input the the matching criteria for
the 1st query and the same criteria in the 2nd query. Which is a bit kludgy,
so do you know how to make the 2nd query automatically take and use the 1st
query's criteria without the user having to input it again?


efandango said:
Jeff,

As i mentioned, it is a complex query. The way it works is that I enter a
Run_No, (which represents an address with postcode) the query then looks for
all other Run_no's with matching postcodes. Which I then run a Report from,
but at the top of the report is the Original Run_No to be matched, but
because it also appears in the query results means that it appears in the
report as cross-referencing itself. So...

'I want to be able to get all the other Run_No's but exclude the original
input criteria . Can this be done?'

But your solution doesn't do the job because it seems to use your criteria
Not [Find Run_No] as a secondary criteria and produces a blank sheet.




If you cut n paste my SQL, you may get an idea of what I mean.


Jeff Boyce said:
I'm not following you. It sounds like you are saying that you get the RunNo
data that you tell the query to use for selection purposes. Isn't that what
you wanted?

If you want to get all of the RunNo's EXCEPT the value you enter as a
selection criterion, you would use:

Not [Enter Run_No]

for the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

efandango said:
I have a (complex) query that uses a [Run_No] for it's criteria, which when
entered, it finds any matching Run_No's (that are related via other
criteria). All works well, except I also get the inputted Criteria Run_No
in
the result too. I want to be able to get all the other Run_No's but
exclude
the original criteria input. Can this be done?

Here is the example of my QBE which I use to input the Criteria.

Field: Run_No
Table: B
Total: Where
Criteria: [Find Run No]

Here is the full QBE:
SELECT A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No AS
Run_No_Entry, B.Run_From_Postcode AS User_FromPCode, B.Run_To_Postcode AS
User_ToPCode, B.Run_From AS UserFrom, B.Run_To AS UserTo
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Find Run No]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From, A.Run_To, A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A, B.Run_No,
B.Run_From_Postcode, B.Run_To_Postcode, B.Run_From, B.Run_To
ORDER BY A.Run_No;
Click to expand...
Click to expand...
Click to expand...
 
J

Jeff Boyce

One approach might be to include the input criterion as a new field in the
first query (as well as a selection criterion). That way you could refer to
that value in the second query.

Regards

Jeff Boyce
Microsoft Office/Access MVP

efandango said:
Jeff, following my reply to your post, i had an idea to create a 2nd query
linked to the first query, using the 'Not' function for [Run_No], which
worked, but only after the user has to input the the matching criteria for
the 1st query and the same criteria in the 2nd query. Which is a bit
kludgy,
so do you know how to make the 2nd query automatically take and use the
1st
query's criteria without the user having to input it again?


efandango said:
Jeff,

As i mentioned, it is a complex query. The way it works is that I enter a
Run_No, (which represents an address with postcode) the query then looks
for
all other Run_no's with matching postcodes. Which I then run a Report
from,
but at the top of the report is the Original Run_No to be matched, but
because it also appears in the query results means that it appears in the
report as cross-referencing itself. So...

'I want to be able to get all the other Run_No's but exclude the original
input criteria . Can this be done?'

But your solution doesn't do the job because it seems to use your
criteria
Not [Find Run_No] as a secondary criteria and produces a blank sheet.




If you cut n paste my SQL, you may get an idea of what I mean.


Jeff Boyce said:
I'm not following you. It sounds like you are saying that you get the
RunNo
data that you tell the query to use for selection purposes. Isn't that
what
you wanted?

If you want to get all of the RunNo's EXCEPT the value you enter as a
selection criterion, you would use:

Not [Enter Run_No]

for the selection criterion.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a (complex) query that uses a [Run_No] for it's criteria, which
when
entered, it finds any matching Run_No's (that are related via other
criteria). All works well, except I also get the inputted Criteria
Run_No
in
the result too. I want to be able to get all the other Run_No's but
exclude
the original criteria input. Can this be done?

Here is the example of my QBE which I use to input the Criteria.

Field: Run_No
Table: B
Total: Where
Criteria: [Find Run No]

Here is the full QBE:
SELECT A.KeyCode, A.Run_No, A.Run_From, A.Run_To,
A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A,
B.Run_No AS
Run_No_Entry, B.Run_From_Postcode AS User_FromPCode,
B.Run_To_Postcode AS
User_ToPCode, B.Run_From AS UserFrom, B.Run_To AS UserTo
FROM FindCodes AS A, FindCodes AS B
WHERE (((A.KeyCode)=.[Run_To_PostCode] Or
(A.KeyCode)=.[Run_From_PostCode]) AND ((B.Run_No)=[Find Run No]))
GROUP BY A.KeyCode, A.Run_No, A.Run_From, A.Run_To,
A.Run_From_Postcode,
A.Run_To_Postcode, A.Run_Point_Venue_A, A.Run_Point_Address_A,
B.Run_No,
B.Run_From_Postcode, B.Run_To_Postcode, B.Run_From, B.Run_To
ORDER BY A.Run_No;
Click to expand...
Click to expand...
Click to expand...
 
Top