How Do I Translate My Access Programs to SQL?

[xljunkie]

So I'm a long time user of Access, and I know some SQL. I have Access programs that run many different queries, manipulate the data, and I might end up with a singular table that is the result of many different tables' results and data transformations.

When I go to SQL, there is space for me to write a SELECT FROM WHERE statement which would give me one table of results. If I wanted to perform two different SELECT statements in the same process in a logical flow of a program, how do I do that? If I hit F5 in SSMS in a query that contains two SELECT statements, I'll get an error.

I'm trying to connect how I normally write a program in Access to how I can achieve the same in SQL. Do I need to look into stored procedures? Or is there something else I need to research?

 

[Norie]

How are you writing the separate queries?

Each one needs to be terminated with a semi-colon.

 

[Kyle123]

You don’t get an error running multiple selects in smss, I do it all the time - nor do you need to terminate with semi colons, though it’s good practice.

I fear that you may misunderstand the difference between Access and SQL server. One isn’t usually a direct replacement for the other, access offers a programming environment, macros, forms etc and is a user facing tool. SQL server is not, it’s only part of the equation and you need something else as well to generally write a program or provide something to end users.

 

[xenou]

So I'm a long time user of Access, and I know some SQL. I have Access programs that run many different queries, manipulate the data, and I might end up with a singular table that is the result of many different tables' results and data transformations.

Generally you can do this easily in SQL server in a proc or even in a single script that runs like a query. As stated - you certainly can run two or more sql queries in one script, create and use temp tables as well, so a lot is possible.

 

[james_lankford]

your question is confusing to me

MS Access allows you to write queries using a language called SQL
you can write queries normally in Access using Design View, but then you can do sql view and see the actual sql

SQL Server is a Microsoft product that is "kid of similar" to Access but MUCH more advanced
SQL Server also uses the language called SQL

SSMS is SQL Server Management Studio, also a Microsoft product

SQL Server is the product that stores the tables and data and queries (these queries are known as stored procedures)
SMSS is the product that connects to SQL Server and allows you to create tables and queries using SQL or a point and click user interface

Access can also connect to SQL Server and you can use SQL to create tables and queries
Access is not SSMS

so, where does your data live ?
in Access or in SQL Server ?
are you trying to write SQL in Access or in SSMS ?