Results 1 to 5 of 5
  1. #1
    aaghd72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    Question SQL Statement - Need a field removed from the SQL

    Initially I had a field [cboVenderID] in tblPaymentInput. I realized that the Vendor ID does not need to be in that table. I have removed it. Now the sql statement for a report I have needs to be edited to remove the reference to the Vender ID field and I have tried different iterations of corrections but have not been able to achieve the desired result. Will someone please let me know what need to be removed from the following sql statement? Thanks



    SELECT tbsInvoice.ysnClosed, tblVendor.strVendorName, tblPaymentInput.dtmDatePaid, tbsInvoice.curInvoiceTotal, tbsInvoice.intInvoiceNumber, tblCreditCardData.strCardLastName, [strAubreyProvidence] & "" & [intFundingCode] AS [Funding Code], tbsInvoice.strAubreyProvidence

    FROM tblCreditCardData INNER JOIN (tblVendor INNER JOIN (tblPaymentInput INNER JOIN tbsInvoice ON (tblPaymentInput.cboInvoiceNumber = tbsInvoice.intInvoiceNumber) AND (tblPaymentInput.cboInvoiceNumber = tbsInvoice.intInvoiceNumber)) ON (tblVendor.intVendorID = tbsInvoice.[cboVendorID]) AND (tblVendor.intVendorID = tblPaymentInput.cboVendorID)) ON tblCreditCardData.intPaymentID = tblPaymentInput.cboPaymentID

    WHERE (((tbsInvoice.ysnClosed)=False) AND ((tblVendor.strVendorName) Like "*" & [Enter Vendor Name (Use * to pull all records)] & "*") AND ((tblCreditCardData.strCardLastName)="Townsend"));

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you look at the report design and see where and which VendorID is used/is not needed. Your select query which seems to be the record source of the report uses various vendorID fields to join your related tables.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It is one of the fields being joined on; you'd need to delete that join:

    ...AND (tblVendor.intVendorID = tblPaymentInput.cboVendorID...
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    aaghd72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2019
    Posts
    4

    Access - sql Statement Edit for Deleted Field

    Thank you. This worked. I appreciate everyone's time and attention to my issue!

    Karen

    Quote Originally Posted by pbaldy View Post
    It is one of the fields being joined on; you'd need to delete that join:

    ...AND (tblVendor.intVendorID = tblPaymentInput.cboVendorID...

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cancelling Selection Gets Pic Removed
    By ortizimo in forum Access
    Replies: 1
    Last Post: 03-11-2019, 09:08 AM
  2. Replies: 2
    Last Post: 10-22-2014, 11:37 AM
  3. Replies: 4
    Last Post: 01-10-2014, 01:20 PM
  4. Features removed from 2013?!
    By jmhultin in forum Access
    Replies: 2
    Last Post: 03-11-2013, 11:13 AM
  5. Removed Enter Paramater Value
    By lsingh165 in forum Access
    Replies: 2
    Last Post: 01-28-2013, 12:52 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums