Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Please Help: How to execute code for all records?

Discussion in 'Information Technology' started by mjj1998@gmail.com, Nov 15, 2005.

  1. mjj1998@gmail.com

    mjj1998@gmail.com
    Expand Collapse
    Guest

    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!
     
  2. Loading...

    Similar Threads Forum Date
    General Help! Looking for a good clairvoyant/card reader......any suggestions? Please Blogs Jan 25, 2016
    Please help Questions and Answers Nov 1, 2013
    Please Help! What is the Name of the Narrator/Raagi? Gurbani Download Oct 16, 2012
    Muslim Girl-Sikh Boy Marriage Problems- Please Help! Love & Marriage Oct 12, 2012
    Help Please :/ Relationships Apr 19, 2011

  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    <mjj1998@gmail.com> wrote in message
    news:1131967881.021793.205510@g43g2000cwa.googlegroups.com...
    > 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!
    >
     
  4. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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.


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <mjj1998@gmail.com> wrote in message
    news:1131967881.021793.205510@g43g2000cwa.googlegroups.com...
    > 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!
    >
     
  5. Mjj1998

    Mjj1998
    Expand Collapse
    Guest

    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
     
  6. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

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

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)



    "Mjj1998" <mjj1998@gmail.com> wrote in message
    news:1131975242.054563.208570@g14g2000cwa.googlegroups.com...
    > 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
    >
     
  7. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    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

    "Mjj1998" <mjj1998@gmail.com> wrote in message
    news:1131975242.054563.208570@g14g2000cwa.googlegroups.com...
    > 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
    >
     

Share This Page