Re: Calculated Value will not store in Table

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Albert D. Kallal (PleaseNOOOsPAMMkallal_at_msn.com)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 17:33:33 -0600

"tonyaims" <anonymous@discussions.microsoft.com> wrote in message
news:173c01c499d3$531ff910$a501280a@phx.gbl...
> Hi Albert,
>
> Good progress. I had not thought about TotPrice. I agree
> there is no need to save it. Thanks for pointing that out.

Actually, the issue of saving is kind of a balance issue. if you don't save
it, then you don't have to worry about storing the wrong values! On the
other hand, for a simple report, you now must write some additional sql
statements to "sum", or "calculate" those values. So, while you save in one
area, you make a bit more work in he other!! So, there is a balancing act
here, and the answer is actually going to be based on the amount of
experience you have, and the particular project (sometimes, it is better, or
"easier" to store the values...but being "easier" for the developer does not
necessary make a better application..but you get the job done!!. And, as
mentioned, in some cases, storing the data is better, but if you can advoied
this...you should.

> I have a combo box in frmPOItem that lists MaterialGrp
> names. The autonumbered ID for the selected name is
> stored in tblPOItem.MaterialGrpID. I do not have a field
> in tblPOItems that holds the actual text name of the
> selected group.
>
> Now if I run a form or a report that wants to list
> material group names, will the names be listed or the
> IDs?

The ID's will be listed (unless you are using the HORRIBLE and dreaded table
lookup feature..which you want to avoid).

>Do I have to have a field that holds the text name
> in order for the text to show up in forms and reports or
> is the ID adequate?

Well, as you can see, using a combo box is the recommend way to handle this.
In fact, if you use the wizard, it will *usually* store the id for you, but
actually display the text description field of your choice. So, for forms,
you can usually use the combo box wizard in this case.

However, for reports, then the best solution is to build a query. If you
have 2, or even 3 or even 4 combo lookups (that is where you store the ID,
but need some description, or "price" or whatever from the other table),
then you simply build a query and base the report on that way.

So, you drop in your main table into he query builder. And, for EACH of
those ID's that lookup into other tables, you simply then drop in the those
other tables (you can drop in as many other tables as you need into he query
builder). You then drop the join lines form the main table to those child
tables. It is important that you draw the join lines FROM the main table to
the child tables. Further, you have to use a left join here. You now are
free to drop in any field, or description field from those other tables
(often, you might have a few from those other tables). You then simply build
the report based on this query, which now has both the "ID" fields, and the
description fields.

So, for forms, I would simply using a combo box, and in fact, let the wizard
build the combo for you. For reports, you use the query builder, and simply
drop in those additional tables with the fields you need. 

-- 
Albert D. Kallal   (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
http://www.attcanada.net/~kallal.msn
Quantcast