Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 21st, 2009, 08:31 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Creating a view (Access Front end/SQL Back end)

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!
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #2 (permalink)  
Old April 21st, 2009, 09:29 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========

Last edited by gbianchi; April 21st, 2009 at 09:30 AM.. Reason: added pingback from original thread
Reply With Quote
  #3 (permalink)  
Old April 21st, 2009, 09:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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.)
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #4 (permalink)  
Old April 21st, 2009, 10:14 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
The Following User Says Thank You to gbianchi For This Useful Post:
lryckman (April 21st, 2009)
  #5 (permalink)  
Old April 21st, 2009, 10:43 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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.
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #6 (permalink)  
Old April 21st, 2009, 10:59 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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.
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
  #7 (permalink)  
Old April 21st, 2009, 11:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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.
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #8 (permalink)  
Old April 21st, 2009, 11:23 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
  #9 (permalink)  
Old April 21st, 2009, 11:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

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."
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
Reply With Quote
  #10 (permalink)  
Old April 21st, 2009, 11:55 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

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)
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Front end Vs Back end ricmar Access VBA 3 May 27th, 2008 02:36 PM
Multiple SQL Databases for Same Access Front End darrenb Access 0 May 27th, 2008 12:07 AM
Ms Access front End with Oracle 10g Back End rahul123 Oracle 1 July 9th, 2007 01:03 AM
Oracle back-end MS-Access 2003 client front-end Corey Access 2 February 16th, 2007 08:31 AM
Oracle Back End - MS Access Front End - Multi User ckaliveas Oracle 1 February 1st, 2007 06:00 AM



All times are GMT -4. The time now is 12:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.