i need some (more) help with msaccess.
the facts:
there is a form and the form contains a subform.
some main form records contain multiple subform records.
i would like a report or query that displays records from the main form (easy enough) and the last record in each main record’s subform (i am stumped).
i checked access help and could not find a way to do this.
any help would be rewarded with glory and gratitude.
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.
well i did find a way to extract the last record entered on the subform for the entire database, including all records from the main form. that only returns one record, though.
i have not found a way to separate the sub-records per main form record, and then return the last record from each.
yikes. is this making sense?
Not quite, but I usually don’t let that stop me.
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.
I guess that’s probably not any clearer, is it?
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.
Can you do a query (sorted descending) and pick the first record? That would give you the ‘last’ record for that item (assuming the numbering scheme is incremental instead of some sort of encryption)
Just as Opengrave said. Set the query to a descending sort on the field and change the ‘Top Values’ box in the menu bar from “All” to ‘1’.
That should give you only the most recent single record.
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.
>> MS ACCESS ANOMOLY
HOLLY MOLLY!!