Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Issue with MS Access Query Builder

Posted on 2014-02-04
12
Medium Priority
?
564 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 1500 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

596 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