|
 |
access thread: SQL Statement with multiple Count queries
Message #1 by "Christopher Cote" <chrscote@9...> on Sun, 28 Oct 2001 01:48:23
|
|
Hi,
I'm having difficulty with an SQL statement for my program in Access. I
am trying to get a statement which will give me a count of the times one
checkbox is checked then another count of when another checkbox is
checked. These two checkboxes (binary fields) have nothing in common. Is
there a way to create an SQL statement which will give something similar
to the following:
SELECT Count(Field1=True) as Count1, Count(Field2=True) as Count2
Please let me know if this is even possible.
Chris
Message #2 by "Ian Ashton" <ian@c...> on Sun, 28 Oct 2001 12:23:37 -0000
|
|
Christopher,
Relying on the fact that True (Checked) in Access = -1, try the following:
SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
AS CountField2
FROM tblCountTry;
Ian Ashton
-----Original Message-----
From: Christopher Cote [mailto:chrscote@9...]
Sent: Sunday, October 28, 2001 1:48 AM
To: Access
Subject: [access] SQL Statement with multiple Count queries
Hi,
I'm having difficulty with an SQL statement for my program in Access. I
am trying to get a statement which will give me a count of the times one
checkbox is checked then another count of when another checkbox is
checked. These two checkboxes (binary fields) have nothing in common. Is
there a way to create an SQL statement which will give something similar
to the following:
SELECT Count(Field1=True) as Count1, Count(Field2=True) as Count2
Please let me know if this is even possible.
Chris
Message #3 by "Christopher Cote" <chrscote@9...> on Mon, 29 Oct 2001 01:11:14
|
|
Hi Ian,
Thank you very much for the help. I've been trying for weeks to figure
this one out. I would have never figured this one out. Now I can finally
finish this program I've been working on for months. I do have one
question before leaving this subject: How does this work? I mean, I would
think that it would still add up the ones where the field isn't selected.
Are non checked boxes zero then?
Chris
> Christopher,
>
> Relying on the fact that True (Checked) in Access = -1, try the
following:
>
> SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
> AS CountField2
> FROM tblCountTry;
>
>
> Ian Ashton
>
>
>
> -----Original Message-----
Message #4 by "Ian Ashton" <ian@c...> on Mon, 29 Oct 2001 07:17:43 -0000
|
|
In Access (and VB) True = -1 and False = 0
(In VB you need to be careful with a CheckBox on a Form as its Checked value
is +1)
Try this:
Go to the debug window (<Ctrl> and <G>
Type ?cint(True)
See the answer
Type ?cint(False)
See the answer
I hope this helps
Ian Ashton
-----Original Message-----
From: Christopher Cote [mailto:chrscote@9...]
Sent: Monday, October 29, 2001 1:11 AM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Hi Ian,
Thank you very much for the help. I've been trying for weeks to figure
this one out. I would have never figured this one out. Now I can finally
finish this program I've been working on for months. I do have one
question before leaving this subject: How does this work? I mean, I would
think that it would still add up the ones where the field isn't selected.
Are non checked boxes zero then?
Chris
> Christopher,
>
> Relying on the fact that True (Checked) in Access = -1, try the
following:
>
> SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
> AS CountField2
> FROM tblCountTry;
>
>
> Ian Ashton
>
>
>
> -----Original Message-----
Message #5 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 29 Oct 2001 07:55:57 -0800
|
|
Please forgive this nit-pick, but:
SELECT Abs(Sum(Field1)) AS CountField1, Abs(Sum(Field2))
AS CountField2
FROM tblCountTry;
Will work even if the value of True is ever changed to 1 (which was planned
for VB.NET, and then cancelled b/c of the outcry from dev's whose code
relied on the numeric value of True being -1).
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 4:24 AM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Christopher,
Relying on the fact that True (Checked) in Access = -1, try the following:
SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
AS CountField2
FROM tblCountTry;
Ian Ashton
-----Original Message-----
From: Christopher Cote [mailto:chrscote@9...]
Sent: Sunday, October 28, 2001 1:48 AM
To: Access
Subject: [access] SQL Statement with multiple Count queries
Hi,
I'm having difficulty with an SQL statement for my program in Access. I
am trying to get a statement which will give me a count of the times one
checkbox is checked then another count of when another checkbox is
checked. These two checkboxes (binary fields) have nothing in common. Is
there a way to create an SQL statement which will give something similar
to the following:
SELECT Count(Field1=True) as Count1, Count(Field2=True) as Count2
Please let me know if this is even possible.
Chris
Message #6 by "Derrick Flores" <Derrick_Flores@s...> on Mon, 29 Oct 2001 12:29:12 -0600
|
|
You can also do this by running a loop throught your recordset and count
all the checkboxes that are set to true
Dim db As Database
Dim rec As Recordset
Dim int1 As Integer
Dim int2 As Integer
Set db =3D CurrentDb()
Set rec =3D db.OpenRecordset("TableName")
Do Until rec.EOF
If rec("CheckBox1") =3D True Then
int1 =3D int1 + 1
End If
If rec("CheckBox2") =3D True Then
int2 =3D int2 + 1
End If
rec.MoveNext
Loop
MsgBox "Number of CheckBox1 is: " & int1 & " and the number of CheckBox2
is: " & int2
rec.close
db.close
Good luck,
Derrick A. Flores
Sony Semiconductor
Assoc. Systems Engineer
66318
>>> "Pardee, Roy E" <roy.e.pardee@l...> 10/29 9:55 AM >>>
Please forgive this nit-pick, but:
SELECT Abs(Sum(Field1)) AS CountField1, Abs(Sum(Field2))
AS CountField2
FROM tblCountTry;
Will work even if the value of True is ever changed to 1 (which was
planned
for VB.NET, and then cancelled b/c of the outcry from dev's whose code
relied on the numeric value of True being -1).
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 4:24 AM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Christopher,
Relying on the fact that True (Checked) in Access =3D -1, try the
following:
SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
AS CountField2
FROM tblCountTry;
Ian Ashton
-----Original Message-----
From: Christopher Cote [mailto:chrscote@9...]
Sent: Sunday, October 28, 2001 1:48 AM
To: Access
Subject: [access] SQL Statement with multiple Count queries
Hi,
I'm having difficulty with an SQL statement for my program in Access.
I
am trying to get a statement which will give me a count of the times
one
checkbox is checked then another count of when another checkbox is
checked. These two checkboxes (binary fields) have nothing in common.
Is
there a way to create an SQL statement which will give something similar=20
to the following:
SELECT Count(Field1=3DTrue) as Count1, Count(Field2=3DTrue) as Count2
Please let me know if this is even possible.
Chris
Message #7 by "Ian Ashton" <ian@c...> on Mon, 29 Oct 2001 20:21:02 -0000
|
|
Running loops through recordsets is VERY VERY slow compared to using a
suitable SQL statement.
(I have tried this more than once because I usually find VB code easier to
write than SQL).
Ian Ashton
-----Original Message-----
From: Derrick Flores [mailto:Derrick_Flores@s...]
Sent: Monday, October 29, 2001 6:29 PM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
You can also do this by running a loop throught your recordset and count all
the checkboxes that are set to true
Dim db As Database
Dim rec As Recordset
Dim int1 As Integer
Dim int2 As Integer
Set db = CurrentDb()
Set rec = db.OpenRecordset("TableName")
Do Until rec.EOF
If rec("CheckBox1") = True Then
int1 = int1 + 1
End If
If rec("CheckBox2") = True Then
int2 = int2 + 1
End If
rec.MoveNext
Loop
MsgBox "Number of CheckBox1 is: " & int1 & " and the number of CheckBox2 is:
" & int2
rec.close
db.close
Good luck,
Derrick A. Flores
Sony Semiconductor
Assoc. Systems Engineer
66318
>>> "Pardee, Roy E" <roy.e.pardee@l...> 10/29 9:55 AM >>>
Please forgive this nit-pick, but:
SELECT Abs(Sum(Field1)) AS CountField1, Abs(Sum(Field2))
AS CountField2
FROM tblCountTry;
Will work even if the value of True is ever changed to 1 (which was planned
for VB.NET, and then cancelled b/c of the outcry from dev's whose code
relied on the numeric value of True being -1).
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 4:24 AM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Christopher,
Relying on the fact that True (Checked) in Access = -1, try the following:
SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
AS CountField2
FROM tblCountTry;
Ian Ashton
-----Original Message-----
From: Christopher Cote [mailto:chrscote@9...]
Sent: Sunday, October 28, 2001 1:48 AM
To: Access
Subject: [access] SQL Statement with multiple Count queries
Hi,
I'm having difficulty with an SQL statement for my program in Access. I
am trying to get a statement which will give me a count of the times one
checkbox is checked then another count of when another checkbox is
checked. These two checkboxes (binary fields) have nothing in common. Is
there a way to create an SQL statement which will give something similar
to the following:
SELECT Count(Field1=True) as Count1, Count(Field2=True) as Count2
Please let me know if this is even possible.
Chris
Message #8 by "Pardee, Roy E" <roy.e.pardee@l...> on Mon, 29 Oct 2001 13:32:42 -0800
|
|
Hear, hear! SQL is your friend. And when you find yourself working with
server databases, you'll have the additional advantage of opting to let all
the work happen on the server.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Monday, October 29, 2001 12:21 PM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Running loops through recordsets is VERY VERY slow compared to using a
suitable SQL statement.
(I have tried this more than once because I usually find VB code easier to
write than SQL).
Ian Ashton
-----Original Message-----
From: Derrick Flores [mailto:Derrick_Flores@s...]
Sent: Monday, October 29, 2001 6:29 PM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
You can also do this by running a loop throught your recordset and count all
the checkboxes that are set to true
Dim db As Database
Dim rec As Recordset
Dim int1 As Integer
Dim int2 As Integer
Set db = CurrentDb()
Set rec = db.OpenRecordset("TableName")
Do Until rec.EOF
If rec("CheckBox1") = True Then
int1 = int1 + 1
End If
If rec("CheckBox2") = True Then
int2 = int2 + 1
End If
rec.MoveNext
Loop
MsgBox "Number of CheckBox1 is: " & int1 & " and the number of CheckBox2 is:
" & int2
rec.close
db.close
Good luck,
Derrick A. Flores
Sony Semiconductor
Assoc. Systems Engineer
66318
>>> "Pardee, Roy E" <roy.e.pardee@l...> 10/29 9:55 AM >>>
Please forgive this nit-pick, but:
SELECT Abs(Sum(Field1)) AS CountField1, Abs(Sum(Field2))
AS CountField2
FROM tblCountTry;
Will work even if the value of True is ever changed to 1 (which was planned
for VB.NET, and then cancelled b/c of the outcry from dev's whose code
relied on the numeric value of True being -1).
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-----Original Message-----
From: Ian Ashton [mailto:ian@c...]
Sent: Sunday, October 28, 2001 4:24 AM
To: Access
Subject: [access] RE: SQL Statement with multiple Count queries
Christopher,
Relying on the fact that True (Checked) in Access = -1, try the following:
SELECT -Sum(Field1) AS CountField1, -Sum(Field2)
AS CountField2
FROM tblCountTry;
Ian Ashton
|
|
|
View Cart
