locked
Trying to add feild to query RRS feed

  • Question

  • My  table tblAdditionCharge has a field called InvoiceDateInv , I am trying to add it to this query but it is not showing in my rh set of fields? Thanks for any help...............Bob

    SELECT rh.HorseID, rh.InvoiceID, rh.DayNo, rh.AdditionCharge, rh.AdditionChargeAmount
    FROM tblAdditionCharge AS rh INNER JOIN (SELECT HorseID,  max(InvoiceID) AS maxdate
        FROM tblAdditionCharge
        GROUP BY HorseID  )  AS maxresults ON (rh.InvoiceID = maxresults.[maxdate]) AND (rh.HorseID = maxresults.[HorseID])
    ORDER BY rh.DayNo, rh.AdditionCharge;


    xxx

    Monday, July 24, 2017 11:14 PM

Answers

  • What happens if you add rh.InvoiceDateInv to the column list in the SQL statement?  Does it accept it, or does it prompt for a parameter value?

    PS:  If it prompts for a parameter value my guess would be that the InvoiceDateInv is not in the tblAdditionCharge table, but in a separate Invoices table or similar which tblAdditionCharge  references on the InvoiceID column.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, July 25, 2017 10:32 AM Postscript added.
    • Marked as answer by TurnipOrange Tuesday, July 25, 2017 11:07 PM
    Tuesday, July 25, 2017 10:28 AM

All replies

  • rh is a query?

    Therefore, you must change rh to include the field InvoiceDateInv.


    peter n roth - http://PNR1.com, Maybe some useful stuff

    Tuesday, July 25, 2017 2:32 AM
  • Hi,

    If the field is in the table, then it should show up for you to add in the query. I would double-check the back end table to make sure it is there or you are referring to the correct table.

    Just my 2 cents...

    Tuesday, July 25, 2017 3:57 AM
  • What happens if you add rh.InvoiceDateInv to the column list in the SQL statement?  Does it accept it, or does it prompt for a parameter value?

    PS:  If it prompts for a parameter value my guess would be that the InvoiceDateInv is not in the tblAdditionCharge table, but in a separate Invoices table or similar which tblAdditionCharge  references on the InvoiceID column.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, July 25, 2017 10:32 AM Postscript added.
    • Marked as answer by TurnipOrange Tuesday, July 25, 2017 11:07 PM
    Tuesday, July 25, 2017 10:28 AM
  • Sorry guys it was the wrong table :/ added the field and it worked perfect..............Regards Bobp

    xxx

    Tuesday, July 25, 2017 11:08 PM
  • Glad to hear you got it sorted out. Good luck!
    Wednesday, July 26, 2017 1:49 AM