Of Saving Queries and Calculating Fields

[jsanders]

Morning All,
Just wondering.

When a form needs a calculated field. Say something simple, like

TotalCost = Quan*Cost

Is it better to create a query?
Use a table and create the calculated field in the query builder?
Or use a table and create a new unbound field on the form with the formula in it?

I never really know which one to do.

Lately I’ve been using saved queries for almost everything and now I have several dozen in this data base and its only half way done. Soon I’ll have to create a data base just to keep track of the queries in this one.

 

[obeylele]

I would go with calculations on forms or reports or where ever
You want to diplay it,

 

[jsanders]

I’m not trying to be glib, but why do you do it that way?

Is it easier to keep track of it or do you get better performance?

 

[Pat Hartman]

I do calculations of this type in a query. This eliminates the need for code behind the form to refresh the calculation when one of its components changes. It also eliminates the problems caused by unbound fields always showing the same value in a continuous form since the field will be bound.

It is good practice to always use queries as the RecordSources for your forms and reports as well as the RowSources for combos and listBoxes.

 

[jsanders]

As usual Pat,

Clear and concise.

 

[directormac]

It is good practice to always use queries as the RecordSources for your forms and reports as well as the RowSources for combos and listBoxes.

Pat: If you have a moment, could you expand on WHY this is considered "best practice"? I read a similar statement (by you, most likely) at some point and started basing every form on a query, especially in split FE/BE environments. Someone recently asked me why I didn't just base a form on the table and all I could think of (seriously) was " 'cause Pat said not to". I looked through the archives but didn't find a definitive reason -- perhaps this should be in the FAQ section?

--Faithful Mac

 

[Pat Hartman]

If only every one followed my directions as faithfully

In a relational database we spend a lot of time normalizing data so that it is spread over numerous tables. Everyone then forgets how to get it back into usable information. Rarely do I create a form or report from a single table. There is almost always some "lookup" required. Rarely do I create a form or report that doesn't require selection criteria. Rarely do I create a form or report that doesn't require sorting. The list goes on. I suppose, if you didn't have any lookups AND you didn't have any selection criteria, and you didn't need to sort the data and the table was not linked to a non-Jet back end and ...., then I suppose you could base your form/report on a single table.

The major reasons are selection criteria and lookups. If you have even the suspicion that your app may at some point need to be upsized, get in the habit of preparing for it now. It doesn't take any more work and it has the potential to make you a hero.

 

[directormac]

Thanks as always. You're the best!

--Grateful-Yet-Again Mac