SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Refer to A Computed Column in Access-SQL Database Query


How to Refer to A Computed Column in Access-SQL Database Query

Author
Message
pete.trudell
pete.trudell
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 55
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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104251 Visits: 15047
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
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
pete.trudell
pete.trudell
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 55
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.
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104251 Visits: 15047
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
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
pete.trudell
pete.trudell
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 55
Creating Columns in a Stored Procedure using Access Query Designer. Using what I know.

Pete
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46411 Visits: 10150
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

pete.trudell
pete.trudell
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 55
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
Jack Corbett
  Jack Corbett
SSC Guru
SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)SSC Guru (104K reputation)

Group: General Forum Members
Points: 104251 Visits: 15047
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
Dont let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work
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 QuestionHow to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
pete.trudell
pete.trudell
Old Hand
Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)Old Hand (397 reputation)

Group: General Forum Members
Points: 397 Visits: 55
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSC-Forever
SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)SSC-Forever (46K reputation)

Group: General Forum Members
Points: 46411 Visits: 10150
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search