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
|
|||
|
|||
adding column with numbers in query
Hi, I am relatively new to Access and was hoping someone could help me out. I
have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#2
|
|||
|
|||
Hi,
You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#3
|
|||
|
|||
Hi Michel,
I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#4
|
|||
|
|||
Hi,
Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#5
|
|||
|
|||
Hi,
That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#6
|
|||
|
|||
HI,
You are right, missing the table name: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid) the " AS a " and the " AS b " are meant to be "alias" for a "reference" to the table. Indeed, we need to reference the table twice, once to get the final result, once to count how many records are in the table with the supplied condition. In the last case, one reference is " As b", the other reference, by default, is the table name itself: in the query designer, if you have the "properties sheet" visible, click on the table (on the top half portion, the "graphical" portion of the designer) and the properties sheet should said "Alias" in the first line (there is only two properties, for a table). As you see, the table name is automatically used as "alias". If you change it for " a ", then, type: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid) because "a" is now the name of one of the "reference" . Since the ( ) around the SELECT acts like a "scope", and since " AS b" is defined within this scope, you cannot refer to b outside that scope. On the other hand, " as a " is define more externally (as you can see in the main FROM clause, in SQL view) and thus you can referred to that reference anywhere within that scope, including in the innermost scope ( SELECT.... ) For references on SQL? here, sure, and some books, like http://www.amazon.ca/exec/obidos/ASI...583917-4704004 Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#7
|
|||
|
|||
Ah it is getting close. This time unique siteiid values in the query output
corresponded to a unique value in the created column. However, it looks like it ranked all 900+ rows that contained unique siteiid values in the original table BEFORE, and used those numbers in the new query column. I only want to rank the subset created by my query based on the 20 unique siteiid values in the query. So instead of succesive numbers from row 1 to row 2 to row 3, etc. in my query output that are 240, 322, 435, etc. , I need the 20 rows to number 1 to 20 (i.e. row 1 to row 2 to row 3 etc. are numbered 1,2,3 etc.) I hope this makes sense. "Michel Walsh" wrote: HI, You are right, missing the table name: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid) the " AS a " and the " AS b " are meant to be "alias" for a "reference" to the table. Indeed, we need to reference the table twice, once to get the final result, once to count how many records are in the table with the supplied condition. In the last case, one reference is " As b", the other reference, by default, is the table name itself: in the query designer, if you have the "properties sheet" visible, click on the table (on the top half portion, the "graphical" portion of the designer) and the properties sheet should said "Alias" in the first line (there is only two properties, for a table). As you see, the table name is automatically used as "alias". If you change it for " a ", then, type: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid) because "a" is now the name of one of the "reference" . Since the ( ) around the SELECT acts like a "scope", and since " AS b" is defined within this scope, you cannot refer to b outside that scope. On the other hand, " as a " is define more externally (as you can see in the main FROM clause, in SQL view) and thus you can referred to that reference anywhere within that scope, including in the innermost scope ( SELECT.... ) For references on SQL? here, sure, and some books, like http://www.amazon.ca/exec/obidos/ASI...583917-4704004 Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#8
|
|||
|
|||
Ah it is getting closer. That time it populated unique values in the created
column for each unique siteiid value in the query. However, it looked like it created the values BEFORE "Michel Walsh" wrote: HI, You are right, missing the table name: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid) the " AS a " and the " AS b " are meant to be "alias" for a "reference" to the table. Indeed, we need to reference the table twice, once to get the final result, once to count how many records are in the table with the supplied condition. In the last case, one reference is " As b", the other reference, by default, is the table name itself: in the query designer, if you have the "properties sheet" visible, click on the table (on the top half portion, the "graphical" portion of the designer) and the properties sheet should said "Alias" in the first line (there is only two properties, for a table). As you see, the table name is automatically used as "alias". If you change it for " a ", then, type: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid) because "a" is now the name of one of the "reference" . Since the ( ) around the SELECT acts like a "scope", and since " AS b" is defined within this scope, you cannot refer to b outside that scope. On the other hand, " as a " is define more externally (as you can see in the main FROM clause, in SQL view) and thus you can referred to that reference anywhere within that scope, including in the innermost scope ( SELECT.... ) For references on SQL? here, sure, and some books, like http://www.amazon.ca/exec/obidos/ASI...583917-4704004 Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#9
|
|||
|
|||
It is getting closer. That time the query assigned unique numerical values
into the new column for each unique siteiid value. However, it looks like it assigned the values to every row, or unique siteiid value, in the original table (over 900!!). Therefore, the new column (expr1) has values that read 124, 158, 405, 514 for the first 4 unique siteiid values in the queried subset, rather than 1, 2, 3, 4. I imagine there is a way to do this within the function used so far, but am at a loss. Access help menu is weak, by the way. Thanks again for the help Michel Walsh" wrote: HI, You are right, missing the table name: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid) the " AS a " and the " AS b " are meant to be "alias" for a "reference" to the table. Indeed, we need to reference the table twice, once to get the final result, once to count how many records are in the table with the supplied condition. In the last case, one reference is " As b", the other reference, by default, is the table name itself: in the query designer, if you have the "properties sheet" visible, click on the table (on the top half portion, the "graphical" portion of the designer) and the properties sheet should said "Alias" in the first line (there is only two properties, for a table). As you see, the table name is automatically used as "alias". If you change it for " a ", then, type: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid) because "a" is now the name of one of the "reference" . Since the ( ) around the SELECT acts like a "scope", and since " AS b" is defined within this scope, you cannot refer to b outside that scope. On the other hand, " as a " is define more externally (as you can see in the main FROM clause, in SQL view) and thus you can referred to that reference anywhere within that scope, including in the innermost scope ( SELECT.... ) For references on SQL? here, sure, and some books, like http://www.amazon.ca/exec/obidos/ASI...583917-4704004 Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
#10
|
|||
|
|||
Pardon me for jumping in, but I think Michel might be taking a break.
You need to limit the subquery to the same set of records that are in the main query. So, can you copy and post the SQL of your query? (Possibly unneeded instructions follow) Open the query Select View:Sql from the Menu Select all the text Copy it Paste it into the message Basically, you need to add the same criteria to SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid as are in your main query. (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid AND b.SomeOtherField = SomeValue) Giz wrote: It is getting closer. That time the query assigned unique numerical values into the new column for each unique siteiid value. However, it looks like it assigned the values to every row, or unique siteiid value, in the original table (over 900!!). Therefore, the new column (expr1) has values that read 124, 158, 405, 514 for the first 4 unique siteiid values in the queried subset, rather than 1, 2, 3, 4. I imagine there is a way to do this within the function used so far, but am at a loss. Access help menu is weak, by the way. Thanks again for the help Michel Walsh" wrote: HI, You are right, missing the table name: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = site.siteiid) the " AS a " and the " AS b " are meant to be "alias" for a "reference" to the table. Indeed, we need to reference the table twice, once to get the final result, once to count how many records are in the table with the supplied condition. In the last case, one reference is " As b", the other reference, by default, is the table name itself: in the query designer, if you have the "properties sheet" visible, click on the table (on the top half portion, the "graphical" portion of the designer) and the properties sheet should said "Alias" in the first line (there is only two properties, for a table). As you see, the table name is automatically used as "alias". If you change it for " a ", then, type: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = a.siteiid) because "a" is now the name of one of the "reference" . Since the ( ) around the SELECT acts like a "scope", and since " AS b" is defined within this scope, you cannot refer to b outside that scope. On the other hand, " as a " is define more externally (as you can see in the main FROM clause, in SQL view) and thus you can referred to that reference anywhere within that scope, including in the innermost scope ( SELECT.... ) For references on SQL? here, sure, and some books, like http://www.amazon.ca/exec/obidos/ASI...583917-4704004 Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, That doesn't really work either. A new column is created in the query, but with just one, identical value populated in each row in the column, that value being the count of rows in the original "site" table that is being queried. Conversely, while I am admittedly a novice at query design and expressions, the original code (the first suggestion) appears to require a table "a" in the initial part of the statement "SELECT a.*(SELECT....". Is this the case, or am I missing something? Is there any method for getting help on functions (i.e. RANK, SELECT, COUNT, etc.) and their required arguements, syntax, etc.??? I have tried Access help but can't seem to get anywhere. This would help my situation a lot. thanx again for any help. "Michel Walsh" wrote: Hi, Should preferably be typed in SQL view. If you wish to type it from the query designer, that could be something like: Expr1: (SELECT COUNT(*) FROM site AS b WHERE b.siteiid = siteiid) and, in the line Sort, ask that expression to be ordered. You can change Expr1: by Rank: to get, in the result, the field name Rank, rather than the field name Expr1. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message news Hi Michel, I attempted to do this, and the expression begins to run, but then interupted with the following error message in a dialog box; "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field." I tried to locate in the help menu any assistance on the syntax and arguement for the FROM function, with little success. My expression, in the "Field" cell in the new column in the query design view, reads like this; Expr1: (SELECT a.*, (SELECT COUNT(*) FROM site As b WHERE b.siteiid = a.siteiid) as Rank FROM site As a ORDER BY a.siteiid) Help!! Giz "Michel Walsh" wrote: Hi, You can rank your data. Assuming you have a field (or a list of fields) that uniquely define who is first, who is second, etc, we can compute that "rank". If DateTimeStamp is that field, as example, then SELECT a.*, (SELECT COUNT(*) FROM myTable As b WHERE b.DateTimeStamp = a.DateTimeStamp) as Rank FROM myTable As a ORDER BY a.DateTimeStamp That assumes you do not have duplicated values. Hoping it may help, Vanderghast, Access MVP "Giz" wrote in message ... Hi, I am relatively new to Access and was hoping someone could help me out. I have created a query that accumulates various columns of information from different tables. In this query the user selects, as criteria for a "symbol" field, a symbol of interest, for subsequent export of the accumulated information for that specific symbol into excel. However, before export I would like to add a column to the query (in query design view, I think) that will contain a number for every row in the query, listed sequentially. In other words if the query results in 20 rows of information, I would like a column that lists 1 to 20. Is there any way to do this in the query design view, either with an expression or otherwise?? Any help would be appreciated |
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
adding 2 fields including null entries | Jesse | Running & Setting Up Queries | 26 | January 18th, 2005 05:31 PM |
crosstab query column headers do not match data in cells | martyc | Running & Setting Up Queries | 1 | October 27th, 2004 10:06 PM |
Adding a ' to the front of a column of numbers | punter | General Discussion | 4 | August 20th, 2004 08:43 PM |
Adding a column of numbers | Worksheet Functions | 3 | April 27th, 2004 05:36 PM |