Queries - New at this stuff (1 Viewer)

[SanHall]

I know it is bad to store a calculated field, but in my case I have to.
I have a field in a form that is connected to a query. I need the calculation stored to the underlying table each time the query is run. When I try to use the controlsource solution from microsoft Q109704, it does not work. Can anyone help.

 

[Dreamboat]

You may be making your first mistake by saying that you "have to" have that calculation in your table. Why do you have to?

 

[SanHall]

Let me be more specific.

I have a form that plant operators enter the amount of parts produced by a press. All they do is enter the parts produced number, the next field gives them a running total and a third field subtracts that running total from another value. It is the calculation of the running total, I would like to store. Because more than one department requires the information, if I could store the total each time the query is calculated it would be easier for everyone.

Hope you can still help. But if you say that it is bad practise, I will have to think of another way to get the data i want.

 

[David R]

A workaround to keep from storing the total (which is sometimes useful, as you mentioned) occurs to me:
How is your database set up? If you have a Presses table with PressID, PressName, PressSpecifications (etc) and then a Output table with a linked field for PressID, you can record the day and total for each Press in series.
It will look something like this:

Presses table:
PressID PressName PressWt PressPartNo
     1  Die-cutter   550    447-23-1
     2  Widget mold  325    234-47-3 

Output table:
OutputID PressID OutputTotal OutputDay
     1       1      5001     12/20/2001
     2       2      2374     12/20/2001
     3       1      5237     12/21/2001
     4       2      2452     12/21/2001

Then it should be a fairly trivial thing to pull totals data for the press you want, for the date range you want, in your query.

HTH,
David R


[This message has been edited by David R (edited 12-20-2001).]

 

[SanHall]

my tables are pretty much set up that way with an entry form for filling in the details. I have a table that lists all the presses and one table to store all the data that has been entered.

The only thing not working and I can't get to work is the running total field. Because of my inexperience in Access, I'm not sure what to put in the properties of the text box. I have set the control source property to the field in my table, and nothing. Am I missing something.???

 

[David R]

Ahh, I wasn't sure where you were running into trouble.

You'll want to click on View>Totals while in Query Design view. This will add a new row to your query builder.
Change the selected option in that drop down to 'Group By' for the PressID category, "Sum" for OutputTotal, and "Where" for OutputDay (I got those columns backward in my example). Under the criteria for OutputDay put Between [Enter Start Date:] And [Enter End Date:], or hard code it if you like.

Then when you run the query, it will prompt you for the beginning and ending days that you want data for. If you don't care and want a plant-wide total since inception, you can of course leave out this criteria.

You can also do your Grouping and Summing in the report itself, but the process is a bit more involved. Your mileage may vary.

Hope that helps,
David R