Please Help: How to execute code for all records?

[mjj1998]

Hi guys,

I have a form ("InfoEntry") that gathers data (a "weight" and
"quantity" field) via user input and plugs these values into an
equation that figures out a total charge. The result is stored in
another field ("charge") within the same record. Each time the user
creates a new record and enters in the data, the charge is calculated
for that record.

I'm going to shift the user entry process to a PDA/mobile device that
will allow users to input the "weight" and "quantity" information when
away from the PC - with the expectation that at the end of the day the
information will be imported into Access. I have the data importing
into a separate table ("daily import"), however I now need to run my
equation on each these new records (I can't run the "charge" equation
on the PDA).... So, I'll need to run the equation which takes values
from "weight" & "quantity" and store the result ("charge") in another
field for *all* the existing records imported (on a record-by-record
basis). This will happen before I finally append these new daily
records to the "main" table.

I was hoping someone might be able to give me some pointers on how to
actually construct the mechanism that I need to be able to do this? I'm
thinking that I'll need to use the MODULES section, but I don't know
where to begin. I have used the VB code editor to run the equation in
my InfoEntry form (as each new record is created), but I'm not sure how
much of this knowledge can be applied to the MODULES section??

Any help would be greatly appreciated
Thanks in advance!

 

[Arvin Meyer [MVP]]

In general, it is unwise to store the result of a calculation in a table
along with the elements of that calculation. After all, you can always
recalculate the result. In fact, Relational database Normalization rules
forbid it except when needed for history. If you really still need to do it,
add a column to the input table and then run an update query to do the
calculation and fill the column.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Douglas J Steele]

You shouldn't be storing calculated fields in tables.

As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."

What you should do is create a query that has a computed field representing
the calculated value and use the query wherever you would otherwise have
used the table.

 

[Mjj1998]

Hey guys,

Thanks for the replies - I suppose that's what happens when you learn
Access "on the fly" and are aiming to only achieve very specific goals!


I understand what you're both saying, so I'll look into creating the
query instead....I hope it isn't going to change the structure too much
(I can't help picturing a house of cards here! LOL ), but it does
sound like a worthwhile investment of time. The only problem I can see
is that my equation is actually based on an "if" statement, which can
satisfy 1 of 3 different conditions per record (each condition has a
unique equation). Can this still be achieved with a single query?

Cheers,
MJ

 

[Douglas J. Steele]

Depends on the actual comparisons, but yes, you should be able to do it
using nested IIf statements.

 

[Arvin Meyer [MVP]]

In addition to Doug's reply, you can build a VBA function in a standard
module based on If ... Then or Select Case and use your function in a query.
As long as you don't intend converting that query to a stored procedure in a
server based database, it should suffice for any possible usage within
Access.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access