Using the Dlookup function on a report 2

[jgarnick]

I have a report that I need to display a field from a table that is not in the recordsource. I actually need to display the most recent data in this field (based on a date in the same table as the needed field).<br><br>I tried putting a dlookup expression in the query, but I can't get it to find the data for each person ([id]).<br>It finds a field with the data in it and just displays that same field for each record.&nbsp;&nbsp;&nbsp;<br><br>Example of report:<br><br>Name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Residency date&nbsp;&nbsp;&nbsp;&nbsp;Fellowship<br>Table 1&nbsp;&nbsp;&nbsp;Table 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Table 3<br>&nbsp;<br>The report's query is based on Table 1 and Table 2.&nbsp;&nbsp;The three tables are related by an id field. If I try to include table 3 in the query it does not pick up all the appropriate records because of the joins.<br><br>Is there a way to lookup the fellowship field in table 3 for the id in table 1 & 2?&nbsp;&nbsp;And the trickier part is finding the most recent fellowship data based on a date in the same table!<br><br>Thanks for any help! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[Cornerstone]

So I assume you did add the ID field to table 3? Good. Now you just need to create an Outer Join in your query. Try to add the third table back into the query and this time double click the join line between table3 and it's related table and choose to display all of the records from the first table and the records from table 3 where the join field is equal. Let me know how that turns out.

 

[jgarnick]

Good memory!&nbsp;&nbsp;Actually this is a different problem--I went about the other problem in a different way without adding the id---the situation is more complicated than my posting described.&nbsp;&nbsp;I think I accomplished what I set out to do--if not, you'll see it posted again hopefully in a clearer summary!<br><br>I'll try what you said above for my new problem and let you know!!<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[jgarnick]

I tried adding the 3rd table into my query again --i have the join set to All records from table 1 and only the records from table 3 where joined fields are equal.&nbsp;&nbsp;But it doesn't work--it brings people up multiple times.<br><br>Help!&nbsp;&nbsp;Can't it be done with dlookup?&nbsp;&nbsp;I really don't think I can get the right records using the three tables in a query................<br><br>Thanks- <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[gol4]

Not sure of the whole situation here but if still having problems with records duplicating, might I suggest you make a query based on the first 2 tables then create a second query based on the query you just made and table 3.

 

[jgarnick]

I'm tackling this problem again--I created a query that combines table 1 and table 2 and added table 3 into my report's recordsource.&nbsp;&nbsp;I tried using all three types of joins and they all produce the same thing--duplicate lines for each person.<br><br>Again, what I need is, from table 3, I need to find the most recent data from a particular field based on another field's date in the same record.&nbsp;&nbsp;I then need to drop this data on my report along with fields from table 1 and 2 (or the combined query).&nbsp;&nbsp;I know there must be a way!!<br><br>Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[gol4]

still not clear on your table structure but here goes. If tables look like this<br>&nbsp;&nbsp;&nbsp;<br>Table 1&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;id&nbsp;&nbsp;Name&nbsp;&nbsp;etc<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>Table 2&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>id&nbsp;&nbsp;residencydate<br>&nbsp;<br>Table 3<br>ID Fellowship&nbsp;&nbsp;date<br><br>and the joins on ID are one to many (inner)it will produce multiple records if they have more then one residency and&nbsp;&nbsp;more then one fellowship. If you add an additional join on the date fields (if it exists) it will then produce one fellowship for each residency you can then ask for max date which will give one record. <br>Good Luck