Solved

Issue with MS Access Query Builder

Posted on 2014-02-04
12
524 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
  • 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
 
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now