Creating a view (Access Front end/SQL Back end)

lryckman · April 21st, 2009, 08:31 AM

Hello!
I posted this on the SQL Server 2005 forum too, but the reply was a link to another website that was WAY above my head!

I am trying to create a view using the Query Designer in Microsoft SQL Server Management Studio Express and do not know how to accomplish it. I need it based off of another view with columns CCNNumber & PartNumber.
For example I have these records...

09-0001 AO-1234
09-0001 MIO-5678
09-0001 SGO-9012
09-0002 MIO-1234
09-0002 MIO-9876
09-0003 ASO-1234

I want the view to generate the following....
09-0001 AO-1234; MIO-5678; SGO-9012
09-0002 MIO-1234; MIO-9876
09-0003 ASO-1234

I can accomplish this in the front end using VBA...but it is really slow due to the number of records it is working with; hence the reason I would like it to be in a view on SQL that I can link as a table...so it will run much faster.

(I am not very fluent with SQL...I am self taught and have not had any training.)
Any help would be greatly appreciated!

gbianchi · April 21st, 2009, 09:29 AM

Hi Laura...

I know that the SQL you saw looks complex, but that is the correct way to do it.

Since you don't know how much data you have to concat on every record, how do you expect to do it??

Also, are you working with access or sql 2005?? because the solution (in terms on Sql string) could be very different.

Just for information: Query Designer Help

lryckman · April 21st, 2009, 09:57 AM

I want to put this into a view on our SQL Server 2005 database. Then I link the views to Access as a linked table. The Query Designer that I'm using looks similar to the design view of a query in Access so if this has to be a stored procedure or a written query...maybe that is why it is so complex for me???
Not being familiar with SQL language, it also makes it difficult for me to understand where to substitute my information at too. (I have had no training on Access VBA or SQL...everything I know is self taught and from asking questions here...I don’t have any education in the Information Technology field either...so that is why I sometimes have a hard time understanding.)

gbianchi · April 21st, 2009, 10:14 AM

You need a query here, no SP to work with.

The first post in that thread show you a table (that is used for example, and looks like your table), then there are 3 querys that do the same thing, with different aproach.
You pick any of the 3 querys you see, and replace @Sample with your table name, and the fields (id, code) with your own fields and you are ready to go.

Try it to see if you got the expected output.

lryckman · April 21st, 2009, 10:43 AM

Ok...I got the results I wanted (thank you!)...but how do I get that linked to Access or as a view in the database? When I tried to save the query, it does not let me save it within the database on the SQL Server...it just brings up a 'save as' box with all my other network and local drives.

gbianchi · April 21st, 2009, 10:59 AM

Mmm.. you can create a view with that query. And I assume ( because I never done that) that you can somehow link it to access, but I donÂ´t know how to do that, sorry.

lryckman · April 21st, 2009, 11:20 AM

Ok...getting closer!
I tried creating a view using the top example and it was automatically changed (formatted) to this:

Code:

SELECT DISTINCT s1.CCNNumber, STUFF
                          ((SELECT DISTINCT TOP 100 PERCENT ',' + s2.PartNumber
                              FROM         dbo.view_tbl2009_PartNumbers AS s2
                              WHERE     s2.CCNNumber = s1.CCNNumber
                              ORDER BY ',' + s2.PartNumber FOR XML PATH('')), 1, 1, '') AS PartNumbers
FROM         dbo.view_tbl2009_PartNumbers AS s1
ORDER BY s1.CCNNumber

Now...When I execute the query I get an SQL Syntax Error stating:
"Error in ORDER BY clause
Unable to parse query text."

The results will still show, but I cannot save the view. When I try to save the view, I get this error message:
"The ORDER BY clause is invalid in view, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Now, if we can get this to save as a view, I will be all set...I can link it to Access from there.

gbianchi · April 21st, 2009, 11:23 AM

One of the examples use top 100 percent ... add it and see if that resolve it...

lryckman · April 21st, 2009, 11:36 AM

I have tried all three...I get the same error on each of them. Then I tried the following:

Code:

SELECT DISTINCT s1.CCNNumber, STUFF
                          ((SELECT     ',' + s2.PartNumber
                              FROM         dbo.view_tbl2009_PartNumbers AS s2
                              WHERE     s2.CCNNumber = s1.CCNNumber FOR XML PATH('')), 1, 1, '') AS PartNumbers
FROM         dbo.view_tbl2009_PartNumbers AS s1

This gave me an error:
"Error in WHERE clause near '('.
Unable to parse query text."

gbianchi · April 21st, 2009, 11:55 AM

I supposed you need to put back the top 100 percent into the subquery (inside the STUFF function) and also add it in the main query (before s1.cnnnumber)