Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    10

    Unanswered: Form Calculations

    My database contains information about daily logs. We currently do test to these pieces. If there is 1-500 parts in the lot we do 3 samples, five tests each. If there is 500+ in a lot we do 13 samples, 5 readings each. Currently I am using calculations in my form to generate an average for these 5 readings in one sample. Then at the end I am calculating overall average using the numbers from the averages of teh samples over the number of samples performed (an input). At the end is a report which asks for the overall average (Lot average) of the samples. Well I'm trying to create a way to calculate this because in my current state, the averages do not enter themselves into the table. Well I'm trying to do either one of two things.
    1) instead of having the average equation in expression builder within the controls source, I create a new expression in either the builder or VBA to calculate the average and input it onto the table.
    OR
    2) Within the report a query is used for its source. In the Query is the Lot average. Well I was hoping to bring in all 65 fields (13 strings of 5 fields) into the query, but it was too large. I was hoping next to create the averages (all 13) within the Query and only display the calculation for those 13. Well in the second scenario, the report would keep propmpting me for the average ( average1, average2, etc) so it could create the lot average.

    Can someone suggest a code for either way, or a suggestion that will work. Its kinda confusing, but if more data is needed, I gots it.
    -CLAYDOS

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Form Calculations

    I'm a bit confused here. Are you inquiring about aggregate functions in a form, or a report? The title is "form calculations" but you keep saying report...

    Either way, this isn't to difficult. If it is a report, simply utilize the grouping levels to generate the aggregates and perform the final calculation by reference controls in the report footer, not the field in the dataset.

    If it's a form, then very simple code can calculate this for you. The exact route to take will depend on exactly what you need this to do.

  3. #3
    Join Date
    Dec 2003
    Posts
    10

    Re: Form Calculations

    Well, the main goal is to print out a report with those calculations in place. I can either go the route of entering data into the form, and in either VBA or expression code can calculate the value and dump that value into the table or I can enter the calculation in a query. If I go the route of the query, I cannot create on lump expression to calculate the numbers. If I use seperate expressions, called averages, which has each sample in it ( an average of 5 results), and I preview teh report, it keeps asking me for those averages.

    I can go either way, but I prefer to have the data be calculated in the form and dumped into the table, then I can do a simple expression to reference to the table with all the data in it.
    -CLAYDOS

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Form Calculations

    Originally posted by claydos
    Well, the main goal is to print out a report with those calculations in place. I can either go the route of entering data into the form, and in either VBA or expression code can calculate the value and dump that value into the table or I can enter the calculation in a query. If I go the route of the query, I cannot create on lump expression to calculate the numbers. If I use seperate expressions, called averages, which has each sample in it ( an average of 5 results), and I preview teh report, it keeps asking me for those averages.

    I can go either way, but I prefer to have the data be calculated in the form and dumped into the table, then I can do a simple expression to reference to the table with all the data in it.
    Use unbound controls on a form. Then use a button to perform your calculations and execute an insert statement using those values. Otherwise with the report approach, you can have the report itself do all the math for you. It sounds like you are trying to do everything in the query, that's the hard way.

  5. #5
    Join Date
    Dec 2003
    Posts
    10

    Re: Form Calculations

    Originally posted by Teddy
    Use unbound controls on a form. Then use a button to perform your calculations and execute an insert statement using those values. Otherwise with the report approach, you can have the report itself do all the math for you. It sounds like you are trying to do everything in the query, that's the hard way.

    I'll try it that way and see what happens. I figured that I would do it the hard way. Its the only realy way I know.
    -CLAYDOS

  6. #6
    Join Date
    Dec 2003
    Posts
    10

    Re: Form Calculations

    Originally posted by claydos
    I'll try it that way and see what happens. I figured that I would do it the hard way. Its the only realy way I know.
    Sorry again. I got the VBA code to work to calculate it, but I can't seem to get the insert statement to work correctly. Right now, I'm testing it with a small table and form to figure out what to do. I have 5 fields, 1,2,3,4,& 5, then the Unbound field of Average.

    my VBA code is: me.average.value =((1+2+3+4+5)/5) ---> simplified
    but then the insert statement is confusing... any suggestions.
    -CLAYDOS

  7. #7
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Re: Form Calculations

    Originally posted by claydos
    Sorry again. I got the VBA code to work to calculate it, but I can't seem to get the insert statement to work correctly. Right now, I'm testing it with a small table and form to figure out what to do. I have 5 fields, 1,2,3,4,& 5, then the Unbound field of Average.

    my VBA code is: me.average.value =((1+2+3+4+5)/5) ---> simplified
    but then the insert statement is confusing... any suggestions.
    What all are you inserting? Please provide a precise explanation of how you would like this too work.

  8. #8
    Join Date
    Dec 2003
    Posts
    10

    Re: Form Calculations

    Originally posted by Teddy
    What all are you inserting? Please provide a precise explanation of how you would like this too work.
    I would like this to work in that when the operator of this program uses the program, all he/she has to do is enter data and press a button to add the record to the table. Within this "add" button, I can insert the line to insert the average of the 5 numbers into the table. So far the table has the 5 numbers I entered, without the average. I just need that average in there and I can get the rest of my database rolled out. This is the "Hill" of the database. Thanks
    -CLAYDOS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •