Welcome to SPN

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

Sign Up Now!

Blank field in Table but not Form

Discussion in 'Information Technology' started by Sterdog, Oct 27, 2005.

  1. Sterdog

    Sterdog
    Expand Collapse
    Guest

    I have a field in my Form that calculates "Income". It is not appearing in
    the Table, all other Form fields are populating over but not the "calculated"
    ones.

    Anyone know why?
     
  2. Loading...


  3. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    .... and that how it should be: Calculated value should generally be
    re-calculated whenever needed it and not stored in the Table.

    While it is possible to store calculated values in the Table, the Relational
    Database Design Theory does not recommend this because this will introduce
    the potential for inconsistencies in data stored in the database, e.g. a
    component value is changed and the calculated value is not re-calculated and
    updated into the Table.

    --
    HTH
    Van T. Dinh
    MVP (Access)




    "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    news:203BF9A6-89E2-457F-876B-A4ED21C0AF83@microsoft.com...
    >I have a field in my Form that calculates "Income". It is not appearing in
    > the Table, all other Form fields are populating over but not the
    > "calculated"
    > ones.
    >
    > Anyone know why?
     
  4. Al Camp

    Al Camp
    Expand Collapse
    Guest

    Sterdog,
    Because they are "calculated" fields and not "bound" to any field in your
    table. The ControlSource of those fields are formulas, so Access has no way
    to associate the calculated value with any specific field.
    But... the larger issue is that calculated values are not saved.

    For example, Price * Qty = LineTotal. Price and Qty are bound to table
    fields, and saved, but Line Total is not. Since you've saved Price and Qty,
    you can always "re-derive" the LineTotal in any subsequent query, report, or
    form by placing a calculated unbound field with =Price*Qty in the
    ControlSource.

    Use calculated "unbound" fields for display purposes, but don't store
    them.
    --
    hth
    Al Camp
    Candia Computer Consulting - Candia NH
    http://home.comcast.net/~cccsolutions


    "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    news:203BF9A6-89E2-457F-876B-A4ED21C0AF83@microsoft.com...
    >I have a field in my Form that calculates "Income". It is not appearing in
    > the Table, all other Form fields are populating over but not the
    > "calculated"
    > ones.
    >
    > Anyone know why?
     
  5. Sterdog

    Sterdog
    Expand Collapse
    Guest

    Thanks guys: BUT how do create a report for calculated fields? I hope I
    didn't do all those calculations in the form for nothing?!!

    "Al Camp" wrote:

    > Sterdog,
    > Because they are "calculated" fields and not "bound" to any field in your
    > table. The ControlSource of those fields are formulas, so Access has no way
    > to associate the calculated value with any specific field.
    > But... the larger issue is that calculated values are not saved.
    >
    > For example, Price * Qty = LineTotal. Price and Qty are bound to table
    > fields, and saved, but Line Total is not. Since you've saved Price and Qty,
    > you can always "re-derive" the LineTotal in any subsequent query, report, or
    > form by placing a calculated unbound field with =Price*Qty in the
    > ControlSource.
    >
    > Use calculated "unbound" fields for display purposes, but don't store
    > them.
    > --
    > hth
    > Al Camp
    > Candia Computer Consulting - Candia NH
    > http://home.comcast.net/~cccsolutions
    >
    >
    > "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    > news:203BF9A6-89E2-457F-876B-A4ED21C0AF83@microsoft.com...
    > >I have a field in my Form that calculates "Income". It is not appearing in
    > > the Table, all other Form fields are populating over but not the
    > > "calculated"
    > > ones.
    > >
    > > Anyone know why?

    >
    >
    >
     
  6. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    If you can crerate a Calculated Value in the Form, you can create the same
    value in the Report, right?

    In the Report, you can either do the calculations in the Query / SQL being
    used as the RecordSource for the Report or you can place the required
    expression in a Calculated Control on the Report (depending on the
    calculations you need).

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    news:B437F88D-FBBC-40ED-B3E2-8C3D1957DB09@microsoft.com...
    > Thanks guys: BUT how do create a report for calculated fields? I hope I
    > didn't do all those calculations in the form for nothing?!!
    >
     
  7. Sterdog

    Sterdog
    Expand Collapse
    Guest

    Van,
    I tried to copy the form's expression and copy into the corresponding
    report's field and the value was incorrect???

    Since I have a very basic database, is there an easier way to get the
    results to "write" to a table?

    "Van T. Dinh" wrote:

    > If you can crerate a Calculated Value in the Form, you can create the same
    > value in the Report, right?
    >
    > In the Report, you can either do the calculations in the Query / SQL being
    > used as the RecordSource for the Report or you can place the required
    > expression in a Calculated Control on the Report (depending on the
    > calculations you need).
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    > "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    > news:B437F88D-FBBC-40ED-B3E2-8C3D1957DB09@microsoft.com...
    > > Thanks guys: BUT how do create a report for calculated fields? I hope I
    > > didn't do all those calculations in the form for nothing?!!
    > >

    >
    >
    >
     
  8. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Sorry, I don't advise people to do things that I consider incorrect and I
    don't do it myself.

    Post the details of relevant Table(s), the SQL of the RecordSource for the
    Form, the expression you used in the Calculated Control on the Form, the SQL
    of the RecordSource for the Report and the expression you attempted to use
    in the Report.

    --
    HTH
    Van T. Dinh
    MVP (Access)




    "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    news:BE868ABD-6ED4-4F64-AAD4-57DC05365C98@microsoft.com...
    > Van,
    > I tried to copy the form's expression and copy into the corresponding
    > report's field and the value was incorrect???
    >
    > Since I have a very basic database, is there an easier way to get the
    > results to "write" to a table?
    >
     
  9. Sterdog

    Sterdog
    Expand Collapse
    Guest

    Ok,
    I went back and queried all the fields in the table and entered in the
    appropriate expressions. I then used that query to build a report.
    Success!!! I am really good with Excel but this is the first time doing
    anything with Access.

    Is there a way I can past in the formatting from a previously made excel
    sheet and fill certain cells with cells from the report?

    "Van T. Dinh" wrote:

    > Sorry, I don't advise people to do things that I consider incorrect and I
    > don't do it myself.
    >
    > Post the details of relevant Table(s), the SQL of the RecordSource for the
    > Form, the expression you used in the Calculated Control on the Form, the SQL
    > of the RecordSource for the Report and the expression you attempted to use
    > in the Report.
    >
    > --
    > HTH
    > Van T. Dinh
    > MVP (Access)
    >
    >
    >
    >
    > "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    > news:BE868ABD-6ED4-4F64-AAD4-57DC05365C98@microsoft.com...
    > > Van,
    > > I tried to copy the form's expression and copy into the corresponding
    > > report's field and the value was incorrect???
    > >
    > > Since I have a very basic database, is there an easier way to get the
    > > results to "write" to a table?
    > >

    >
    >
    >
     
  10. Van T. Dinh

    Van T. Dinh
    Expand Collapse
    Guest

    Sorry, I don't know for sure but the Excel & Access formatting are
    substantially different.

    --
    HTH
    Van T. Dinh
    MVP (Access)



    "Sterdog" <Sterdog@discussions.microsoft.com> wrote in message
    news:3BA01766-525D-4B23-B307-0A51B379290B@microsoft.com...
    > Ok,
    > I went back and queried all the fields in the table and entered in the
    > appropriate expressions. I then used that query to build a report.
    > Success!!! I am really good with Excel but this is the first time doing
    > anything with Access.
    >
    > Is there a way I can past in the formatting from a previously made excel
    > sheet and fill certain cells with cells from the report?
    >
     

Share This Page