Counting in a query

[RoBo]

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

 

[Bob Barrows [MVP]]

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

 

[RoBo]

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

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"

 

[Bob Barrows [MVP]]

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

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"

 

[RoBo]

Hello Bob,

Thank you very much; your suggestion works fine. This was exactly what I
needed.

Have a nice weekend, with best regards, Ron

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

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"