problem of subquery in from clause?!

[dogdog172]

i am using Access 2002 and iis

I have two tables, one (table "member") is to store the general members' details such as name and phone number, another ("payment") is to store the date when members pay the fee. the second table got only three fields, one is the member_id which can be used to join the member table, second is the paid_date and the third field is just a unique key. the payment table can have multiple rows regarding to one member if he/she has paid membership fee more than once. (actually, members is charged monthly)

now, i would like to find out the latest date when all the members who had paid the fee together with their general details such as name. what i am trying to do is:

sql = "select m.english_name, m.surname, m.firstname, p.paid_date from members m, (select member_id, max(paid_date) from payment group by member_id) p where m.member_id = p.member_id"

but i got the error saying: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10) [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

any ideas? thx in advance.

 

[Golom]

You haven't supplied a name for the computed MAX(paid_Date) field. You need something like

sql = "select m.english_name, m.surname, m.firstname, p.[COLOR=red][Last_Paid][/color] from members m, (select member_id, max(paid_date) [COLOR=red]As [Last_Paid][/color] from payment group by member_id) p where m.member_id = p.member_id"

 

[AnonGod]

Just pulled this out of Access. See if this works for ya.
tables named m and p
-Andrew

SELECT m.english_name, m.surname, m.firstname, Max(p.paid_date) AS MaxOfpaid_date
FROM p INNER JOIN m ON p.member_id = m.member_id
GROUP BY p.member_id, m.english_name, m.surname, m.firstname;

 

[dogdog172]

thanks so much guys.

i got an error by using AnonGod's query, things like microsoft jet database engine cannot find the input table or query 'p'. Thx for your try anyway.

and it works with Colom's one, cheers!

 

[PHV]

sql = "SELECT m.english_name, m.surname, m.firstname, Max(p.paid_date) AS Last_Paid
& " FROM payment p INNER JOIN members m ON p.member_id = m.member_id" _
& " GROUP BY m.english_name, m.surname, m.firstname"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244