What does the following SQL query do?

Question

The drawing logs at that the company I work for use are written in MS Access VBA. The original code was written 15+ years ago by someone else, and we're now running into errors with the reporting functionality that we can't find a solution for.

The query I'm having an issue with is the following:

Select Case Me.frameBaseType
    Case 1  'All drawings
        strFROM = "FROM (Company RIGHT JOIN Drawings ON Company.ID = Drawings.Company) LEFT JOIN Revisions ON Drawings.ID = Revisions.DwgID "

    Case 2  'Most recent revision
        strFROM = "FROM Company RIGHT JOIN ((Drawings INNER JOIN [For Report - Group Revision] ON Drawings.ID = "
        strFROM = strFROM & "[For Report - Group Revision].CbnDwgID) INNER JOIN Revisions ON (Drawings.ID = Revisions.DwgID) AND "
        strFROM = strFROM & "([For Report - Group Revision].RecentRevision = Revisions.ESIRevision)"
        strFROM = strFROM & " AND ([For Report - Group Revision].RevDate = Revisions.RevDate)"
        strFROM = strFROM & ") ON Company.ID = Drawings.Company "

    Case 3  'Due or late in routing
        strFROM = "FROM (Company RIGHT JOIN Drawings ON Company.ID = Drawings.Company) LEFT JOIN Revisions ON Drawings.ID = Revisions.DwgID "
        strWHERElate = "WHERE (((Revisions.Due)=1 Or (Revisions.Due)=2)) "
        strWHERE = strWHERElate

    Case 4  'Due or late from Vendor
        strFROM = "FROM (Company RIGHT JOIN Drawings ON Company.ID = Drawings.Company) LEFT JOIN Revisions ON Drawings.ID = Revisions.DwgID "
        strWHERElate = "WHERE (((Revisions.Due)=3 Or (Revisions.Due)=4)) "
        strWHERE = strWHERElate
End Select

Does anyone know what the notation [For Report - Group Revision] means in a SQL query?

This can't possibly be the complete query, so "what does it do? " No-one can tell you... Neither there is an error. [For Report - Group Revision] Could be a form or report name.... — hetOrakel, Nov 21 '17 at 19:40
Square brackets (`[`, `]`) are used to address fields with blanks. — Uwe Keim, Nov 21 '17 at 19:40
....why do people use `RIGHT JOIN`s? Why not just use that table as the initial `FROM` reference, and `LEFT JOIN` the other...? — Clockwork-Muse, Nov 21 '17 at 19:41
@Clockwork-Muse To make it difficult to run under SQLite3? :) — tonypdmtr, Nov 21 '17 at 19:42
@tonypdmtr - probably something like that. Or just making it hard to read, I guess - nothing like getting 20 `JOINS` down a query, only to find that the 21st is `RIGHT JOIN`ed, and nothing else was a guaranteed reference, :sigh: — Clockwork-Muse, Nov 21 '17 at 19:45
Thanks for adding more code, however you still don't show the *meat* of the query... Where is the 'SELECT...", etc. And what is the error??? — Wayne G. Dunn, Nov 21 '17 at 19:45
Somewhere following your code, the full SQL statement is assembled. Apply this: [How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271). If you still need help, add the full, final SQL and the error to your question. — Andre, Nov 21 '17 at 23:30

score 0 · Answer 1 · answered Nov 21 '17 at 19:35

0

That is a table name or query name that includes spaces and/or non-alphanumeric characters, etc. The brackets serve only to surround the poorly named (my opinion) object.

answered Nov 21 '17 at 19:35

Wellspring

1,128
1
10
19

Stavr00 · Accepted Answer · 2017-11-21T19:48:51.087

0

Square brackets are used for quoting entity names. Standard SQL uses ".

In this context, [For Report - Group Revision] being to the left of the period refers to a table or table-like object such as a view, report or other data source.

edited Nov 21 '17 at 19:48

answered Nov 21 '17 at 19:38

Stavr00

3,219
1
16
28

That is, square brackets, not angle. – tonypdmtr Nov 21 '17 at 19:41

score 0 · Answer 3 · answered Nov 21 '17 at 19:38

0

It is name of table or view (object) in database, after inner join comes table or a view:

Drawings INNER JOIN [For Report - Group Revision]

answered Nov 21 '17 at 19:38

MRsa

666
4
8

What does the following SQL query do?

3 Answers3