Filtering the last record of many from many people using totals in query design view

[not a code fan]

I have made a couple of posts about this and had no response as yet. Maybe i didn't explain it well.

I have a form that logs emails. The emails are either "to" or "from" a person. I want to have two queries that pick out either to or from. Now each person i know sends me several emails over time obviously but i am only concerned with their last email or MY last email to THEM because that determines if i should email them back or if i am waiting for them to contact me. At the moment i am having problems filtering out the last record for each person which determines whether i need to email them or they need to email me. I also use the record to log when the last mobile text was sent, phone call etc so it is not just emails. The following is what my formsfields look like

CommunicationID
Communication type
TO or From
Date
Day (automatically taken from the date)
Subject

I cannot for the life of me get the filter to work properly. Currently i am either getting more than one record for each person or the results are jumbled up. I really need a better understanding of how to use the totals thingy in the querys design view. I'd really appreciate some help on this. Thanks....Ross

 

[DanG]

As always there is more than one way to skin a cat...so with that said, I am sure there are better ways of doing this but here's how I did it.

You will need 2 queries:
Query 1 (Totals query and unique records set to "Yes")
To/From (group by)
Date (Max)
When this query is run you should see the latest date for each "to/from" that you have grouped on.

Query 2
bring in query1 and also the table/query that has all the fields you will need.
Then link the To/From in q1 to the To/From in the table/query you brought in and then the same for the Date fields. Then base your form off of q2.

Dan

 

[not a code fan]

Thanks for your help, I tried what you said. I think i do properly understand what you descibed but i get the feeeling it might be something more sinester than i originally thought. Sometimes the CommunicationType field and also the ToFrom field are correct and other times they are not. If choose max i think it sorts the fields content alphabetically. If i choose last i don't know what it does because some are right and some are wrong with no logical explanation i can see.

The other fields all seem to be ok. I tried using another query and linking it to the first but no luck so i have gone back to something similar to what i had which is...

Communication type (tblCommunicatonRec) =Last
ToFrom (tblCommunicatonRec) = Last
First Name (tblContacts) = Group By / Ascending
Last Name (tblContacts) = Group By / Ascending
Date (tblCommunicatonRec) = Max
CommunicationID (tblCommunicatonRec) =Max
Day (tblCommunicatonRec) = Last

If i change "ToFrom" or "CommunicationType" from Max to Last or vice versa it gives confusing results, neither of which seem more right than the other

Any more ideas???

 

[RV]

"Last" won't give you the last record...

Use the MAX function on a column with data type Date or on a column with data type Autonumber to retrieve the last record

RV

 

[not a code fan]

I've been trying both last and Max in various combinations and it doesn't seem to work. If you have mentioned in other threads why Max is better to use in other can you give me a clue where to find them? I'd also find it really useful if there was something that explained a bit better what max min first last, sum etc do and how they can work together. I understand in part already but sometimes it's good to ask what seems like a daft question as you often learn more than you thought you knew already. I certainly know that i need more understanding of the above.