Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to Refer to A Computed Column in Access-SQL Database Query Expand / Collapse
Author
Message
Posted Tuesday, August 19, 2008 1:44 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 15, 2009 8:43 AM
Points: 25, 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
Post #555325
Posted Tuesday, August 19, 2008 1:53 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #555333
Posted Tuesday, August 19, 2008 2:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 15, 2009 8:43 AM
Points: 25, 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.
Post #555351
Posted Tuesday, August 19, 2008 2:39 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #555354
Posted Tuesday, August 19, 2008 2:49 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 15, 2009 8:43 AM
Points: 25, Visits: 55
Creating Columns in a Stored Procedure using Access Query Designer. Using what I know.

Pete
Post #555358
Posted Tuesday, August 19, 2008 3:57 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #555385
Posted Wednesday, August 20, 2008 7:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 15, 2009 8:43 AM
Points: 25, 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

Post #555783
Posted Wednesday, August 20, 2008 8:26 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:06 PM
Points: 10,910, Visits: 12,546
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

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
Post #555802
Posted Wednesday, August 20, 2008 9:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, July 15, 2009 8:43 AM
Points: 25, 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
Post #555881
Posted Wednesday, August 20, 2008 2:14 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:17 AM
Points: 4,379, Visits: 9,470
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #556094
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse