Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kkrishna
Starting Member
23 Posts |
 Posted - 2010-05-16 : 09:21:28
|
| Hi All,I am working on test databases on the SQL Express 2008 to gain experience.I created an Access front end for a database and I have now an .accdb file.I have a table, dbo_HitTheTarget, in the database in which I have two columns, NoOfHits and NoOfAttempts.I Want to generate a query that gives me the NoOfHits, NoOfAttempts and the SuccessRate.I created a function in the Access front end (VBA in a module) for calculating the SuccessRate.Function SRate(hit As Integer,att As Integer) As Variant If (att = 0) Then SRate = "" 'Leave the result cell blank. Else SRate = hit/att End If 'Assign the data type to the result If (SRate = "") Then SRate = CStr(SRate) 'convert to a string Else SRate = CDbl(SRate) 'Convert to a number SRate = Format(SRate,"#,###.0000") 'Format the number to have four decimal places. End IfEnd FunctionNow generate a SQL query in the Access file.SELECT NoOfHits,NoOfAttempts,SRate(NoOfHits,NoOfAttempts) As SuccessRateFROM dbo_HitTheTargetThere could be a problem of accomodating two data types in an Access table. Later on, I want to add more functions, join other tables, filter the results with WHERE clauses etc.I want to know whether this is an accepted practice in working with the SQL database. Before I try similar queries in a production database for which I have only SELECT permission, can I work along these lines?I do not want to corrupt the database by creating such queries.Will the experienced SQL specialists offer advice?Thanks in advance |
|
|
|
|
|
|
|
SELECT query in SQL database...