Newbie question about Looping through records

[JON JON]

Dear Experts,

I am trying to make a form that will calculate the amount of delivery
charges per item to be delivered. What I did is to put the total delivery
charges on the main form. My problem now is how to allocate the charges to
each individual item in the subform base on their weight. Please note that
the field for charges per item can not be a calculated field. It has to be
a value because it is needed to be stored in the database for use in the
report.

TIA

Jon-jon

 

[Douglas J. Steele]

Why does it have to be a stored value? You can create a query that includes
a calculated field, and use that query as the RecordSource for your report.

 

[JON JON]

Douglas,

Thanks for the reply. I don't want to sound stupid but as a beginner your
answer makes me more confuse. If I could stored it as a value it would be
better for me since I can manipulate the data easier. I need this project
done before the end of year so I guess I don't have much time to learn what
you had suggested.

I know it is possible by the use of VBA. I knew VBA but in excel so i
guess it could help me learn in Access. My idea is that I will store the
amount and the total weight in a variable. Then get the ratio and multiply
it to the individual weight of the item. My code would be something like
this

Sub myCodeName()
Dim N as Double, D as Double, Q as Double
N = Me!Amount.Value ' This is the field for the delivery charges
D = Me![mySubform]![Sum Weight].Value ' This is the footer of the sub
form
Q = N/D
'this where I want to right my code that will loop for each record in
the subform that will store in the field [Charges] = [Weight] * Q
End Sub

Please help me!

 

[Tom Wickerath]

Jon,

You should be able to create a calculated field in your subform's source query, which calculates
the freight for each detail item. It would look like this in query design:

Freight: Ccur([ItemWeight]*[Rate])

where ItemWeight is the field that includes the weight of each individual item and Rate is the
freight charge per unit weight. You can use a textbox in a report that sums the individual
freight charges that you display in a detail section.

You do not want to store the results of a calculation in a database. To do so violates both 2nf
(second normal form) and 3nf (third normal form) of database design. This is a very common error
that people who come to Access from Excel make on a regular basis, and the error that Doug was
trying to keep you from introducing into your database schema. You might ask "What's so bad
about storing calculated values"? I quote the following from Database Design expert Michael
Hernandez, in his paper titled Understanding Normalization:

"The most important point for you to remember is that you will always re-introduce data integrity
problems when you de-Normalize your structures! This means that it becomes incumbent upon you or
the user to deal with this issue. Either way, it imposes an unnecessary burden upon the both of
you."

You can download this paper from:

http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")

The paragraph I quoted is found on page 23.

Tom
_________________________________


Douglas,

Thanks for the reply. I don't want to sound stupid but as a beginner your answer makes me more
confuse. If I could stored it as a value it would be better for me since I can manipulate the
data easier. I need this project done before the end of year so I guess I don't have much time
to learn what you had suggested.

I know it is possible by the use of VBA. I knew VBA but in excel so i guess it could help me
learn in Access. My idea is that I will store the amount and the total weight in a variable.
Then get the ratio and multiply it to the individual weight of the item. My code would be
something like this

Sub myCodeName()
Dim N as Double, D as Double, Q as Double
N = Me!Amount.Value ' This is the field for the delivery charges
D = Me![mySubform]![Sum Weight].Value ' This is the footer of the sub
form
Q = N/D
'this where I want to right my code that will loop for each record in
the subform that will store in the field [Charges] = [Weight] * Q

End Sub


Please help me!

_________________________________


Why does it have to be a stored value? You can create a query that includes a calculated field,
and use that query as the RecordSource for your report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


_________________________________


Dear Experts,

I am trying to make a form that will calculate the amount of delivery charges per item to be
delivered. What I did is to put the total delivery charges on the main form. My problem now is
how to allocate the charges to each individual item in the subform base on their weight. Please
note that the field for charges per item can not be a calculated field. It has to be a value
because it is needed to be stored in the database for use in the report.

TIA

Jon-jon

 

[Rick Brandt]

Douglas,

Thanks for the reply. I don't want to sound stupid but as a
beginner your answer makes me more confuse. If I could stored it as
a value it would be better for me since I can manipulate the data
easier. I need this project done before the end of year so I guess I
don't have much time to learn what you had suggested.

I know it is possible by the use of VBA. I knew VBA but in excel so i guess
it could help me learn in Access. My idea is that I will
store the amount and the total weight in a variable. Then get the
ratio and multiply it to the individual weight of the item. My code
would be something like this

Sub myCodeName()
Dim N as Double, D as Double, Q as Double
N = Me!Amount.Value ' This is the field for the delivery charges
D = Me![mySubform]![Sum Weight].Value ' This is the footer of the
sub form
Q = N/D
'this where I want to right my code that will loop for each record
in the subform that will store in the field [Charges] = [Weight] * Q
End Sub

Please help me!

If you were doing this in Excel, which of the following would you do?

A)
Create a Macro that scans all the rows in your spreadsheet taking the value
found in Column(A) and Multiplying by the value in Column(B) and storing the
result in Column(C). Any time either value in Column(A) or Column(B) changes
you would need to remember to re-run your macro. If by some chance a value in
Column(C) were ever edited directly you would now have a row where [A] *
does not equal [C] and have no way of knowing which of the three values is
incorrect.

B)
Enter into Column(C) the expression =[A1]*[B1] and then copy that expression
down to all rows. Changes to either Column(A) or Column(B) are automatically
reflected in Column(C) without any user intervention.

Option A sounds pretty ludicrous doesn't it? But it is exactly what you are
attempting to do when you store the result of a calculation in a database table.
Put the calculation in a query and then use the query every place you are now
using the table. There is nothing you can do with a table that you cannot also
do with a query.