Trying to select a single row from many side table

[user-hostile]

Hello. I thought I could handle this one, but I'm just not getting anywhere. My situation is pretty simple: I have two tables, with a one-to-many relationship. Both tables have primary keys, and the primary and foreign keys are set up correctly. Referential integrity is enforced. It's a pretty simple join/relationship. OK...

If I build a query and select a row from my "one" side table, obviously 0, 1, or more than 1 related records can be selected in the "many" side table. Very ordinary, and most of the time, that's what I need.

But now, instead of returning the "many" related records from the "many" side table, I want to select only ONE record, based on the most recent date value in one of the date fields. That is, if I build a query that selects a particular record on the "one" side, I want ONLY the most recent (by virtue of the date field) record from the "many" side table to return. I don't want the other records that are related. AND, I need to be able to return (in the same record set) the "many" side table's unique ID.

I'm writing on the fly SQL below (don't have Access on this machine), so it may not be exact, but here's an idea what I'm trying for:

SELECT Max(ManyTable.MyDate) as MaxDate, ManyTable.ManyTableID, OneTable.OneTableID FROM ManyTable INNER JOIN OneTable ON OneTable.OneTableID = ManyTable.OneTableID

This SQL returns all the related records from the "many" table, even though I thought my Max() function would only return the latest date value.

SUMMARY: I just want a "temporary" one-to-one recordset in my query, with the one (on the "many" side) simply the record that has the greatest date value in my specified date field. I've tried a dozen different totals queries, crosstabs, etc. I can't believe I'm having such a block about this. Any help is appreciated; thanks.


U-H

 

[Rabbie]

Try looking up TOP in Access help

 

[user-hostile]

Try looking up TOP in Access help

Thanks, but that doesn't work in my situation.

Any other ideas?


U-H

 

[gemma-the-husky]

rabbie must be right

if you need just one record then changing to a totals query, and selecting top1 from a sorted date field MUST give you a single record that fits your bill.

 

[user-hostile]

rabbie must be right

if you need just one record then changing to a totals query, and selecting top1 from a sorted date field MUST give you a single record that fits your bill.

OOH--you gave me an insight. I actually need ALL the records from my parent table, but if I include the TOP 1 keyword in a _subquery_, the subquery just might return the one record I want. Thanks very much; I'll give it another try.


U-H

 

[dane-croatia]

How I can to add two columns from table, that I get new column with add?
I'm sorry on bad English.

 

[The_Doc_Man]

SELECT Max(ManyTable.MyDate) as MaxDate, ManyTable.ManyTableID, OneTable.OneTableID FROM ManyTable INNER JOIN OneTable ON OneTable.OneTableID = ManyTable.OneTableID

What is wrong with this is that the MAX is in the wrong place.

SELECT ManyTable.MyDate as MaxDate, ManyTable.ManyTableID, OneTable.OneTableID FROM ManyTable INNER JOIN OneTable ON OneTable.OneTableID = ManyTable.OneTableID WHERE OneTable.OneTableID = DMAX("ManyTable","[OneTableID]");

Or something like that. Don't quote me though. The syntax might be a bit tougher than this, but the idea is there.

Make the WHERE clause select the maximum value