Syntax Error (missing operator)

Question

Background:

We had an ADP database, but with the new Office, it is no longer supported, so I am transferring everything over to ACCDB.  I have updated this query, but it gives me a syntax error at the group of INNER JOINS.

Can someone please point out where I screwed up, this is killing me.

Thanks

Chad

SELECT P.[WorkLocationDescription] AS Source

              ,D.[WorkLocationDescription] AS Destination

              ,M.[MaterialType]

              ,  T.[Loads]

               * E.[Capacity]

               * M.[MaterialSwellFactor]

               * M.[MaterialBulkDensity] AS Tonnes

              ,IsProductive(T.[Pit_SP_AreaID], T.[DumpingLocationID], T.[MaterialTypeID]) AS IsProductive

              ,(SELECT TOP 1 RS.[GroupID]

                FROM dbo_UI_ReportSetup_Production RS

                LEFT JOIN dbo_UI_ReportSetup_Production_Account RA ON RA.[GroupID] = RS.[GroupID]

                WHERE RS.[ConfigID] = @ConfigID

                  AND (RS.[AllPitSPAreaCategory] = 1       OR RS.[PitSPAreaCategoryID]       = P.[WorkLocationCategoryID])

                  AND (RS.[AllDumpingLocationCategory] = 1 OR RS.[DumpingLocationCategoryID] = D.[WorkLocationCategoryID])

                  AND (RS.[AllMaterialCategory] = 1        OR RS.[MaterialCategoryID]        = M.[MaterialCategoryID])

                  AND (RS.[AllMaterialType] = 1            OR RS.[MaterialTypeID]            = M.[MaterialTypeID])

                  AND (RS.[AllAccount] = 1                 OR RA.[AccountID]                 = A.[AccountID])

                ORDER BY RS.[Priority]) AS GroupID

        FROM dbo_TimeCardData T

        INNER JOIN dbo_Equipment E ON E.[EquipmentID]   = T.[EquipmentID]

        INNER JOIN dbo_MaterialType M ON M.[MaterialTypeID] = T.[MaterialTypeID]

        INNER JOIN dbo_WorkLocation P ON P.[WorkLocationID] = T.[Pit_SP_AreaID]

        INNER JOIN dbo_WorkLocation D ON D.[WorkLocationID] = T.[DumpingLocationID]

        INNER JOIN dbo_WorkLocationCategory C ON C.[WorkLocationCategoryID] = P.[WorkLocationCategoryID]

        INNER JOIN dbo_EquipmentCategory Q ON Q.[CategoryID] = E.[CategoryID]

        INNER JOIN dbo_Account A ON A.[AccountID] = T.[AccountID]

        WHERE T.[Date] BETWEEN @FromDate AND @ToDate

          AND Q.[IsHauling] = 1

          AND A.[IsTraining] = 0

    ), Material AS (

        SELECT P.[GroupLabel]

              ,P.[Priority]

              ,T.[Source]

              ,T.[Destination]

              ,T.[MaterialType]

              ,CASE T.[IsProductive] WHEN 1 THEN T.[Tonnes]

                                            ELSE 0.0 END 'Productive'

              ,CASE T.[IsProductive] WHEN 0 THEN T.[Tonnes]

                                            ELSE 0.0 END 'Rehandle'

        FROM Tonnes T

        INNER JOIN dbo_UI_ReportSetup_Production P ON P.[GroupID] = T.[GroupID]

        WHERE T.Tonnes > 0

    )

SELECT [GroupLabel]

          ,[Priority]

          ,[Source]

          ,[Destination]

          ,[MaterialType]

          ,CAST(SUM([Productive]) AS decimal(19,2)) 'Productive'

          ,CAST(SUM([Rehandle]) AS decimal(19,2)) 'Rehandle'

    FROM Material

    GROUP BY [GroupLabel]

            ,[Priority]

            ,[Source]

            ,[Destination]

            ,[MaterialType]

END

Answer 1

Ok, I assumed perhaps more experience here, but it would seem you are making some good progress here.

I was in fact suggesting two steps.

That “store procedure” stub I posted, and the suggesting to add your t-sql right after was to be done in sql management studio (SSMS).

Once that store procedure was created, you THEN follow the second part as to how to call that t-sql.

So the steps are:

From SSMS – you paste in that create procedure stub, and also include your long t-sql you posted. I assume you choose a store procedure name of your choice or whatever the heck you like. I used “MyWorkQuery”, but unfortunately can’t read minds, and what name you choose is really up to you, no more so then what name you want to give a windows folder or file.

Once you paste that create proc into SSMS and run it, then you can toss out that query you just made. You only run that create store proc code one time. This will create a store procedure with your 2 parameters, and the t-sql you have. You are NOT doing this from Access, but from SSMS.

If you don’t have SSMS, or experience with SSMS, then you need to play with SSMS. I can no more suggest a location for you to deliver some pizza, but then finding out you don’t have a driver’s license means that a rather simple act of asking you to get a coffee for me is missing that you don’t drive.

As noted, if you get this right, it really is a cut + paste kind of approach. (You don’t have to read the t-sql, or debug it, or even modify it).

Once you created the store proc, then you create the PT query in Access with the “exec” part. And as noted, the important detail is that original t-sql has two parameters in it. We using this approach so we really don’t have to write code, but really just cut + paste what we have already.

If the PT query is prompting for a connection, then you not setup the connection correctly. So you need + want to address that issue. When you create the PT query, you want to create the connection – if you must, create a new FILE dsn. I am going to repeat this only one time:

ALWAYS use a FILE dsn. (failure to follow this advice will cause much pain on your part, and you will become a thirsty camlel wondering the desert looking for water).

So the ONLY PT query part you do in Access is this:

Exec MyWorkQuery '2018-05-15', '2018-05-16'

You want to get the above query working in Access. Don’t try code, forms and all kinds of other stuff.

Get the PT query working – and replace the above dates with some known dates you have. Once again, we are cut + paste here. We not writing code, and we really not working hard. So far, we just playing with the mouse and cutting and pasting stuff.

So, in summary:

Steps are:

Create the store proc – that is done 100% outside of Access. (with SSMS)

If the store proc is created success, then next up is to create the Access PT query. And inside of that PT query for testing you can use the above exec.

Exec MyWorkQuery '2018-05-15', '2018-05-16'

So the above is what you place in that PT query. You don’t really want to attempt or mess with the first part I have above with “create procedure” inside of Access. You really (but really!) want to do that kind of stuff from SSMS – not from access.

So after the create proc works. Then next is to create a PT query in Access. At this point in time you not written any VBA, not modified any Access VBA code at all.

So the working assuming here is that you have some basic skills to work with SQL server and SSMS. If you don’t have use of SSMS, then you are really dead in the water.

Once you get the above two steps working, then you can now look at say the VBA code you have to launch the report with the date parameters, and get that working (so that would be step 3 in this process).

As noted, depending on the VBA code used to launch the report and “how” it gets the date will in fact determine if my advice to use a store proc (in placed of the OTHER suggesting to use a view). As noted, you can consider a view, but you have to modify your existing SQL, and I am suggesting you adopt a cut + paste approach in which you don’t really care to read, or even know how the t-sql works. I mean, ok, take a look as you cut + paste, but really why waste brain power and time reading something you already know has been working for many years.

So a basic skill set to work with SQL server is required here. (and thus did not realize you would not know where to paste + run the create procedure part (you do that in SSMS – not access).

So the first 2 steps can be accomplished without writing any VBA code, and the steps are in fact a mechanical cut + paste approach.

Total time here is less then what it will take you to read this post.

So you are 100% correct. The creating of the store proc does not return records, but I assume you spooled up a copy of the sql management studio (SSMS). That part of creating the t-sql procedure will NOT be done from Access. (it can be, but I don’t recommend you do this – too much work and pain).

So before anything of above, you need to be using + running SSMS.

Regards,

Albert D. Kallal (Access MVP, 2003-2017)

Edmonton, Alberta Canada

Answer 2

I found a bunch of mistakes.  I have modified it.  Now I get syntax error in union query.

(SELECT P.[WorkLocationDescription] AS Source

              ,D.[WorkLocationDescription] AS Destination

              ,M.[MaterialType]

              ,  T.[Loads]

               * E.[Capacity]

               * M.[MaterialSwellFactor]

               * M.[MaterialBulkDensity] AS Tonnes

              ,fIsProductive(T.[Pit_SP_AreaID], T.[DumpingLocationID], T.[MaterialTypeID]) AS IsProductive

              ,(SELECT TOP 1 RS.[GroupID]

                FROM (dbo_UI_ReportSetup_Production RS

                LEFT JOIN dbo_UI_ReportSetup_Production_Account RA ON RA.[GroupID] = RS.[GroupID])

                WHERE RS.[ConfigID] = @ConfigID

                  AND (RS.[AllPitSPAreaCategory] = 1       OR RS.[PitSPAreaCategoryID]       = P.[WorkLocationCategoryID])

                  AND (RS.[AllDumpingLocationCategory] = 1 OR RS.[DumpingLocationCategoryID] = D.[WorkLocationCategoryID])

                  AND (RS.[AllMaterialCategory] = 1        OR RS.[MaterialCategoryID]        = M.[MaterialCategoryID])

                  AND (RS.[AllMaterialType] = 1            OR RS.[MaterialTypeID]            = M.[MaterialTypeID])

                  AND (RS.[AllAccount] = 1                 OR RA.[AccountID]                 = A.[AccountID])

                ORDER BY RS.[Priority])

        FROM (((((((dbo_TimeCardData T

        INNER JOIN dbo_Equipment E ON E.[EquipmentID] = T.[EquipmentID])

        INNER JOIN dbo_MaterialType M ON M.[MaterialTypeID] = T.[MaterialTypeID])

        INNER JOIN dbo_WorkLocation P ON P.[WorkLocationID] = T.[Pit_SP_AreaID])

        INNER JOIN dbo_WorkLocation D ON D.[WorkLocationID] = T.[DumpingLocationID])

        INNER JOIN dbo_WorkLocationCategory C ON C.[WorkLocationCategoryID] = P.[WorkLocationCategoryID])

        INNER JOIN dbo_EquipmentCategory Q ON Q.[CategoryID] = E.[CategoryID])

        INNER JOIN dbo_Account A ON A.[AccountID] = T.[AccountID])

WHERE T.[Date] BETWEEN @FromDate AND @ToDate

          AND Q.[IsHauling] = 1

          AND A.[IsTraining] = 0)

UNION

(SELECT P.[GroupLabel]

              ,P.[Priority]

              ,T.[Source]

              ,T.[Destination]

              ,T.[MaterialType]

              ,iif(T.[IsProductive] = 1, T.[Tonnes], 0.0) AS Productive

              ,iif(T.[IsProductive] = 0, T.[Tonnes], 0.0) AS Rehandle

        FROM (Tonnes T

        INNER JOIN dbo_UI_ReportSetup_Production P ON P.[GroupID] = T.[GroupID])

        WHERE T.[Tonnes] > 0)

UNION

(SELECT [GroupLabel]

          ,[Priority]

          ,[Source]

          ,[Destination]

          ,[MaterialType]

          ,CLng(SUM([Productive]) AS Productive

          ,CLng(SUM([Rehandle]) AS Rehandle

    FROM Material

    GROUP BY [GroupLabel]

            ,[Priority]

            ,[Source]

            ,[Destination]

            ,[MaterialType])

Answer 3

I did not study in detail, but to debug a union query you would create a temporary query for each part, and debug it.

Of course the parts of a union query all have to use the same number of columns and the same data types.

Answer 4

At least a couple of things: the fields don't match between the two UNIONS (Source, Destination, Tonnes ... vs. GroupLabel, Priority, Source Destination, MaterialType...); and you're using the T-SQL Case and Cast syntax which does not exist in the ACE dialect of SQL. You may be able to set the SQL level but I haven't worked with that!

Answer 5

I think I screwed up. I don't think it was a union statement in my ADP. Is it possible to run a select statement and then run a different select statement  on that live data?

I think that is what was happening in my ADP. There was no mention of unions in the ADP code.