Queries in Access to Views in SQL Server (1 Viewer)

Hi,

I have been using access for several years now and am getting up to speed in SQL Server now.

In Access, you can create a new column that is based upon other columns in the table. Then, within the same query, you can use the new column as a basis for a new calculated column and so on.

My question is that I see in SQL Server views I don't have this ability. So, if I use a column from a table to create a calculated column, I can't then refer to that new column within the same view.

Rather, I have to either create a new view on top of the first view where I am then allowed to use the new column or I have to copy all of the calculations from the old column into the new calculations (which makes it very messy).

Has anyone seen an easier solution here? Thanks

Best Regards,
C

not quite following....
when you say VIEW, you mean an SQL svr view. Which shows as a table in access.
then you make an access query of the 'table' and add new fields.
Ive done this.
When you build your query, youre saying you cannot add new fields?

CNorway said:

In Access, you can create a new column that is based upon other columns in the table. Then, within the same query, you can use the new column as a basis for a new calculated column and so on.

Click to expand...

What version are you using. In my experience (up to A 2010) fields derived in a query cannot be used in the same query unless it is through a subquery.

Agree with Galaxiom's experience of doing this.
When I have tried doing this in the past, it occasionally works but often produces a parameter input box before working on a second run... or not at all.
The two step approach is guarantee to work in both Access or SQL server.

For info, SQL server views are normally read only in Access unless you can assign a PK field in Access

Hi,

Galaxian,
I am using Access 2010. Though, I do the same thing in Access 2016. For example, I can create a field:

Within the same query I can do the following


FullName: [FirstName] & " " & [LastName]
FullNameWithTitle: [Title] & " " & [FullName]
FullNameWithTitleAndRank: [Rank] & " " & [FullNameWithTitle]

Can you see that in Access within the same query I can create a new column and then use that new column in order to get the next column without any issues at all.

Ranman256,
In SQL Server and Access, you have tables in each. However, the equivalent of an Access query in SQL Server is a view. What I want is like you see above where I can just feed in the new calculated column into the next column I am creating. Instead, I have to do the following:

FullName: [FirstName] & " " & [LastName]
FullNameWithTitle: [Title] & " " & [FirstName] & " " & [LastName]
FullNameWithTitleAndRank: [Rank] & " " & [Title] & " " & [FirstName] & " " & [LastName]

So, I am limited to only using the columns found in the table I am using. Thus, the result is much messier than the Access solution where I can create a new column and then immediately use that new column in creating the next column.

Thanks,
c

Hi Colin,

Yes, I always assign a PK for SQL Server views when using them in Access.

I am very interested in hearing what the two-step approach is you are referring to. I am migrating all of my Access queries over to SQL Server views. Though, this issue of not being able to feed one column into the next in a given view is really slowing down the process and making my code messy.

Thanks,
C

As a general rule of thumb, it is not recommended to base Views on Views in SQL, performance can get very poor, as the execution plan doesn't correlate the two queries together.

Your example is by far the best way to go. If you really want to refer to a calculated column in your view, you can always use a derived table in the view, to create it locally first within the view, this will be included in the execution plan.

Minty said:

As a general rule of thumb, it is not recommended to base Views on Views in SQL, performance can get very poor, as the execution plan doesn't correlate the two queries together.

Your example is by far the best way to go. If you really want to refer to a calculated column in your view, you can always use a derived table in the view, to create it locally first within the view, this will be included in the execution plan.

Click to expand...

Great advice on the views. That is what I have been trying to avoid.

OK. So, the correct solution is using a derived table in the view. I will definitely go in that direction.

My current method is creating insanely messy code that is impossible to troubleshoot since each column is using data from the previous columns.

Thanks,
c

Hi,

Final note... after researching derived tables I discovered that CTE's are the best choice for me. You calculate and create the new column in a CTE, then you are free to use that column name anywhere in the query. This is a perfect solution that accomplishes what I have previously done in Access.

The part I like best is that there is no redundant code. That was the main issue previously. I have a pile of CASE statements and then whenever I want to use that field I had to paste in all of those CASE statements!

Anyway thanks again for the tip in derived tables

Best Regards,
c