To a large extent you’ve stumped me … but I know that one of the main tenets of the relational database model is you are not supposed to care or even know about the order in which the DBMS chooses to store the data. So it’s not surprising that Access doesn’t have many (or any) facilities for extracting records based on their order in the database.
That is, you should probably have a column in those subforms that can be used to determine the order, then you can just extract one based on being the maximum value in that column, or whatever.
Each form is associated with a specific table (obviously) in the query wizard, you need to include both (or more) tables for the query. Each record should have a unique identifier or key. If you include the fields that you want from each form, in whatever order you wish, you can set a criteria to select the specific record you wish from the main form (eg. customer name) and a second critera that selects the most recent record from the subform (eg last purchase) by selecting for the largest value in the ID key. This assumes that you are allowing the ID key to be autonumbered sequentially. This should give you the most recent record in the subform that is associated with the specific record in the main form you wish.
ankh_too - how do i select the largest value in the id key? this is where i’m getting hung up. i couldn’t find a function that returns the largest value. i tried the LARGE function, but it wasn’t recognized, and MAX requires specific values.
i tried that, and it did give me the most recent single record of all the subform records combined. what i can’t get is the most recent single record for each main record’s subform. when i create a query based on the subform table, access treats all the records like one big table. there is a field that links the subform records to their corresponding main forms, but each related subform record has the same value in that field.
the suggestions i’m getting all work fine on a single table, but don’t have the right affect on the subform table.
to clarify my question, how can i isolate a main record’s subform data and perform sorting and selecting actions on it?
Hmmm…well, I admit you’ve kind of lost me here, but my best suggestion for an easy answer it to add a field to your table, then to your subform. Have a default vaule placed in that field depending on how the form was accessed. Then you could sort on that value and find the top N record.