Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 579
  • Last Modified:

Issue with MS Access Query Builder

I have a query (that I know runs successfully as I pulled it straight from the SQR), but I'm having lots of fun (<--sarcasm) trying to rebuild the query in MS Access. Hoping someone here can give me some help on how I need to change the below into "Access-speak"

I can confirm that every table/field in the below exists and is visible to Access. I have all of these tables linked in my Access db. However, when I run the below, I'm receiving a syntax error on the line of the first join.

SELECT
MAINVDTL.COMPANY,
MAINVDTL.ITEM,
MAINVDTL.ITEM_DESC,
MAINVDTL.ITEM_TYPE,
MAINVDTL.LINE_NBR,
MAINVDTL.VENDOR,
MAINVDTL.VEN_ITEM,
MAINVDTL.VBUY_UOM,
MAINVDTL.TAX_AMOUNT,
MAINVDTL.TOT_BASE_AMT,
MAINVDTL.DISTRIB_DATE,
HCCHRGITEM.CHARGE_NBR,
Trim(PURCHORDER.PO_NUMBER) AS PO_NUMBER,
PURCHORDER.PO_DATE,
PURCHORDER.COMPANY,
PURCHORDER.USER_ID,
PURCHORDER.PO_USER_FLD_3,
PURCHORDER.PO_USER_FLD_5,
L_HPCR.R_NAME,
L_HPCR.OBJECT,
APINVOICE.INVOICE,
APINVOICE.proc_level,
APPAYMENT.CHECK_DATE,
Trim(APPAYMENT.TRANS_NBR) AS TRANS_NBR,
APPAYMENT.TRANS_IDENT,
APPAYMENT.void_date,
MMDIST.ACCT_UNIT
FROM MAINVDTL

LEFT JOIN HCCHRGITEM
ON MAINVDTL.ITEM=HCCHRGITEM.ITEM

LEFT JOIN PURCHORDER
      ON PURCHORDER.COMPANY = MAINVDTL.COMPANY
     AND PURCHORDER.PO_NUMBER = MAINVDTL.PO_NUMBER
     AND PURCHORDER.PO_RELEASE = MAINVDTL.PO_RELEASE
     AND PURCHORDER.PO_CODE = MAINVDTL.PO_CODE

LEFT JOIN L_HPCR
                  ON PURCHORDER.L_INDEX=L_HPCR.L_INDEX
                AND L_HPCR.ATCHNBR='zz'
     
LEFT JOIN APINVOICE
      ON MAINVDTL.COMPANY = APINVOICE.COMPANY
     AND MAINVDTL.VENDOR = APINVOICE.VENDOR
     AND MAINVDTL.INVOICE = APINVOICE.INVOICE
     AND APINVOICE.proc_level = [Enter Process Level]
         
inner JOIN APPAYMENT
      ON APPAYMENT.COMPANY = APINVOICE.COMPANY
     AND APPAYMENT.VENDOR = APINVOICE.VENDOR
     AND APPAYMENT.INVOICE = APINVOICE.INVOICE
     AND APPAYMENT.SUFFIX = APINVOICE.SUFFIX
     AND APPAYMENT.CANCEL_SEQ = APINVOICE.CANCEL_SEQ
     AND APPAYMENT.CHECK_DATE >= [Beg Date]
     AND APPAYMENT.CHECK_DATE <= [End Date]
     and APPAYMENT.VOID_DATE=#01/01/1700#

LEFT JOIN MMDIST
      ON MAINVDTL.COMPANY = MMDIST.COMPANY
     AND MAINVDTL.PO_NUMBER = MMDIST.DOC_NUMBER
     AND MAINVDTL.PO_RELEASE = MMDIST.DOC_NBR_NUM
     AND MAINVDTL.PO_CODE = MMDIST.PO_CODE
     AND MAINVDTL.LINE_NBR = MMDIST.LINE_NBR
     AND MAINVDTL.LOCATION = MMDIST.LOCATION
     AND MMDIST.SYSTEM_CD='PO'
     AND MMDIST.DOC_TYPE='PT'
WHERE
mainvdtl.COMPANY=1000
AND MAINVDTL.VENDOR=[Enter Vendor]
AND mainvdtl.TAX_AMOUNT=0
0
Lorenda Christensen
Asked:
Lorenda Christensen
  • 6
  • 5
1 Solution
 
Jim P.Commented:
SQR -- Peoplesoft?

This is the join you are talking about:
LEFT JOIN HCCHRGITEM
ON MAINVDTL.ITEM=HCCHRGITEM.ITEM

Open in new window


What is the data types on both sides of the join? If it is a bigint MS Access can't handle it. The largest Access can handle is the SQL integer which is a Long Integer for MS Access and is 2,147,483,647.
0
 
Lorenda ChristensenAccounting and VBA ConsultantAuthor Commented:
Yeah - sorry. Peoplesoft background - SQR is probably not the correct term as I'm pretty sure these tables are housed in business objects.

Both of the field types here are Long Integer, and none of them should exceed MS Access limit...they are usually 5-7 digits long.

I tried using the parentheses that MS Access is so fond of, and managed to get a general "Syntax error in FROM clause" error instead of one that specifically points at this row. So maybe I need parentheses? If so, I'd love some help with the placements.
0
 
Jim P.Commented:
Can you open the tables in datasheet view just from the Access window and see data?

The brackets generally aren't needed unless there are special characters or spaces in the table names.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
Lorenda ChristensenAccounting and VBA ConsultantAuthor Commented:
Yes - I can see all data when opening my linked tables in Datasheet view. And the brackets ARE sometimes needed for these Lawson tables, so I include them on everything; Access might not need them, but it's never caused an error before.
0
 
Jim P.Commented:
Try this on:

SELECT
[MAINVDTL].[COMPANY],
[MAINVDTL].[ITEM],
[MAINVDTL].[ITEM_DESC],
[MAINVDTL].[ITEM_TYPE],
[MAINVDTL].[LINE_NBR],
[MAINVDTL].[VENDOR],
[MAINVDTL].[VEN_ITEM],
[MAINVDTL].[VBUY_UOM],
[MAINVDTL].[TAX_AMOUNT],
[MAINVDTL].[TOT_BASE_AMT],
[MAINVDTL].[DISTRIB_DATE],
[HCCHRGITEM].[CHARGE_NBR],
Trim([PURCHORDER].[PO_NUMBER]) AS PO_NUM,
[PURCHORDER].[PO_DATE],
[PURCHORDER].[COMPANY],
[PURCHORDER].[USER_ID],
[PURCHORDER].[PO_USER_FLD_3],
[PURCHORDER].[PO_USER_FLD_5],
[L_HPCR].[R_NAME],
[L_HPCR].[OBJECT],
[APINVOICE].[INVOICE],
[APINVOICE].[proc_level],
[APPAYMENT].[CHECK_DATE],
[Trim(APPAYMENT].[TRANS_NBR) AS TRANS_NBR],
[APPAYMENT].[TRANS_IDENT],
[APPAYMENT].[void_date],
[MMDIST].[ACCT_UNIT
FROM [MAINVDTL]

LEFT JOIN [HCCHRGITEM]
ON [MAINVDTL].[ITEM=HCCHRGITEM].[ITEM

LEFT JOIN [PURCHORDER]
      ON [PURCHORDER].[COMPANY] = [MAINVDTL].[COMPANY]
     AND [PURCHORDER].[PO_NUMBER] = [MAINVDTL].[PO_NUMBER]
     AND [PURCHORDER].[PO_RELEASE] = [MAINVDTL].[PO_RELEASE]
     AND [PURCHORDER].[PO_CODE] = [MAINVDTL].[PO_CODE]

LEFT JOIN [L_HPCR]
                  ON [PURCHORDER].[L_INDEX] = [L_HPCR].[L_INDEX ]
                AND [L_HPCR].[ATCHNBR] ='zz'

LEFT JOIN [APINVOICE]
      ON [MAINVDTL].[COMPANY] = [APINVOICE].[COMPANY]
     AND [MAINVDTL].[VENDOR] = [APINVOICE].[VENDOR]
     AND [MAINVDTL].[INVOICE] = [APINVOICE].[INVOICE]
     AND [APINVOICE].[proc_level] = [Enter Process Level]

inner JOIN [APPAYMENT]
      ON [APPAYMENT].[COMPANY] = [APINVOICE].[COMPANY]
     AND [APPAYMENT].[VENDOR] = [APINVOICE].[VENDOR]
     AND [APPAYMENT].[INVOICE] = [APINVOICE].[INVOICE]
     AND [APPAYMENT].[SUFFIX] = [APINVOICE].[SUFFIX]
     AND [APPAYMENT].[CANCEL_SEQ] = [APINVOICE].[CANCEL_SEQ]
     AND [APPAYMENT].[CHECK_DATE] >= [Beg Date]
     AND [APPAYMENT].[CHECK_DATE] <= [End Date]
     AND [APPAYMENT].[VOID_DATE] = [#01/01/1700#

LEFT JOIN [MMDIST]
      ON [MAINVDTL].[COMPANY] = [MMDIST].[COMPANY
     AND [MAINVDTL].[PO_NUMBER] = [MMDIST].[DOC_NUMBER
     AND [MAINVDTL].[PO_RELEASE] = [MMDIST].[DOC_NBR_NUM
     AND [MAINVDTL].[PO_CODE] = [MMDIST].[PO_CODE
     AND [MAINVDTL].[LINE_NBR] = [MMDIST].[LINE_NBR
     AND [MAINVDTL].[LOCATION] = [MMDIST].[LOCATION
     AND [MMDIST].[SYSTEM_CD='PO'
     AND [MMDIST].[DOC_TYPE='PT'
WHERE [mainvdtl].[COMPANY] = 1000
AND [MAINVDTL].[VENDOR]= [Enter Vendor]
AND [mainvdtl].[TAX_AMOUNT] = 0

Open in new window

0
 
SarahDaisy8Commented:
Sometimes what I do is write my SQL in the SQL view and list the tables or sub queries with commas and no joins.  Then I go into the design mode and connect them where I need them.  I'm used to writing the SQL like you have above, but you are right Access LOVES those complicated hard to follow parenthesis.  Not sure if that's helpful, but thought I would share.
0
 
Jim P.Commented:
Another note: I changed this to Trim([PURCHORDER].[PO_NUMBER]) AS PO_NUM. You shouldn't really use the AS to go back to the original column name. It can confuse Access.
0
 
Lorenda ChristensenAccounting and VBA ConsultantAuthor Commented:
Jim - tried the above; I'm still getting a syntax (missing operator) error.
0
 
Lorenda ChristensenAccounting and VBA ConsultantAuthor Commented:
Sarah - actually, I tried the above, and got about 100x's the records I was expecting.

I need to do some research on this, but I'm beginning to wonder whether the system this query was pulled from (Lawson Financials) has some sort of pre-set join schema that is running behind the scenes in addition to this SQL...because I've never seen a left outer join switch left tables mid statement

i.e. the first join goes from MAININVDTL to HCCHRGITEM; the second one flip-flops and pulls PURCHORDER to MAININVDTL instead of the other way around.
0
 
Jim P.Commented:
Without having your data or database troubleshooting this remotely is hard.

I would suggest you start by removing some of the fields/tables first. Then add them back after the base query works.
0
 
Lorenda ChristensenAccounting and VBA ConsultantAuthor Commented:
That's what Sarah suggested (or at least close to it--at least that's what I did). But I'm returning far too many records.

Which is why I think there must be a system-setting that is forcing one of the left outer joins into an inner join behind the scenes of this Lawson setup.
0
 
Jim P.Commented:
Add in Where clauses, such as WHERE [MAINVDTL].[DISTRIB_DATE] > Date()-30

Just basic stuff until you get closer to what you want for results and joins. They're easy enough to delete later.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now