Originally Posted by ECEK
Do I build it in one complex query (Not dissimilar to VBA style or do I build views and then reference these (as one would do in Access).
Put simply "Yes".
What you use depends on the context. A View may be good where there are a number of queries built on the same foundation. Provided they are not too complex they work pretty much like tables.
TSQL is a full programming language that supports multiple commands within one query. You have to use the SQL editor to do this because the designer doesn't support this functionality.
Don't be put off writing SQL just because you might have struggled with the SQL editor in Access. It always was hopeless. The Editor in the Management Studio is very powerful with Intellisense and on-the-fly syntax checking. It is much easier to build far more complex queries including subqueries.
The joins are much simpler to write than in Access without the need for nesting joins. The joins are far more diverse than in Access, with Full Outer Join, Cross Apply, Outer Apply as well as the familiar ones.
The learning curve to get into SQL Server through Management Studio is not especially steep but "the mountain is high" if you get my drift. You can get working with it fairly quickly but keep finding new stuff for years. I've always found a capability to do anything I have been able to imagine wanting to do (and I have a good imagination).
Learn about Stored Procedures early in your exploration. They are one of the most useful features.
Start your query coding using the Select TOP 1000 records on the right click of a table. This will load the SELECT for its columns and the FROM clause which you can start editing from.
Save time entering column names for other tables by Dragging the Column heading of the table from the Object explorer tree view into the query editor. This will paste a list of the columns into the SQL text.
The key to building complex queries is the formatting. Use new lines for the key words and indent the subordinate sections. Vertically align the open and close parentheses of the subsections.
Highlight a section of code in the editor and press run to get the results from that section. This is handy for testing.