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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43741 Visits: 14925
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

Applications Developer

Don't 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 Question
How 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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43741 Visits: 14925
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

Applications Developer

Don't 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 Question
How 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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

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

Pete
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18763 Visits: 10042
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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43741 Visits: 14925
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

Applications Developer

Don't 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 Question
How 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
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 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
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18763 Visits: 10042
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