question about table structuring

[Tomnehek]

I'll be honest, I'm quite new to SQL and all that good stuff so this may be sound like a dumb question. I'm in the process of creating a database of baseball statistics in microsoft access and I'd like to figure out a way to take data from multiple fields in the same table to be displayed in another field in, once again the same table. For example, I would like to have something like the Batting Average to be calculated by taking the data in the Hits column and dividing it by the data in the At Bats column automatically. This would make it much easier to update the database and it would eliminate a ridiculous amount of work. Does anyone know how I would go about doing this? Thanks a bunch!

[jesh]

I must admit that I haven't used Access in 10 years so I don't know if you can run SQL queries in it, but here is what a sample INSERT query could look like:

INSERT INTO PlayerStatistics (FirstName, LastName, Hits, AtBats, BattingAverage) VALUES('Albert', 'Einstein', 37, 56, 37 / 56)

If you are entering data into the table using a query, you can calculate the average right in the query using division ("/"). If you are not using an INSERT query, maybe there's a setting on the table itself that you could specify the default value as being [Hits]/[AtBats] so when a record is added to the table, the default value is the value in the Hits column divided by the value in the AtBats column.

[aspnetguy]

yes you can run queries in Access

[Tomnehek]

When I tried to write [H]/[AB] into the default value, it says that it doesn't recognize the fields "H" and "AB" in a validation expression or in the table. What exactly does that mean?

[aspnetguy]

Access will treat anything you place in [] as a fieldname

[Tomnehek]

I know, which is why I find it strange that it doesn't recognize it as a field name because H and AB are most difinately fields. I don't know what it has to do with the validation expression, so I'm a bit confused.

[aspnetguy]

ohhh I se you want the default value to be H dived by AB?try tableName.[H]/tableName.[AB]but I don't think it will work. Think about this, which row or H and which row of AB do you want to use....it is ambiguos.

[jesh]

ohhh I se you want the default value to be H dived by AB?try tableName.[H]/tableName.[AB]but I don't think it will work. Think about this, which row or H and which row of AB do you want to use....it is ambiguos.

Speaking of this (and I think it's because I brought up the possibility), is there a way to set up a table so that a specific column has as its default value an arithmetic calculation of two (or more) other columns? Or is this only possible in the INSERT query.To further this, does it make sense to have a column in a table which is a calculation of other columns in the same table? Would it make more sense to perform the calculations in the application code rather than storing it in the database?

[Tomnehek]

I discovered that I can achieve what I'm trying to do by saving the table as a form and then using the form properties by placing the expressions I need in the control source. Thanks for all your help guys!