473,320 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Counting yes tick boxes

A little background.

I have a database to record newspaper deliveries. To enter a new order, you input the customer details etc, and then there are 7 tick boxes in the table, one for each day. The user will tick the box to indicate the customer wants the paper delivered that day. So for example if the customer wants the Guardian mon-fri you would put in their details, then the newspaper title then tick the monday, tuesday, wednesday, thursday, friday boxes.

To calculate the invoice in a report I need to multiply the number of times the paper was delivered in a week, by the cost of the paper. But to do this I need to sum the number of yes ticks per record. The count function only lets me count by field though. So I would end up with the number of yes ticks in the monday field, which is useless to me.

I hope this is clear enough, and would appreciate any help! Thank you!
Nov 29 '11 #1

✓ answered by TheSmileyCoder

You can sum the fields directly in your reports underlying query.
Amount: -(FieldMonday+FieldTuesday+...)
For this to work, the field needs to have a default value of 0 (False), because if the fields have no default they will be Null which cannot be added together.

The - in front is because TRUE is represented as -1. So a sum of all the true values for delivery monday to friday would -5, giving 5 in the example shown.

10 2328
TheSmileyCoder
2,322 Expert Mod 2GB
You can sum the fields directly in your reports underlying query.
Amount: -(FieldMonday+FieldTuesday+...)
For this to work, the field needs to have a default value of 0 (False), because if the fields have no default they will be Null which cannot be added together.

The - in front is because TRUE is represented as -1. So a sum of all the true values for delivery monday to friday would -5, giving 5 in the example shown.
Nov 29 '11 #2
Mihail
759 512MB
Or design a sum query for each day and another one, based on first five, to sum all days.
Nov 30 '11 #3
johny10151981
1,059 1GB
if your days type is all enum('0','1')

then the query can be
Expand|Select|Wrap|Line Numbers
  1. SELECT d1+d2+d3+d4+d5+d6+d7 FROM abcd;
This code will also work too if enum('y','n')

Expand|Select|Wrap|Line Numbers
  1. SELECT (d1='y')+(d2='y')+( d3='y')+( d4='y')+( d5='y')+( d6='y')+( d7='y') FROM abcd;
  2.  
Nov 30 '11 #4
NeoPa
32,556 Expert Mod 16PB
Johnny:
Expand|Select|Wrap|Line Numbers
  1. SELECT (d1='y')+(d2='y')+( d3='y')+( d4='y')+( d5='y')+( d6='y')+( d7='y') FROM abcd;
Not forgetting the point Smiley made in post #2 that True resolves to -1 so this should be :
Expand|Select|Wrap|Line Numbers
  1. SELECT -(d1='y')-(d2='y')-( d3='y')-( d4='y')-( d5='y')-( d6='y')-( d7='y') FROM abcd;
Nov 30 '11 #5
Thank you very much, that certainly helped me a lot! After about 5 hours work, the invoice system works perfectly!
Nov 30 '11 #6
johny10151981
1,059 1GB
conditional statement always return either 1 or 0. I cannot expect a -1 in condition, if -1 returns then it must have to be a bad design
Nov 30 '11 #7
NeoPa
32,556 Expert Mod 16PB
No. That's just not true Johnny.

Conditional statements can work with any numeric values. Zero is always treated as a False value and anything else causes the True path to be taken.

I suspect you meant to say that boolean expressions (such as [d1]='y' for instance) always return either a 1 or a 0. This is also quite wrong. They return either the value False, which is numerically equivalent to 0, or the value True, which is indeed numerically equivalent to -1 as stated earlier.

The reason that True is set to -1 rather than 1, is that boolean arithmetic and logical expressions would not work correctly unless all bits in the value were ones (which is the case with -1 of course). Consider the value returned if you tried to say Not True if True were simply 1? The returned value would not be False (or zero), but rather -2 (all ones except the logically rightmost binary digit).

It's generally worth doing a little bit of research before making a public claim that somebody else is wrong. It can help to save face even if you're not worried about manners ;-)
Nov 30 '11 #8
Mihail
759 512MB
@johny10151981
Long time ago I had the same trouble with this boolean values.
So you must update your mind to FALSE = 0 and TRUE = -1
Keep in mind: If you use other environment to develop an application first verify the numeric value for TRUE and FALSE.
Dec 1 '11 #9
johny10151981
1,059 1GB
ok,
I didn't get any documentation related with mysql return -1 on condition being true

Anyway, I am withdrawing my speech.

But C always return 1 if condition true otherwise 0


Now I noticed, this question is related with Access!!!!!!!!!!!!!!!!!!!!!!!!
Dec 1 '11 #10
NeoPa
32,556 Expert Mod 16PB
Indeed you're right Johnny. C indeed evaluates comparison expressions to 1 (True) or 0 (False) and, what is strange at least to me, as integer types. I can understand a BIT value of 1 or 0, but I'm confused that an integer would work that way. I expect the compiler must include logic to ensure any such tests ignore all other bits and only ever deal with bit 0 (Rightmost).

As you rightly say, Access handles it quite differently.
Dec 1 '11 #11

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: aaj | last post by:
Hi all I have a continuous bound form and on each record is a tick box. The user ticks the boxes and these boxes define the batch. for future operations before they leave the page I count...
11
by: chrisc | last post by:
Hello, My infamous manufacturing database continues to bane my life, mostly because I'm really stumbling around in the dark here! What I am trying to do is populate some fields automatically...
5
by: John | last post by:
Hi I have a bound gridview. I need to allow users to select multiple rows and then press the process button to process multiple selected rows. For this I want to use additional column of...
2
by: Dixie | last post by:
I have a table with records where each record has 14 tick boxes. Simply speaking, I want a report which shows each record for which any of the tick boxes is not ticked. It sounded simple until I...
1
by: NeedHelpFast | last post by:
Hi there I have a system that pulls information out of a database and replaces some of the words with text boxes. I am struggling trying to find out the number of text boxes contained on a page...
4
by: questionit | last post by:
Hi I have 2 tick boxes (say A and B) and a list box. I want following, can anyone help If tick-box A is checked: the list box will show all of its values If tick-box B is checked: the list...
1
by: Inaccessable | last post by:
This is probably very simple but still beyond me. I have a table that stores text in a number of different languages. Each entry has a unique ID number. Each language version of the same text...
18
by: sweeneye | last post by:
Hi, I'm basing a query on the variables used in a form. The database contains lots of problems, say with a computer and a tick box for the apropriate component like monitor, keyboard, mouse etc....
31
by: Sophie Mess | last post by:
Hi, I've been tasked to designing a form for 'students' to use to submit which courses to take at school. The courses are split into those for Semester 1 and Semester 2 and 3(max) can be chosen...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.