August 19, 2008 at 1:44 pm
I have several Queries which need to do calulations on calculated columns of data, however every time I attempt to refer to a computer column of data, I get a error message which has no fix indicated? I'm at a loss as how I should do this, I used to do it all the time in old Access 2003, but I am required to move up to a Access front-end with a SQL (2005) backend.
Appreciate any suggestions, and any directions to references on the Access Front-End/SQL Back-end Model.
Thanks Pete
August 19, 2008 at 1:53 pm
It would help if you posted the error message and the table definitions. See the links in my signature line for some advice on how to get better, faster answers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 2:29 pm
Sorry, nine year of Access, 3 mos. of SQL.
Here's my most recent attempt to create a computer column from several computed columns.
EMonth = DATEPART(m,dbo.employee.LGD)
EDay=DATEPART(d,dbo.employee.LGD)
These work fine, then I attempt to create a column with the following:
PPR Due Date = EMonth& ' / '&EDay
The structure is changed to 'EMonth& ' / '&EDay' , and thats what ends up in my column, not calculations.
If I create a column of data :
Lunch Time = DateDiff(s,[End Lunch],[Start Lunch]) it's okay, but then every time I attempt to use the new column [Lunch Time] i get an error - [Lunch Time] in expression is not part of the query.
Thanks for any direction.
August 19, 2008 at 2:39 pm
I don't know about you, but I am still confused.
Are you creating COMPUTED COLUMNS on a table or are you trying to create output columns in a query?
Are you creating the query using the ACCESS query designer or using SQL Server Management Studio (SSMS)?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 19, 2008 at 2:49 pm
Creating Columns in a Stored Procedure using Access Query Designer. Using what I know.
Pete
August 19, 2008 at 3:57 pm
pete.trudell (8/19/2008)
Sorry, nine year of Access, 3 mos. of SQL.Here's my most recent attempt to create a computer column from several computed columns.
EMonth = DATEPART(m,dbo.employee.LGD)
EDay=DATEPART(d,dbo.employee.LGD)
These work fine, then I attempt to create a column with the following:
PPR Due Date = EMonth& ' / '&EDay
The structure is changed to 'EMonth& ' / '&EDay' , and thats what ends up in my column, not calculations.
If I create a column of data :
Lunch Time = DateDiff(s,[End Lunch],[Start Lunch]) it's okay, but then every time I attempt to use the new column [Lunch Time] i get an error - [Lunch Time] in expression is not part of the query.
Thanks for any direction.
It would be very helpful if you would read the articles that Jack links to in his signature. Having those would make it a lot easier to provide you with a working example.
With that said - you can use one of the following constructs:
-- Example using CTE
CREATE PROCEDURE dbo.MyProcedure AS
;WITH myComputed (col1, EMonth, EDay, LunchTime)
AS (SELECT col1
,DATEPART(m,dbo.employee.LGD) AS EMonth
,DATEPART(d,dbo.employee.LGD) AS EDay
,DATEDIFF(s,[End Lunch],[Start Lunch]) AS LunchTime
FROM MyTable)
SELECT col1
,EMonth + '/' + EDay AS PPRDueDate
,LunchTime
FROM myComputed;
GO
-- Example using derived table
CREATE PROCEDURE dbo.MyProcedure AS
SELECT col1
,EMonth + '/' + EDay AS PPRDueDate
,LunchTime
FROM (SELECT col1
,DATEPART(m,dbo.employee.LGD) AS EMonth
,DATEPART(d,dbo.employee.LGD) AS EDay
,DATEDIFF(s,[End Lunch],[Start Lunch]) AS LunchTime
FROM MyTable) AS t;
GO
I prefer using the CTE myself.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 20, 2008 at 7:54 am
I appreciate your information, however I don't understand any of it. What is CTE? That is why I came in under Novice as I have never used SQL before and I have been struggling to find some reference to Access and SQL to under stand the differences. But no one has a clue.... They just send me information that is Greek , and I don't speak the language.
But, I do appreciate the attempt. Sorry.
Pete
August 20, 2008 at 8:26 am
Okay Pete,
Your main problem is that in Access "&" is the concatenation operator, in SQL Server you need to use "+".
Where version of Access are you using? Access 2007 does not allow the Create Procedure statement so you aren't creating a stored procedure using it.
If you really are moving forward you will be much better off learning to use the SQL Server Tools like SSMS. SSMS has a Query Builder which is very similar to the Access Query Builder so you can add tables, drag and drop joins, etc... Once you've seen the results a couple of times you'll be ready to abandon it. SSMS also has templates so you can easily learn syntax as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 20, 2008 at 9:32 am
Thanks for that Info, however I am using Access 2007 and Stored Procedures. It has a Button to create Stored Procedures. We're also on the hated Microsoft Vista OS (which I hate). I'll check out SSMS , Thanks Again.
Pete
August 20, 2008 at 2:14 pm
pete.trudell (8/20/2008)
I appreciate your information, however I don't understand any of it. What is CTE? That is why I came in under Novice as I have never used SQL before and I have been struggling to find some reference to Access and SQL to under stand the differences. But no one has a clue.... They just send me information that is Greek , and I don't speak the language.But, I do appreciate the attempt. Sorry.
Pete
Okay, CTE stands for Common Table Expression and was introduced in SQL Server 2005. In this instance, all we are doing is creating a defined query that can be used in the later part of your query. This way, we can create the computed columns and then reference those computed columns. You can get a lot more information in Books Online (help that comes with the SQL Server tools).
The other option is called a derived table (using a query in the from clause), again so we can reference the computed columns in our outer query.
Another option would be to create a view with your computed columns and then use that view in your stored procedure:
CREATE VIEW dbo.MyView AS
SELECT [columns]
,computed_column1
,computed_column2
FROM your_table
WHERE somecriteria_if_needed
GO
Then in your stored procedure you would just reference the view.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply