I've never seen anything like [Numbers by Quarter and Bedsize].SizeCategory
This is an example of a dot qualified name. The element to the left of the dot is the correlation name. If you do not provide an explicit correlation name in the from clause, a default correlation name equal to the table name is assumed. For example
SELECT [Numbers by Quarter and Bedsize].SizeCategory
FROM [Numbers by Quarter and Bedsize];
...is actually parsed as:
SELECT [Numbers by Quarter and Bedsize].SizeCategory
FROM [Numbers by Quarter and Bedsize] AS [Numbers by Quarter and Bedsize];
You'll see correlation name referred to as an "alias", which is not correct but had sadly become the vernacular.
Arguably it makes the code easier to read is a short correlation name is chosen e.g.
SELECT n.SizeCategory
FROM [Numbers by Quarter and Bedsize] AS n;
The square brackets around the table name are quoted identifiers. In Standard SQL, the quoted identifier is the double quote ("). The Access database engine (ACE, Jet, whatever) is not compliant with the SQL Standard and uses square brackets. SQL Server can use both the Standard and the proprietary square brackets as quoted identifiers.
Access requires quoted identifiers when a data element would otherwise be illegal e.g. contains or begins with spaces or non-alpha characters. The Access query by example builder thing tends to add quoted identifiers around all data element names regardless of whether they are actually required.
The table name Numbers by Quarter and Bedsize requires quotes identifiers due to the presence of spaces. I suggest you take the port as an opportunity to rename data elements. While a complete revision is probably due (I would question the general quality of the names chosen), some 'low hanging fruit' would be the opportunity to replace space characters with underscores.
Following the above advice, and adding some formatting, the query would become:
SELECT n.SizeCategory,
n.Bedsize,
n.SumOfNumber_of_CLABSI,
n.SumOfCentral_Line_Days,
Round(1000 * n.SumOfNumber_of_CLABSI / n.SumOfCentral_Line_Days, 2)
AS State_CLABSI_Rate,
n.SummaryYQ
FROM Numbers_by_Quarter_and_Bedsize AS n
ORDER
BY SizeCategory, SummaryYQ;
Obviously, the port will involve syntax changes but you also should also look for changes in behaviour. For example, division in Access SQL results in a FLOAT unless one of the values is of type DECIMAL in which case the result is a DECIMAL; the behaviour in SQL Server is different. One of the challenges you will face is the extremely poor documentation for Access e.g. the assertion I just made about division is not stated in the Access help nor any other official documentation from Microsoft; I had to figure it out for myself by trial and error.
I recommend you write comprehensive tests in both Access and SQL Server to ensure your ported SQL does not result in changed behaviour.
