Results 1 to 1 of 1
2001-09-21, 19:11 #1jeffhookGuest
Time-Out for all You Hard-Working Access Geniuses (Works Database)
I hope the punishment for querying the sage counselors of this group about separate applications is not too draconian. I trust you'll be able to excuse me when you understand my motive. My previous Access inquiry here showed me what a superlative resource this board is, and my current question, although it relates to the crude "flat file" of Works 6.0's database, IS at least database-related. I've become so impressed by the sheer expertise on display here that I thought a few of you might wish to "clean your palates," so to speak, between more substantive and arcane Access queries. Even if you have no direct knowledge of Works 6.0's database, I'm sure whatever Access-based comments you make will be relevant to my application. I suspect my problem is a very elementary one.
I wrote my first-ever database formula this afternoon, so I'm an utter novice at formulae. I've "noodled around" in Works 6.0's crude database for the last couple of years, so I'm not completely unfamiliar with the application. In my use of previous Works databases I actually manually ran tallies of data myself, by adding them on the Windows Calculator, and by then copying the results to the relevant field of the database. I'm now ready at last to "move into
the big time" (so to speak...) and to have these simple calculations done automatically for me by the database application itself.
I have a nice form for new employment which I just obtained. It will serve as a "user-friendly" means of entering data in a 50-field database which records my pay earned, expenses incurred, time invested, pay received, expense reimbursements received, etc. . Several of my fields are intended to show me a CUMULATIVE TOTAL of all the data which are entered both in all preceding records and in the instant record for a particular field. For example, I indicate a dollar value on each form in a field for "Day's Pay Earned." I then wish to see in an associated field a "running total" (a "cumulative total") of all such data from all previous forms/records, and including the instant record's entry. Such a field would simply "add up" all the numerical values which are stored in that "Day's Pay Earned" field on all the records, including the instant record. The total would then be reported to me, for example, as the "Total Pay Earned to Date." (My actual field names are shorter; these are the labels which I use on my form in place of the hidden field names. Of course I've used the precise field names in the "intra-record" formulae which I've written so far, and which work very nicely.) This seems to be a very simple and basic task, relative to the trigonometric formulae and the financial calculations which I see are available in this application, but I can find absolutely no indication at all that such a modest achievement is even possible! Formulae which I've written to perform arithmetic functions (addition and multiplication) among fields on the same record work nicely, but I have no idea how to "tunnel down" through the "stack" of records and to extract a cumulative total from all the records.
How do I obtain a cumulative-total-to-date of all data in a given field for the entire database, from the first record to the present record? I'm not talking about an "intra-record" arithmetic function which would be performed among the data from fields which are together on one record. I'm talking about adding all the values in the "stack" of preceding records to the value in the instant form's records and then reporting the total to the "cumulative total" field. I know absolutely nothing at all about spreadsheets but isn't there a related "SUM" feature in spreadsheets? How do I perform a comparable function in a database?
Thanks very much for your kind help.
Sergeantsville, NJ, USA