get a query to store data?

[Guest]

I need to find a way to combine a query with data entry on a form, and all of
the information from that form needs to be saved.

Basically, I'm setting up an end of the workday journal entry, where several
simple calculations are performed on data from several different tables
(simple sums or totals), but I also need fields for new data to be entered
(comments on the day's work, etc.) and all of this (the calculated data plus
new data) needs to end up in a separate table, if possible.

I'm not sure if this would be easiest in a form, or report?

Thanks for your help.
~Christine

 

[Guest]

Link the form to a table1, on the "OK" button on the form it can save the
data to a table1. It can also then run an append query to take the data from
table1 perform calculations possibly with data from another table2 and insert
it into a final table3 of results. At the end of the process have a delete
query delete the results in table1. Create reports off of the table3 if need
be.

 

[Guest]

Thanks for your help. To be sure I have this straight...

Table 1 would be populated by the 'new' data, linked to the form. The "OK"
button would be a control that would save that data to table1, and also run
the append calcuation queries from other tables and add all of these to a
Table3?

Can this be run automatically, including the delete query? (there will be
many users needing to do this)

 

[John Vinson]

I need to find a way to combine a query with data entry on a form, and all of
the information from that form needs to be saved.

Basically, I'm setting up an end of the workday journal entry, where several
simple calculations are performed on data from several different tables
(simple sums or totals), but I also need fields for new data to be entered
(comments on the day's work, etc.) and all of this (the calculated data plus
new data) needs to end up in a separate table, if possible.

I'm not sure if this would be easiest in a form, or report?

Thanks for your help.
~Christine

Could you explain WHY you feel the need to store calculated values?

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

That said... you can do it, if you insist; you'll need to have two
textboxes for each such field, one unbound with the calculated
expression, and the other (which can be left invisible if you wish)
bound to the table field. In the Form's BeforeUpdate event you can set
the value of each bound control to its corresponding calculated
control.

John W. Vinson[MVP]

 

[Guest]

Thank you! After speaking to my boss, he had some of the same concerns
(namely, the data corruption). Guess I was trying to make it too easy for my
colleagues!

Thanks again.

 

[John Vinson]

Thank you! After speaking to my boss, he had some of the same concerns
(namely, the data corruption). Guess I was trying to make it too easy for my
colleagues!

<g> Well, you can have both: a valid (non-redundant) data storage
model, and no extra work for your colleagues, if you set it up right!

John W. Vinson[MVP]