Forms, Tables and Queries...Oh my!

[Ted Clore]

I have formula's on my forms that take values in several fields and
calculate a value and display that calulation.

This value isn't stored on the table, but it is OK on the form.

If I do a query of a table the value that is on the form doesn't show up,
but instead shows the value on the table which is always a 0.

How can I get the value on the form to be stored on the table so I can query
that information?

Thank you for your support.

Ted

 

[Baz]

I have formula's on my forms that take values in several fields and
calculate a value and display that calulation.

This value isn't stored on the table, but it is OK on the form.

If I do a query of a table the value that is on the form doesn't show up,
but instead shows the value on the table which is always a 0.

How can I get the value on the form to be stored on the table so I can query
that information?

Thank you for your support.

Ted

It is poor database design to store in a table a value that can be
calculated. Obviously this is such a value, because you are calculating it
in the form.

Let's suppose that, for the sake of argument, the formula you are using in
your form is:

= fieldA + fieldB

Then when you create your query, you can use exactly the same formula e.g.

SELECT fieldA + fieldB AS some_total FROM some_table

 

[Baz]

I have formula's on my forms that take values in several fields and
calculate a value and display that calulation.

This value isn't stored on the table, but it is OK on the form.

If I do a query of a table the value that is on the form doesn't show up,
but instead shows the value on the table which is always a 0.

How can I get the value on the form to be stored on the table so I can query
that information?

Thank you for your support.

Ted

It is poor database design to store in a table a value that can be
calculated. Obviously this is such a value, because you are calculating it
in the form.

Let's suppose that, for the sake of argument, the formula you are using in
your form is:

= fieldA + fieldB

Then when you create your query, you can use exactly the same formula e.g.

SELECT fieldA + fieldB AS some_total FROM some_table

 

[Baz]

It is poor database design to store in a table a value that can be
calculated. Obviously this is such a value, because you are calculating it
in the form.

Let's suppose that, for the sake of argument, the formula you are using in
your form is:

= fieldA + fieldB

Then when you create your query, you can use exactly the same formula e.g.

SELECT fieldA + fieldB AS some_total FROM some_table

Sorry everyone, dunno how that got posted twice. Wasn't me AFAIK.

 

[Ted Clore]

Thank you Baz, I think I understand. I should make my queries like my Form
and do the calculations in the query.

Ted

 

[Vincent Johns]

Thank you Baz, I think I understand. I should make my queries like my Form
and do the calculations in the query.

Ted

I inherited a database recently in which the controls on some of the
Forms had SQL statements as their data sources. Replacing all of those
with references to Queries (which I defined by copying the SQL from the
conrols) made it easier for me to keep track of them. It was also
pretty easy to test and update the Queries, since they were now visible
on the Query tab of the Database window. I gave them names that began
with abbreviations of the names of the Form and Control with which they
were used, and I added comments (accessible via Properties) visible in
the Database window. When you have dozens or hundreds of Queries to
keep track of, it helps to have a system. You may not have that many
right now, but you may think later of new things you'd like your
database to do, and keeping track of what you have now will let you
reuse or modify it so that you can avoid redoing the work at that time.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Rick Brandt]

I inherited a database recently in which the controls on some of the
Forms had SQL statements as their data sources. Replacing all of
those with references to Queries (which I defined by copying the SQL
from the conrols) made it easier for me to keep track of them. It
was also pretty easy to test and update the Queries, since they were
now visible on the Query tab of the Database window. I gave them
names that began with abbreviations of the names of the Form and
Control with which they were used, and I added comments (accessible
via Properties) visible in the Database window. When you have dozens
or hundreds of Queries to keep track of, it helps to have a system.
You may not have that many right now, but you may think later of new
things you'd like your database to do, and keeping track of what you
have now will let you reuse or modify it so that you can avoid
redoing the work at that time.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

I use SQL Statements as the RecordSource for my forms and reports whenever
possible precisely because I DON'T want them in the db window. When there
is a saved query in the db window I have to document where it is used. When
I use a SQL Statement in the RecordSource property it is self-documenting.

 

[Vincent Johns]

I use SQL Statements as the RecordSource for my forms and reports whenever
possible precisely because I DON'T want them in the db window. When there
is a saved query in the db window I have to document where it is used. When
I use a SQL Statement in the RecordSource property it is self-documenting.

OK, fair enough... but why do you "have to document" them? If someone
else is requiring that, then you probably have to respect his wishes
concerning how you organize your stuff.

If it's for your own benefit, then isn't it optional? I document my own
Queries because I want to be able to maintain them, and for me that's
not as easy to do inside a bunch of controls as in the list of named
Queries, though I know that many people (not just you) prefer to put
them there. But I don't think of SQL as being especially
self-documenting; for example, in Jet SQL, I haven't found any way to
add comments to a WHERE or INNER JOIN clause.

Note that, in Access 2003 (though not in Access 2000), you can get a
list of many of the places where a given Query is used, useful in case
you are thinking of modifying or deleting it and want to avoid goofing
up something that has been working and might depend on the Query.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Rick Brandt]

I use SQL Statements as the RecordSource for my forms and reports
whenever possible precisely because I DON'T want them in the db
window. When there is a saved query in the db window I have to
document where it is used. When I use a SQL Statement in the
RecordSource property it is self-documenting.

OK, fair enough... but why do you "have to document" them? If someone
else is requiring that, then you probably have to respect his wishes
concerning how you organize your stuff. [snip]

By "documenting" I mostly mean "where is this used?". When a SQL statement
is in the RecordSource of a form/report I automatically know that this
form/report uses it and nothing else does. That means I can freely mess
with it and know that I am not breaking something else.

With a saved query I have to first be sure of ALL of the places where it
might be used before I change it or delete it. Naming conventions can be
some help here, but I prefer not relying on that.

 

[Baz]

I use SQL Statements as the RecordSource for my forms and reports whenever
possible precisely because I DON'T want them in the db window. When there
is a saved query in the db window I have to document where it is used. When
I use a SQL Statement in the RecordSource property it is self-documenting.

I agree entirely. When I first started out with Access years ago, I did
everything with saved queries, believing that this was the smart move for
reasons of efficiency and reusability. However, over the years I have come
to see that this kind of over-proliferation of objects is a maintenance
nightmare, and I now only use saved queries when absolutely unavoidable.

 

[Vincent Johns]

[...]

I use SQL Statements as the RecordSource for my forms and reports whenever
possible precisely because I DON'T want them in the db window. When there
is a saved query in the db window I have to document where it is used.

RBrandt at Hunter dot com

I agree entirely. When I first started out with Access years ago, I did
everything with saved queries, believing that this was the smart move for
reasons of efficiency and reusability. However, over the years I have come
to see that this kind of over-proliferation of objects is a maintenance
nightmare, and I now only use saved queries when absolutely unavoidable.

OK, de gustibus non disputandum est.

I sometimes wind up with well over a hundred named Queries in a database
file, so I can appreciate that they can become confusing, but I name
them systematically and use comments, so having lots of them in there
hasn't been a big problem for me. And many (or most) of my Queries
depend on others, as I use them as wrappers for Tables -- to me it's
helpful to be able to make a global change by changing one underlying
Query, leaving the Tables untouched. But I can see that it would be
easy to create major trouble if it weren't obvious what the dependencies
are (which I usually handle via names).

I suppose I should also mention that the "Dependencies" function in
Access 2003, though it does a fairly good job, can't (for example)
identify dependencies involving computed SQL code, which might not even
exist until run time.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.