If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
excluding the original criteria from the Query result?
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)=[b].[Run_To_PostCode] Or (A.KeyCode)=[b].[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; |
#2
|
|||
|
|||
excluding the original criteria from the Query result?
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" wrote in message news 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)=[b].[Run_To_PostCode] Or (A.KeyCode)=[b].[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; |
#3
|
|||
|
|||
excluding the original criteria from the Query result?
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" wrote: 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" wrote in message news 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)=[b].[Run_To_PostCode] Or (A.KeyCode)=[b].[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; |
#4
|
|||
|
|||
excluding the original criteria from the Query result?
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" wrote: 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" wrote: 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" wrote in message news 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)=[b].[Run_To_PostCode] Or (A.KeyCode)=[b].[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; |
#5
|
|||
|
|||
excluding the original criteria from the Query result?
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" wrote in message ... 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" wrote: 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" wrote: 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" wrote in message news 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)=[b].[Run_To_PostCode] Or (A.KeyCode)=[b].[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; |
Thread Tools | |
Display Modes | |
|
|