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
|
|||
|
|||
Counting in a query
Who can give me a hand?
I posted the question to the Dutch groups as well but got no answer that helps me further. In a table I have some fields with the values 1, 2 and 3 for respectively "Yes", "No" and "No opinion". I would like to create a query that gives me one record in which the values 1, 2 and 3 are counted e.g. Key: Yes No No opinion 407 4 5 3 A cross tab query does that but not in one row (record). Should I use Dcount? Thanks for any suggstions/solutions. Ron |
#2
|
|||
|
|||
Counting in a query
RoBo wrote:
Who can give me a hand? I posted the question to the Dutch groups as well but got no answer that helps me further. In a table I have some fields with the values 1, 2 and 3 for respectively "Yes", "No" and "No opinion". I would like to create a query that gives me one record in which the values 1, 2 and 3 are counted e.g. Key: Yes No No opinion 407 4 5 3 A cross tab query does that but not in one row (record). Should I use Dcount? If I understand you correctly (table structure would have helped) Select Key ,Sum(iif([fieldname]=1,1,0)) As Yes ,Sum(iif([fieldname]=2,1,0)) As No ,Sum(iif([fieldname]=3,1,0)) As No Opinion From table Group By Key -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#3
|
|||
|
|||
Counting in a query
Hello Bob,
Thanks for your quick response. Do I understand correctly that I should create an SQL query that does the trick? The table looks like this: field Key, AutoNumber field "Description", Text field "Complex number", Text field "Postal code and house number", Text field "Info", Number (values 1, 2 or 3) field "Personnel:, Number (values 1, 2 or 3) and some more fields with the same structure The results of the query should be presented in a subform. The subform is linked to the field "Complex number", obtained from the main form; only the statistics of that specific complex should be shown. The query should return a record based on the linked field "Complex number". How do link the SQL code to the linked field of the main form? Ron "Bob Barrows [MVP]" wrote: RoBo wrote: Who can give me a hand? I posted the question to the Dutch groups as well but got no answer that helps me further. In a table I have some fields with the values 1, 2 and 3 for respectively "Yes", "No" and "No opinion". I would like to create a query that gives me one record in which the values 1, 2 and 3 are counted e.g. Key: Yes No No opinion 407 4 5 3 A cross tab query does that but not in one row (record). Should I use Dcount? If I understand you correctly (table structure would have helped) Select Key ,Sum(iif([fieldname]=1,1,0)) As Yes ,Sum(iif([fieldname]=2,1,0)) As No ,Sum(iif([fieldname]=3,1,0)) As No Opinion From table Group By Key -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#4
|
|||
|
|||
Counting in a query
RoBo wrote:
Hello Bob, Thanks for your quick response. Do I understand correctly that I should create an SQL query that does the trick? Yes. The table looks like this: field Key, AutoNumber field "Description", Text field "Complex number", Text field "Postal code and house number", Text field "Info", Number (values 1, 2 or 3) field "Personnel:, Number (values 1, 2 or 3) and some more fields with the same structure The results of the query should be presented in a subform. The subform is linked to the field "Complex number", obtained from the main form; only the statistics of that specific complex should be shown. The query should return a record based on the linked field "Complex number". How do link the SQL code to the linked field of the main form? Well, it appears that Key is no longer relevant, correct? So create a query using my initial suggestion, only group by Complex Number rather than Key. Save the query and bind the subform to the saved query "Bob Barrows [MVP]" wrote: RoBo wrote: Who can give me a hand? I posted the question to the Dutch groups as well but got no answer that helps me further. In a table I have some fields with the values 1, 2 and 3 for respectively "Yes", "No" and "No opinion". I would like to create a query that gives me one record in which the values 1, 2 and 3 are counted e.g. Key: Yes No No opinion 407 4 5 3 A cross tab query does that but not in one row (record). Should I use Dcount? If I understand you correctly (table structure would have helped) Select Key ,Sum(iif([fieldname]=1,1,0)) As Yes ,Sum(iif([fieldname]=2,1,0)) As No ,Sum(iif([fieldname]=3,1,0)) As No Opinion From table Group By Key -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
#5
|
|||
|
|||
Counting in a query
Hello Bob,
Thank you very much; your suggestion works fine. This was exactly what I needed. Have a nice weekend, with best regards, Ron "Bob Barrows [MVP]" wrote: RoBo wrote: Hello Bob, Thanks for your quick response. Do I understand correctly that I should create an SQL query that does the trick? Yes. The table looks like this: field Key, AutoNumber field "Description", Text field "Complex number", Text field "Postal code and house number", Text field "Info", Number (values 1, 2 or 3) field "Personnel:, Number (values 1, 2 or 3) and some more fields with the same structure The results of the query should be presented in a subform. The subform is linked to the field "Complex number", obtained from the main form; only the statistics of that specific complex should be shown. The query should return a record based on the linked field "Complex number". How do link the SQL code to the linked field of the main form? Well, it appears that Key is no longer relevant, correct? So create a query using my initial suggestion, only group by Complex Number rather than Key. Save the query and bind the subform to the saved query "Bob Barrows [MVP]" wrote: RoBo wrote: Who can give me a hand? I posted the question to the Dutch groups as well but got no answer that helps me further. In a table I have some fields with the values 1, 2 and 3 for respectively "Yes", "No" and "No opinion". I would like to create a query that gives me one record in which the values 1, 2 and 3 are counted e.g. Key: Yes No No opinion 407 4 5 3 A cross tab query does that but not in one row (record). Should I use Dcount? If I understand you correctly (table structure would have helped) Select Key ,Sum(iif([fieldname]=1,1,0)) As Yes ,Sum(iif([fieldname]=2,1,0)) As No ,Sum(iif([fieldname]=3,1,0)) As No Opinion From table Group By Key -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM" |
Thread Tools | |
Display Modes | |
|
|