Calculated field help. tally points

[mobyecho]

Hi all,

Ok so i have a simple table with Date, Points Earned, Points Used and Client ID number. Id like to tally the points for each client ID, after each time they either use or earn points.
How do I do this?
Regards
Adrian

 

[Mile-O]

Not in a table. You do it in a query. Just create a new field in the query.

Something like:

Tally: [Points Earned] - [Points Used]

Database design expects that you calculate values at run time rather than store values in a table.

 

[mobyecho]

yeah I am not sure what the go is.

The task I need to do is -
This table will also need to provide a formula to keep a running total of the points
earned and used by ''each'' program member.

It cant be done in the table?

If I was to do it in query how would that code keep a running tally, it would just subtract one from the other, then next time the customer earns points or uses points.. the existing points do not exist. how can it keep the tally>?

Iam a total beginner at access so if i say something stupid.. well lol

 

[Mile-O]

This table will also need to provide a formula to keep a running total of the points earned and used by ''each'' program member.

It cant be done in the table?

No, it can't be done in a table. Stop looking at the table and thinking it looks like Excel and so must to things like Excel. It's not Excel. If it acted like Excel, we would just use Excel and Access would be redundant.

Access is a database. At table is for storing data. You can do calculations on the data in a database table, but you would do them in a query, a form, or a report. You don't store the result of a calculation in a table. A database table doesn't allow for functions.

how can it keep the tally>?

It's exactly as I said. Create a query. Do the calculation there.

 

[Brianwarnock]

I have a feeling that you are at cross purposes here.

I no longer have Access so cannot give an example but I think that mobyecho wants to add values to the data in the table , say a person has earned 50 points and that is in the table when he earns another 5 that needs to e added giving 55.

To do this requires an update query
I would have a form with 2 textboxes one for points earned , one for points used, both defaulting to 0, and a command button to run an update query to add them to the table.

Brian

 

[Mile-O]

See what you mean, Brian.

I wonder if I would personally do that or whether, as a manic data controller, I would envision a debit/credit system for points so as to always have a complete history/audit trail. (Me and bigger thinking. )

 

[Brianwarnock]

I agree mile-o, I was making the mistake of merely answering the question as I saw it rather than challenging the whole approach.

Brian

 

[mobyecho]

hmmmmmmmmmm

 

[mobyecho]

iam confused

 

[mobyecho]

i need to post a link, need 10 posts LOL

 

[mobyecho]

this is crazyyy mann

 

[mobyecho]

3 to go!!!!!!

 

[mobyecho]

getting there......

 

[mobyecho]

cool bananas !!!

 

[mobyecho]

ok lets see what we got here

 

[mobyecho]

hey well id like some help in doing this, I am still learning Access.
This is the table I have here in the picture ---
http://tinypic.com/r/2upb5uw/8

''This table will also need to provide a formula to keep a running total of the points earned and used by each program member''.


I need some instruction on how to do it, so i cant do it in the table? as the instructions say it has to show the running total in the table.. so i just tell my lecturer this cant be done?

I feel like doing bit of this :banghead:

Any help is very well welcomed!

 

[Brianwarnock]

Did my first post , post #5, not indicate a method to do this? I do however agree with Mile-O that the better approach to this sort of DB is to log every transaction and get current totals via a query, but instructors often have their own agenda even if it bears no relation to the real world.

Brian