Another Check Box Question

  • Thread starter Thread starter Teresa Anderson
  • Start date Start date
T

Teresa Anderson

OK here's the deal...In my query I have colmuns as follows...

Column 1 is for Zone
Column 2 is for Block Number
Column 3 is for GPM (gallons per minute)
Column 4 is my checkbox for On/Off

What I am trying to do is create a way to calculate how many gpm of water is
being used. I want to be able to put a check in the box when the water is
running in these zones/blocks and have some sort of way to total these gpm.

I was thinking that creating another column (5) showing Total and creating
some sort of function/expression there to make this calculation but I am at a
loss as how to do that.

Also, I have NO experience in creating expression in Access so any
"elementary" explinations would really help.
 
SELECT ZONE, Block, Sum(GPM) as TotalGPM
FROM YourTable
WHERE CheckboxName = True
GROUP BY Zone, Block

In the query grid
-- Add your table
-- Add the four fields
-- Select View: Totals from the menu
-- Change Group By to Sum under GPM
-- Change Group By to Where under the Checkbox field
-- Set Criteria to TRUE under the Checkbox field
-- Run the query.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks John...

I am not exactly sure what you mean in the top section.

In the 2nd section...
I get In the query grid

--Add your table (done that)
--Add the four fields (done that)

but I am not understanding the rest.

As I mentioned before...I'm am really NEW to Access, can you really detailed
with your explination?
 
The top section is what the SQL looks like if you write the SQL directly.
The Design view is just a way to let you click and point to build the query.

I don't know how to make the second part (using design view - the query
grid) much simpler, but I will try.

-- Add your table
-- Add the four fields

-- Select View: Totals from the menu
---- In the menubar at the top of the window click on view and then select
Totals from the list of items
----When you do so, the query grid will add a new row entitled Total: and
under each of the fields you will see the words Group By

-- Change Group By to Sum under GPM
---- Click on Group by under GPM
---- Select SUM from the drop down list (or just type SUM)

-- Change Group By to Where under the Checkbox field
---- Select WHERE from the drop down list (or just type WHERE)

-- In the Criteria "Cell" under the Checkbox field, type TRUE
-- Run the query

If you still can't do this, then I suggest (gently) that you will need to
take a course on using Access or get Access for Dummies or hire someone to
come in and help.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Thanks again John...I've done everything EXACTLY the way you said but it did
not work. I guess you are right...I will have to take some classes on
Access. Thanks so much for your patience with me and explaning it so even I
could understand.
 
Saying "It did not work" is not very descriptive of the problem.

Did you get an error message?
Did you get the wrong results? If so, why do you think they are wrong?

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I didn't get an error message...I just did not get the outcome I was looking
for.
 
Go into the SQL, copy, paste in a post so we can see what your query looks
like. Also post a sample of your results and point out where the error is.
 
If you didn't get the outcome you were looking for let me suggest that you
post a small sample of your records and what you want to see as the outcome
of that small sample. Also helpful would be the SQL statement.

Please 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

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
SELECT GPM.Zone, GPM.[Block Number], Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone, GPM.[Block Number];
 
SELECT GPM.Zone, GPM.[Block Number], Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone, GPM.[Block Number];
 
Open the table GPM in design view, click on field GPM and see what is the
datatype.
Also click on field On/Off and also post it's datatype.
--
KARL DEWEY
Build a little - Test a little


Teresa Anderson said:
SELECT GPM.Zone, GPM.[Block Number], Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone, GPM.[Block Number];
--
Teresa B Anderson


KARL DEWEY said:
Go into the SQL, copy, paste in a post so we can see what your query looks
like. Also post a sample of your results and point out where the error is.
Click to expand...
Click to expand...
 
Ok So you answered one of my two questions. I still don't know why you
don't like the data that was returned. And you didn't give me a small
sample of the data, and what you wanted returned from the data.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Zone Block Number GPM On/Off Total
B 01 224 No
B 02 113 Yes
B 03 102 Yes
B 04 93 No
D 05 128 No
D 06 81 Yes
D 07d 104 Yes
B 08 80 Yes
B 09b 80 No
B 10 134 No
C 11 53 No
C 12 141 No
B 13 121 No
B 14 89 No
B 15 120 No
B 16 51 No
B 17 40 No
E 18 96 No
A 20 176 No
A 21 83 No
A 22 93 No
A 23 44 No
B 24 125 No
A 25 143 No
A 26 117 No
A 27 78 No
A 28 88 No
A 29 160 No
E A 129 No
E B 131 No
E C 53 No
E D 48 No
E E 77 No
E F 93 No
D G 38 No
D H 59 No
D I 34 No
D J 105 No
D K 54 No
D L 81 No
D M 76 No
E Q 56 No
E R 56 No
E S 77 No
E T 75 No
D 07b 104 No
B 07b 104 No
John Spencer said:
Ok So you answered one of my two questions. I still don't know why you
don't like the data that was returned. And you didn't give me a small
sample of the data, and what you wanted returned from the data.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Teresa said:
SELECT GPM.Zone, GPM.[Block Number], Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone, GPM.[Block Number];
Click to expand...
Click to expand...
 
So is that the returned data or the records in the table. It appears to be
the records in the table. What do you want returned? Your query should
return every line where On/Off is Yes. If you want to report the total flow
for a specific zone, then drop [Block Number] from the query.

Requirements:
-- Total Flow for a zone
-- Flow should only be totaled if On/Off is True (checked)

SELECT GPM.Zone
, Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone

I'm sorry but I really don't understand what you are trying to accomplish.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Teresa Anderson said:
Zone Block Number GPM On/Off Total
B 01 224 No
B 02 113 Yes
B 03 102 Yes
B 04 93 No
D 05 128 No
D 06 81 Yes
D 07d 104 Yes
B 08 80 Yes
B 09b 80 No
B 10 134 No
C 11 53 No
C 12 141 No
B 13 121 No
B 14 89 No
B 15 120 No
B 16 51 No
B 17 40 No
E 18 96 No
A 20 176 No
A 21 83 No
A 22 93 No
A 23 44 No
B 24 125 No
A 25 143 No
A 26 117 No
A 27 78 No
A 28 88 No
A 29 160 No
E A 129 No
E B 131 No
E C 53 No
E D 48 No
E E 77 No
E F 93 No
D G 38 No
D H 59 No
D I 34 No
D J 105 No
D K 54 No
D L 81 No
D M 76 No
E Q 56 No
E R 56 No
E S 77 No
E T 75 No
D 07b 104 No
B 07b 104 No
John Spencer said:
Ok So you answered one of my two questions. I still don't know why you
don't like the data that was returned. And you didn't give me a small
sample of the data, and what you wanted returned from the data.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Teresa said:
SELECT GPM.Zone, GPM.[Block Number], Sum(GPM.GPM) AS SumOfGPM
FROM GPM
WHERE (((GPM.[On/Off])=True))
GROUP BY GPM.Zone, GPM.[Block Number];
Click to expand...
Click to expand...
Click to expand...
 
Back
Top