Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2013
    Posts
    6

    Unanswered: Parameter prompt on query trying to retrieve most current record

    I have got the following query to retrieve the most current rate for a unique id (returns the onshore_adder amount for a particular personnel_no based on on the latest weekending date for that record):-

    Code:
    select distinct t1.personnel_no, t1.performer_name, t1.onshore_adder, t1.week_ending
    from expense_adders t1
    inner join  
    (		select personnel_no, max(week_ending) as max_week_ending
    		from 	expense_adders
    		group by personnel_no
    	) t2
    on  	t1.personnel_no = t2.personnel_no
    and 	t1.week_ending = t2.max_week_ending
    However, when I run it I am being prompted for parameter for personnel_no and then again for t1.personnel_no.

    If I just enter 1 for both parameters I get the right results for performer_name, onshore_adder & weekending date but obviously the personnel_no is all shown as "1".

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    that message means the SQL runtime cannot work out what the specified column name is
    usually thats down to a typo or missing column name.

    in this case I suspect
    Code:
    ....from expense_adders t1
    should read
    Code:
    ....from expense_adders as t1
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    6
    it's ok - solved

    I had two underscores in field name personnel__no by mistake

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
  •