master/child form

[jamies]

Hi All,
I have 2 tables, master & child. with a one to many relationship.
On one of my forms I want to display some of the fields from the master table and only the last entry from my child table.
How would i accomplish this?

Many thanks
Jamie

 

[rzw0wr]

Put the fields you want in a query.
Use the query to make the form.

Dale

 

[jamies]

Thanks for the reply,
However, the form will still show me all the records associated, i am only interested in the last child record of the master. Each master may have 100 child records but i only want the last child for each master.


Thanks
Jamie

 

[missinglinq]

You'd have to have a Field, in your Child Table, to show the order that the Records were entered in. Most developers would use a Date Created Field. Then your Query has to only show the Record whose date is the maximum date. The SQL Statement for the Query would be something like

SELECT DISTINCT UniqueField, DMax("[DateCreated]", "ActualTableName","[UniqueField]='" & [UniqueField] & "'") AS LastRecord FROM ActualTableName;

Replacing UniqueField with the actual name of any Field that is unique to a given Record, DateCreated with the actual name of your date field, and ActualTableName with the actual name of the underlying Table.

Linq ;0)>

 

[jamies]

hi missinglinq,
That what i thought i would have to do, was just hoping there was some other way.

Jamie

 

[jamies]

okay, I am trying out the DMAX Function in a query but it returns an #error in the field and returns all values

SELECT tbl_Master.MasterID, tbl_Master.PName, tbl_Master.Ver, tbl_Master.Date_Entered, tbl_Master.Loc, tbl_Child.ChildID,
DMax("[DateTimeStamp]","tbl_Child","[tbl_Child.MasterID]='"& [tbl_Master.MasterID] & "'") AS LastRecord,
tbl_Child.CreatedBy, tbl_Child.otherLoc FROM tbl_Master INNER JOIN tbl_Child ON tbl_Master.MasterID = tbl_Child.MasterID;

What am i missing?

Thanks
Jamie