Max function not working as i expected

krowe

Registered User.
Local time
Yesterday, 20:13
Joined
Mar 29, 2011
Messages
159
Hi

I have a query bringing together information from lots of tables for a mail merge.

The bulk of the fields come from 2 tables, tblPerson and tblPropHistory. A Person can have many entries in PropHistory.

I need the query to return a single result, that being the the most recent property a person has occupied.

I have set up qry1 to return the correct Person (often this returns 3 or 4 results as the person has had 3 or 4 properties)

I have set up qry2 based on query 1, and have enabled total. In the field DateMovedIn i set the total to Max, expecting this to just return the result with the most recent DateMovedIn.

For some reason it returns all the properties for that Person (the same as the qry1 output).

Please can you advise where i'm going wrong.

Thanks

Kev
 
Hard to say without seeing the SQL of the query. My guess is that you're grouping on the date field.
 
My money is that he is grouping on the property, the queries should probably be in the other order, find the max date per person, then join this on date fields to the original table and extract all the other data you require.

Just a stab in the dark of course.

Brian
 
Thanks for all the replies

I am trying to set up the queries the other way around now as suggested.

The SQL i get for obtaining the max move in date is completely different to the example on BaldyWeb. I do not know how to write the sql without making the query in design view. This is what i get when i put in Max for DateMoved In:

SELECT DISTINCT tblPerson.ID, tblPropHistory.PropID, Max(tblPropHistory.MovedIn) AS MaxOfMovedIn
FROM tblPropHistory INNER JOIN tblPerson ON tblPropHistory.ID = tblPerson.ID
GROUP BY tblPerson.ID, tblPropHistory.PropID;


Any ideas what i may be doing wrong.
 
Also, here is a screenprint of my design view for the query

Thanks again
 

Attachments

  • example.JPG
    example.JPG
    91.1 KB · Views: 457
Since the personid = the Propertius you do not need to select both however I would expect the query to work for the same reason, what error is happening or is wrong with the result?

Brian
 
Hi

I am getting more than 1 property per person, and i am expecting only the property with the most recent date for each person.

I have attached the result of the query, ID 8 returns 2 properties not just the Max DateMovedIn as does ID 12 and 19.

Thanks

Kev
 

Attachments

  • resultsofqry.jpg
    resultsofqry.jpg
    101.7 KB · Views: 312
As Brian mentioned earlier, the problem is that you're grouping by property, so the result is exactly what you've asked for. I also wouldn't include the person table at this point.
 
Hi

Thanks for all your replies.

I have, with your help, managed to return just the most recent address!!!!

However...

The next step was to get the info from all the other tables in the same place for a mail merge. And i only get 3 results returned.

I have had this problem before, and it was to do with the join type, so i tried adjusting the join, but get an error saying it contains ambiguous out joins.

Please can i ask for a final bit of help with this.

here is my sql now for the qry which brings together all the data from the other tables.

The join between qryForLetterRIA2 and tblPerson had to be created in the query as Access didnt pick up there were linked fields.

SELECT qryForLetterRIA2.*, tblPerson.*, tblLandlord.*, tblAgent.*
FROM (tblAdviser INNER JOIN (qryForLetterRIA2 INNER JOIN tblPerson ON qryForLetterRIA2.tblPropHistory.ID = tblPerson.ID) ON tblAdviser.AdviserName = tblPerson.LeadAdviser) INNER JOIN (tblAgent INNER JOIN (tblLandlord INNER JOIN tblStandardLetters ON tblLandlord.LandlordID = tblStandardLetters.RIA_Landlord) ON tblAgent.AgentID = tblStandardLetters.RIA_Agent) ON tblPerson.ID = tblStandardLetters.ID;

I also attach the design view.

Thanks again

Kev
 

Attachments

  • example3.jpg
    example3.jpg
    99.1 KB · Views: 290

Users who are viewing this thread

Back
Top Bottom