Need to edit recordset thru query, MAX also needed

[roxannep]

I have two tables: Contacts and Transactions. It is a one-to-many relationship (Contacts-to-Transactions).

I am trying to come up with an editable recordset in datasheet view using this relationship. The hitch is that I need to display the Contact record only once for any and all times it finds a transaction over $500 in the Transaction table. It does need to remain editable.

Currently, I can get this far with my query and have it remain editable:

ContactID Send Newsletter Email TransactionAmt
1 Yes xyz@abc.com $1,000.00
1 Yes xyz@abc.com $516.00
2 123@567.com $678.00
3 No abc@ttt.com $560.00
3 No abc@ttt.com $1,200.00
3 No abc@ttt.com $875.00


I need:
ContactID Send Newsletter Email TransactionAmt
1 Yes xyz@abc.com $1,000.00
2 123@567.com $678.00
3 No abc@ttt.com $1,200.00

And I need to be able to toggle that "Send Newsletter" field (which is why I need it editable, but I don't want to have to scroll through 87 records for Mr. Jones to get to Mr. Jonesy). I am dealing with 800+ transaction records that meet the over $500 criteria, but really only 250 Contacts.

I have rather racked my brains out and realize it's probably staring me in the face.

 

[lupins46]

Does it make any difference which tranaction amount you get?
Do you actually need the transaction amount at all (given that you only look at items over 500)?

 

[PHV]

You may try something like this:
SELECT T.ContactID, [Send Newsletter], C.Email, T.TransactionAmt
FROM Transactions AS T INNER JOIN Contacts AS C ON T.ContactID = C.ContactID
WHERE T.TransactionAmt = (SELECT Max(TransactionAmt) FROM Transactions WHERE ContactID = T.ContactID)

A usefull link about Updatable queries:

http://msdn.microsoft.com/archive/en-us/dnaraccess/html/msdn_harness.asp

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

 

[roxannep]

Lupins46 - No, it really doesn't matter if I see the transactions. I was just trying to find a way to hone it down to one Contacts record. Does this make it easier?