Tomnehek Posted October 11, 2006 Share Posted October 11, 2006 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! Link to comment Share on other sites More sharing options...
jesh Posted October 11, 2006 Share Posted October 11, 2006 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. Link to comment Share on other sites More sharing options...
aspnetguy Posted October 11, 2006 Share Posted October 11, 2006 yes you can run queries in Access Link to comment Share on other sites More sharing options...
Tomnehek Posted October 11, 2006 Author Share Posted October 11, 2006 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? Link to comment Share on other sites More sharing options...
aspnetguy Posted October 11, 2006 Share Posted October 11, 2006 Access will treat anything you place in [] as a fieldname Link to comment Share on other sites More sharing options...
Tomnehek Posted October 11, 2006 Author Share Posted October 11, 2006 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. Link to comment Share on other sites More sharing options...
aspnetguy Posted October 11, 2006 Share Posted October 11, 2006 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. Link to comment Share on other sites More sharing options...
jesh Posted October 11, 2006 Share Posted October 11, 2006 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? Link to comment Share on other sites More sharing options...
Tomnehek Posted October 11, 2006 Author Share Posted October 11, 2006 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! Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now