I need to run a data report that will query an Access_Table that has the
following Project info:
My Form has a command_button with the following code:
Private Sub Command1_Click()
Set db = New Connection
db.CursorLocation = adUseClient
db.Open "PROVIDER=MSDataShape;Data
PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path &
"Access_Table.mdb;"
Set adoAge = New Recordset
adoAge.Open "SELECT CustomerName, OrderNo, Invoice_Date,
IIf([Invoice_Date]<Now()-30 And
[Invoice_DATE]>Now()-60,[Invoice_Amount],"") AS Greaterthan30,
IIf([Invoice_Date]<Now()-60,[Invoice_Amount],"") AS Greaterthan60 FROM
Cust WHERE [Invoice_Date]< Now()-30", db, adOpenStatic, adLockOptimistic
Set DataReport1.DataSource = adoAge
DataReport1.Show
Problem arise when trying to display the above report that has an error
message that says "Datafield Greaterthan30 not found"
Is this an SQL Query limitation where Invoice_Date Datafield can only be
queried once & if we have more than one Datafield
with the same fieldname (Invoice_Date) within the same Data report,the
report will fail to run? Should there be one more
New Recordset to hold a second Datafield (Invoice_Date)?
can anyone help? Thanks
From:Cady Steldyn
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
HiI am using Management Studio with SQL Server 2005 Express. I am trying to use the Calculated Column Specification by entering a formula.Every attempt results in the same error 'Error Validating the formula'Lets say I have 3 columns a,b, and cI wish to put a formula into c so that it becomes a/bCan anyone either help me with the syntax or point me a resource. I have googled without success. There seems to little or nothing out there on this topic.Thanks,Bill
I am a newbie to SQL Server using SQL Server 2000.
I am trying to use a calculated field in a table, what I want is for the result to be shown as an integer (int)? However the fields that I base the calculation on are of the type (money), and the calculted field although giving me the correct result makes the field of type (money).
The fields that I am basing the calculation on are:-
The above report would list 2 columns as Description & Amount, next it would sort the Description column by GL_ID, next by Year 2005 & lastly by Period 08, with a net figure of asset minus liability.
Guys, hope someone out there can help me with the sql command for the above report?
I have a table and I need to have a calculated field which calculates GPA based on the letter grade they have, and only those grades that have subject as CHEM or BIO.
This may be an extremely simple question, but I am trying to combine two text fields (last name, comma, space and first name) into a new field that can be used as a GROUP in my report.
I need a calculated field C with several CASES. If (field A is 'daily' or 'half day' or 'hourly') and (field B is NULL) then C= D-50 If (field A is hourly and field B is NULL then C= 850I don't know sql server 2000 well enough to create this query.thanksMilton
I am used to working in MS Access where you can return a value as in: [date1]-[date2]=X
It will calculate that value provided "date1" and "date2" are fields in the recordset. One calc for each record. I am getting an error message in SQL Server saying that neither "date1" nor "date2" are not contained in an aggregate function and there is no "group by" clause. In Access this would not be a problem. Can you help? Thank you.
Right now I have one view that grabs records and sums up related records etc.... and returns a result. So basically it has the ID number and the number I calculated. THen I have another view that takes that number and performs calculations on it into three different columns. Is there any way to make these two view into one without a lot of repetative statements? Here is an example:
SELECT (tblTest.Quantity * tblTest.Price) as SubTotal, SubTotal * 1.06 as Total
Obviously that doesn't work, but what could I do to get that basic thing to work?
I have a Vehicle/Driver Inspection Database and I'm trying to create a Traffic Violation Point System, its a system when drivers commit certain traffic offences they will incur driving-offence points.
System: The points for violations that all occurred within the last 12 months of ONE ANOTHER are added together to calculate point total. If the accumulated points is reached 14 points driver is suspended the points will be REMOVED or minus 14 points after the suspension has been served.
Example:
Date of Offence - Number of Points Aug. 6, 2013 - 6 Feb. 4, 2014 - 4 Apr. 25, 2014 - 4 - 2 May 8, 2014 - 2
Explanation:
On April 25, 2014 two offences incurred and from (Aug 6, 2013 - Apr. 25 2014) 14 points have accumulated (6 + 4 + 4) and suspension is carried out then 14 points is removed. The 2nd offence on Apr. 25, 2014 will be carried forward and added to May 8, 2014 points (total 4 points).
Hiin T-SQL,(how) is it possible to concatenate 3 (varchar) fields into one; eitherin a SQL query or through a calculated field (or using a view, ifanybody can explain to me how to use views), according to the followingrules:{first 30 chars of Trim(AttributeVal1)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal2)if resulting string<30 chars append", " & first 30 chars of Trim(AttributeVal3)}=> define as new field StockItemDescriptionideally I would like SQL Server to do this processing rather thanbuilding all these answer strings on the client side.tiaAxel
I have a quick question that i have just a simple calc field.
Field.Value-Field.value then i want to sort by the calculated results. The expression is fine i know it is written wrong here but i cannot figure how to sort by the result of the expression. Thanks.
I have a fairly simple SQL code that includes a calculated field (date difference) in Oracle. I am bring the data in a csv format, and its giving me nothing for that calculation. I checked the results in TOAD and they look fine. I know, i am missing something for the format but have tried everything. I am new to this so any help is appreciated.
Thanks
select rpt.emc_last_run_date "Report Run Date",
(rpt.EMC_ACCEPT_DATE_TIME - rpt.EMC_QUEUED_DATE_TIME * 24 * 60) "Time To Accept(mins)"
I would have a question, if it is possible to reference a calculated field in T-SQL query. The following expression does not work in SQLExpress 2005
SELECT Qty, UnitPrice, Tax, Qty*UnitPrice as Expr1, Expr1*(1.0 + Tax) AS Expr2
This problem has occurred after upsizing to SQLExpress from Access XP/Jet Engine. Since Access does not have any issue with the expression above, the SQLExpress does not even accept it.
The only way how to avoid the issue in the SQLExpress seems probably to be
- Duplicate some calculations (i.e. expand every expression with duplicating some mathematic operations with some performance loss)
- Utilize computed columns if possible (no idea on performance impact here)
Am I right or is there any other way how to reference a calculated field?
Any suggestion is greatly welcomed! Thanks in advance.
I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!
I'm trying to create an OLAP system using SQL Server 2000 AnalysisServices (AS). I want the AS cube to be based on a database with astar schema.I have a field called Ratio. The initial ratio value is based uponthis formula: (CurrentTimePeriodAmount -PreviousTimePeriodAmount)/PreviousTimePeriodAmount. However, thatinitial ratio can be manually overridden and replaced. Then the ratiois used to calculate future Amounts.Should I place that calculated Ratio field in the "source" databasedesign or should it be part of the Analysis Services cube?
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.
Then I stuffed the self joins into a couple views for the events I'm interested in...
The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.
So how can I speed up the filtering on that calculated column?
Or am I going to have to get really creative with unions?
OK I have a report that needs an interactive sort on a calculated field. I get the message: "Report items cannot be used in sort expressions"
That's the whole reason we purchased SS*S and are putting up a Data Warehouse, so we can rank and analyze our data. Surely there is a way to do this?? Thanks for any advice!
I'm trying to create a calculated field that gives me the avg 75 percentile.
Right now I get this value by doing the following:
Create data set:
Select top 75 percent <field>
from <table>
Then I create the following calculated field
Avg(Fields!<field>.value,"<data_set_name>")
But I wanted to be able to create a calculated field that gives me the avg 75 percentile without creating a separate data set to get the top 75 percent Value.
use Northwind Go select dbo.Orders.OrderID ,Cast(dbo.Orders.OrderDate As DATE)Order_Date , dbo.Customers.CustomerID , dbo.Customers.CompanyName , dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
I'm trying to create a calculated field using SQL Server Express, from within the Visual Web Developer Express 2008 IED. The field I am trying to create is called "total" and is supposed to be the sum of the subtotal, salestax, and shipping fields.
