Answered by:
Trying to add feild to query

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