Yes/No addition

[SajDGreat]

Hi, is there a way (in access 1997) to add up automatically all the yes/no a user has entered. basically i am making a database for a newsagent, and for each day the user wants a paper i click the yes/no for that chosen day. moving on from that i want the database to then calculate the toal number of ticks for that customer so that i can multiply the total number of ticks by the price of newspaper

so i want - number of ticks * newspaper price
___________________________________________________
Key
¬ = tick

Monday ¬

Tuesday

Wednesday ¬

Newspaper Price £0.35

Payment required ????????????
_______________________________________________


will the database be able to calculate the total number of ticks?? plz email me to help me im really stuck. if i cnt do this as a query i think il have to do it in VBA. email addy is = sajmulla@hotmail.com

 

[Rich]

=Sum(Abs([YourField))

 

[sdkramer]

you'll want to couple that with the on update or on change of the checkbox.

 

[SajDGreat]

OH MY GOD YOU LOT ARE STARS FOR REPLYING.

ive not got the database its in college but im gonna try it. you dnt no how thank ful i am, even my college teachers struggled to do this hopefully it will work.
if not is it ok if i email u ppl back using this forum. wens the best tym that you lot will be on.

 

[Mile-O]

you'll want to couple that with the on update or on change of the checkbox.

No you won't.

If it's the ControlSource of a textbox then it calculates automatically.

 

[SajDGreat]

hi regarding my question above i tried the formula that u told me however it doesnt work- it comes up with an error.

first i would lyk to ask that in the formula you sent

=Sum(Abs([YourField))

the part where it says ([YourFeild]) means the feilds in my database which will be ticked , e.g monday-sunday.

after this i typed in all the different days seperated by a comma however i got the error message sayin: -

cant have aggregate function in WHERE clause ([order for customer table]. Total Days] = Sum(Abs([Monday,Tuesday])))

please can you help me, i really need to do this. if you want i can send you my database via messenger. i appreciate any help

 

[Mile-O]

the part where it says ([YourFeild]) means the feilds in my database which will be ticked , e.g monday-sunday.

That's a design problem. Your database has a repeating group. If you want to know more about this then I can suggest having a read of this; I haven't written the rest yet but you can still search for information on normalisation and the normal forms.

However, if you aren't going to change your design then this should do it for you:

=Sum(Abs([Sunday])) + Sum(Abs([Monday])) + Sum(Abs([Tuesday])) etc...

 

[Mile-O]

Just realised this is in queries and not forms - the same logic applies though.

 

[SajDGreat]

Hi thanks for all your help, i read that article however i found it too advanced im just an average user and finding it difficult to complete my coursework. is it possible that i could send you my database. because when you are saying it is a design problem i still dont understand it. its urgent and i really need to sort it. this is my main query, i have jus finished my relationships and finished adding data into my tables. now i have got queries and forms left top do, a mail merge and then the write up. i thjink il be ok with that jus i dont know how to complete this query, i would appreciate if one of you could have a look at my database and help me to correct it.

 

[SajDGreat]

i tried the formula : - =Sum(Abs([Sunday])) + Sum(Abs([Monday])) + Sum(Abs([Tuesday])) u gave me however im still getting the same error message and need to sort it. even my teacher doesnt understand this. plz help

 

[Mile-O]

Post your SQL from the query's SQL View.

 

[Mile-O]

even my teacher doesnt understand this.

How does (s)he intend to mark your work then?

 

[SajDGreat]

Post your SQL from the query's SQL View.

sorry im not advanced i dnt know how to post the sql code or to access the code, the teacher sends the work of to a moderator. can i send the whole database via msn messenger? i also have another question, about passwords were shall i ask that questions.


_______________________________________________

one problem leads to another

 

[Mile-O]

Re: Post your SQL from the query's SQL View.

im not advanced i dnt know how to post the sql code or to access the code

In the Query Design Grid there's a button on one of the Toolbars which can be swapped between three different views: Design View, Datasheet View, and SQL View. Select the SQL View option, copy and paste the SQL here. Hey presto! You are advanced enough to post SQL code.

can i send the whole database via msn messenger?

No, for two reasons: a) I don't have MSN Messenger; and b) I'm at work and have my own databases to worry about.

i also have another question, about passwords were shall i ask that questions.

In the General Forum.