Simple Calculation

[Dave Titan]

I've a massive table (I've only just learnt how to break tables up and relate them, but for know I have to stick with this old table)

To sum it up:

44 Affiliates
12 sports

Each sport costs 75

I've a tick box next to each sport in the affiliate table/form to indicate that they do that sport.

I'd like each tick box to be the value of 75. and have a total field then per affiliate to see how much they owe?

I don't know VB(night course starts next month), but could someone help with this?

Alternatively can someone show me how to add up fields and make a totals column??

Thanks

 

[Travis]

You can do this with a Query:

I'm Assuming that the Check boxes for each of the Sports is represented on the table with a Yes/No field.

In MS Access True/Yes=-1
False/No=0


Query:

Select *,(ABS([FootBall])+ABS([BaseBall])+ABS([FieldHockey]))*75 as [MyTotal] From [MyTable]

Using the ABS or Absolute value you convert -1 to 1 giving you a count of sports, then multiply that count of sports by the dollar amount will give you the total for this record.


Note:
It would have been normalized if the list of Sports would have been a seperate table linked to the Main record. You could have then just have gotten a count of Sports Records and multiplied them by the Dollar Amount and later when the requirements changed.

 

[Dave Titan]

Thanks Travis,

I get what your saying. But a little lost in how to actually implement it.

What is ABS?

.... Naw, actually on second thought I'm totally lost.

Right, Lets presume I have a new table. ID - Affiliates(there are 44) - Football - Hockey are the only fields. I'd like to count how many affiliates do Football. How would I do that? And have the number display in a seperate field in the query?

I know it's something to do with the count feature but it's more than what I've done before! But once I get my foot in the door I can usually figure out the rest.

 

[Travis]

First:

ABS = Absolute value (Layman it makes negative numbers positive and positive numbers positive)

Example SQL:

Count of Football:
----------------------
SELECT Count(tblSports.Affiliates) AS CountOfAffiliates
FROM tblSports
WHERE (((tblSports.Football)=True));

Count of Hockey:
----------------------
SELECT Count(tblSports.Affiliates) AS CountOfAffiliates
FROM tblSports
WHERE (((tblSports.Hockey)=True));


Count of FootBall and Hockey:
--------------------------------------
SELECT Count(tblSports.Affiliates) AS CountOfAffiliates
FROM tblSports
WHERE (((tblSports.Football)=True) AND ((tblSports.Hockey)=True));

 

[Dave Titan]

thanks again. Unfortuately I'm suffering from the problem "problem between keyboard and chair"!!!!

I'm starting again.

one table on it is

ID(autonum) - Affiliate(text) - Football(tick box) - Hockey(tick box)-totalfootbal(number) - totalHockey(number)

What is the easiest way to add up the two totals columns in figures?

If I can do this, I'll be on my way. I'm sure adding financial figues will be much the same.

Thanks again for the help

 

[Travis]

Are you saying you have a Table with the following fields

Table
----------------------
ID(autonum)
Affiliate(text)
Football(tick box)
Hockey(tick box)
totalfootbal(number)
totalHockey(number)


What is the totalfootbal and totalHockey for?

Are you trying to show a running sum on a report?

Are you trying to show a bottom line total?

I'm asking these questions because I thought I had answered your question. I know see that I didn't fully understand it.

 

[Dave Titan]

Sorry for the confusion.

In my last post I put in those tables so that if I knew how to add up a field so the total would appearing another field, then I'd be on my way to trying the more difficult things I first mentioned.

So, I guess the easiest was to ask it is. How do I add the numbers in a single column, and have the total appear in another?

Much like in an Excel sheet

 

[Travis]

Which Fields do you want to sum?

if it is the Total fields then simply create a query give the new field a name and set it equal to the formula:

TotalCnt:[totalfootbal]+[totalHockey]