Solved

Issue with MS Access Query Builder

Posted on 2014-02-04
12
539 Views
Last Modified: 2014-02-09
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
Comment
Question by:lorenda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
12 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39833871
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
 
LVL 6

Author Comment

by:lorenda
ID: 39833948
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39833984
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
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 6

Author Comment

by:lorenda
ID: 39835645
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39835912
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
 
LVL 1

Accepted Solution

by:
SarahDaisy8 earned 500 total points
ID: 39836388
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39836400
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
 
LVL 6

Author Comment

by:lorenda
ID: 39838947
Jim - tried the above; I'm still getting a syntax (missing operator) error.
0
 
LVL 6

Author Comment

by:lorenda
ID: 39838953
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39839227
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
 
LVL 6

Author Comment

by:lorenda
ID: 39839691
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
 
LVL 38

Expert Comment

by:Jim P.
ID: 39839760
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

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question