Want last record only from a 1 to many linked table.

[Chauncey]

I have a query that successfully allows me every record in the first table.
BUT, for EACH of those records it shows all linked records in the second
table. I only want to see the last entry in the linked table.

Hellllp.

 

[Roger Carlson]

What do you mean by "last"? In Access, last and first are slippery
concepts. If you mean the latest one entered, Access won't know. You have
to have some field which will show its positioin. For instance, if there is
a date field, you can find the maximum date.

Even then, the issue is not all that easy. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"MaxQueryProblem.mdb" which illustrates the problem and how to correct it.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[pietlinden]

I have a query that successfully allows me every record in the first table.
BUT, for EACH of those records it shows all linked records in the second
table. I only want to see the last entry in the linked table.

Hellllp.

Since data in tables are not ordered, you would need some kind of
autonumber or date stamp to determine the "last" record. "Last" is a
*relative* position, so how can you determine that given the fields
you have? Once we know that, your problem can be solved, I think...

 

[Lord Kelvan]

well you could also use the primarykey of the second field if it is an
autonumber but i wouldnt trust that as far as i could throw it and i
dont feel like picking up my computer

if you wanted to do that you would have to use 'IN' something like

SELECT *
FROM table1, table2
where table1.table1id= table2.table1
and table2id in (select max(table2id) from table2 group by table1id);

which will give you the last record entered based on a autonumber
primary key as long as data is only entered sequentionaly and soneone
dosnt go and enter a record form 3 years ago or a hundered of other
possibilities which could mean that that record isnt the most current
record but it is a stop gap

hope this helps