Help with Converting an SQL query statement

I have a program in VB 6 that uses a query that I need to pull out and move into Microsoft Access or just in SQL. I keep getting some syntax errors. Can someone help translate this for me?

SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR " & _"FROM PRODDTA.F4101 INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)) INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM " & _
    "GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT "
 
Nmark80Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dbaSQLCommented:
Is this the entire query?  I see a number of oddities, but first, what is the error you are receiving?
0
Nmark80Author Commented:
Well when I type it into the SQL editor in Access it states invalid syntax. The tables and structure here is frustrating which is what makes it harder for me when trying to convert this. THe error states "the select statement contains a reserved word ..."
0
dbaSQLCommented:
Well, not sure about that, but try this.  Your double quotes and &'s are gone -- syntactically, this is acceptable.  


SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR
FROM PRODDTA.F4101
INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM))
INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
         PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

ee_rleeCommented:
try this
SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR FROM PRODDTA.F4101 INNER JOIN ((PRODDTA.F4102 INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU) AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)) INNER JOIN PRODDTA.F0101 ON PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8) ON PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT

Open in new window

0
Nmark80Author Commented:
It is now stating that there is a Syntax error in the join operation. Any thoughts?
0
ee_rleeCommented:
how about this one?
SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR 
FROM PRODDTA.F4101 
INNER JOIN PRODDTA.F4102 ON (PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM)
INNER JOIN PRODDTA.F41021 ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU AND PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM) 
INNER JOIN PRODDTA.F0101 ON (PRODDTA.F4102.IBVEND = PRODDTA.F0101.ABAN8)  
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND, PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT

Open in new window

0
dbaSQLCommented:
again, hard to say, as i don't have the access editor, but try this:

SELECT PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT, PRODDTA.F0101.ABALPH,
Sum(PRODDTA.F41021.LIPQOH) AS SumOfLIPQOH, Sum(PRODDTA.F41021.LIPBCK) AS SumOfLIPBCK, Sum(PRODDTA.F41021.LIPREQ) AS SumOfLIPREQ,
Sum(PRODDTA.F41021.LIQTTR) AS SumOfLIQTTR FROM PRODDTA.F4101 INNER JOIN PRODDTA.F4102
                                                       ON (PRODDTA.F4102.IBMCU = PRODDTA.F41021.LIMCU)
                                                   AND (PRODDTA.F4102.IBITM = PRODDTA.F41021.LIITM)
                                                      INNER JOIN PRODDTA.F0101
                                                              ON PRODDTA.F0101.ABAN8=PRODDTA.F4102.IBVEND
                                                             AND PRODDTA.F4101.IMITM = PRODDTA.F4102.IBITM
GROUP BY PRODDTA.F4102.IBLITM, PRODDTA.F4102.IBAITM, PRODDTA.F4101.IMDSC2, PRODDTA.F4102.IBMCU, PRODDTA.F4102.IBPRP4, PRODDTA.F4102.IBVEND,
         PRODDTA.F0101.ABALPH, PRODDTA.F4102.IBSTKT, PRODDTA.F4102.IBBACK, PRODDTA.F4102.IBCKAV, PRODDTA.F4102.IBGLPT
0
Anthony PerkinsCommented:
You do realize the SQL syntax is different in MS Access as opposed to MS SQL Server, right?
0
dbaSQLCommented:
yep.  to my knowledge, the OUTER JOIN is not supported by Access.  aside from that, i believe the access join has the ON condition just as sql does.  and the right/left is handled the same, is it not?

>>>>>>>>>  Use a LEFT JOIN operation to create a left outer join. Left outer joins include all of the records from the first (left) of two tables, even if there are no matching values for records in the second (right) table.

Use a RIGHT JOIN operation to create a right outer join. Right outer joins include all of the records from the second (right) of two tables, even if there are no matching values for records in the first (left) table.

http://msdn2.microsoft.com/en-us/library/bb208854.aspx
http://msdn2.microsoft.com/en-us/library/bb208894.aspx

0
Anthony PerkinsCommented:
My comment was really directed at the questioner.

>>i believe the access join has the ON condition just as sql does.  and the right/left is handled the same, is it not?<<  
Yes but I don'e believe that MS Access supports a 2 part naming convention (owner name and table name) for the FROM clause.

0
dbaSQLCommented:
>>My comment was really directed at the questioner.
silly me.  sorry bout that
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.