My 7 year old does math better than that...
Smile. Sadly, the same error is present in the template (not just in the
picture). It is a good example of why one should carefully consider whether
they want to store a value in a table that can be calculated. Storing values
that can be derived from other data violates third normal form of database
design, but is sometimes justified for performance. You will see a paragraph
that is a direct quote from one of the design papers written by Michael
Hernandez, on page one of my Access Links.doc Word document.
Is there a good site to get quality templates?
I don't know of any sites that specialize in templates. I know of lots of
sample databases that others have available for download. You might check out
Roger's Access Library, here:
http://www.rogersaccesslibrary.com/TableOfContents3.asp
and
http://www.rogersaccesslibrary.com/OtherLibraries.asp
I am trying to set up an asset tracking database for our church, and this
is the first one I found.
I know of a really nice church database, but I'm not sure if it includes
asset tracking. If you send me a private e-mail message with a valid reply
address, I can help you further on this. My e-mail address is available near
the bottom of the contributor's page, indicated in my signature line. Please
do not post your e-mail address (or mine) to a newsgroup reply, as this will
attract spammers like flys on cow dung.
Also, how do you get away from the table lookup fields? I have the
categories in a different table and just want to be able to pull the value
from that table to update the asset category field on the asset table.
A combo box on a form serves a very similar purpose. This is not the same as
a nasty lookup field defined at either the table or query level. Normally,
one should not be trying to enter data directly into tables or queries, at
least for routine use of a database. A developer may very well choose to
enter data directly, but that's only after having a clear understanding of
the structure of the database. For user's, such as people at your church who
may eventually be entering data, they would be restricted to using forms and
reports only. Kind of like driving a car, the user's do not have to
understand all the workings under the hood.
A combo box includes a Row Source property, which is generally a table,
query or SQL (Structured Query Language) statement. A SQL statement is a
query, but it's just not saved as an query in the database. You can recognize
a SQL statement easily, because it should start out with the SELECT keyword
for a combo box row source. The combo box will have other properties
appropriate to it's intended use, such as number of columns, column widths
(keep in mind that a combo box will only display the first column of width
greater than zero when it is not in the dropped down mode), bound column (the
bound column contains the actual value selected, which may or may not be the
value displayed to the user, depending on column widths), and the Control
Source. The Control Source is the field that the selected value is saved
into. This is generally the foreign key field of the many side table that is
related to the lookup table. Consider the following example from the Assets
Tracking database. Open the Assets form in design view. Display the
Properties dialog (View | Properties, or use the F4 button) if it is not
already displayed. Select the combo box that includes a label with the
caption "Asset Category". You should see the name of this control,
"AssetCategoryID", in the blue title bar of the Properties dialog. Check out
the following properties:
On Format tab
Column Count: 2
Column Widths: 0";2"
List Rows: 8 (I recommend increasing this to 20)
On Data tab
Control Source: AssetCategoryID
Row Source Type: Table/Query
Row Source: SELECT DISTINCTROW [Asset Categories].* FROM [Asset
Categories] ORDER BY [Asset Categories].AssetCategory;
Bound Column: 1
Notes:
1.) You should not need the SQL DISTINCTROW keyword, since there should only
be one unique entry for AssetCategory in the AssetCategories table.
2.) I recommend changing this SQL statement as follows:
SELECT AssetCategoryID, AssetCategory
FROM [Asset Categories]
ORDER BY AssetCategory;
so that you know exactly which fields you are selecting, rather than the
SELECT * syntax, which would select all fields in the table. In this case,
this table only includes two fields, but that may not always be the case as
you may later decide to add one or more fields to this table. It's a good
idea to follow a golden rule in database design: Fetch only the data needed.
The SELECT * syntax is really not a good idea in my opinion.
3.) The Bound Column = 1 corresponds to the first field selected in the
query. This is the numeric AssetCategoryID (autonumber primary key) field.
Later on, when you start learning VBA code, the column numbers are zero
based, just to keep you on your toes! Anyways, it is the Bound Column value
(AssetCategoryID) a user selects, even though they never see this number
(first column width is zero) that gets stored in the Assets table foreign key
field specified in the Control Source property, [Assets].[AssetCategoryID].
Because the designers choose to use the same field name, AssetCategoryID, in
two tables, it may not be obvious at all to a beginner which field the data
is being saved to. It is being saved to the [Assets].[AssetCategoryID] field.
This field is included in the Record Source property for the Form.
On Other tab
Name: AssetCategoryID
Tab Stop: Yes
Tab Index: 4
Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
