Form/Subform Question

[marf]

I have a form with a subform (one-to-many relationship). The many side of the
relationship (subform) has a date associated with each record. When I run the
form I only want to see the entry with the newest date in the subform...

How do I do that??

 

[KARL DEWEY]

Create a totals query with record identifying fields and your date field.
Change the Group By for the date field to Max. Join this query in you
present query that is the source for the subform on those record identifying
fields and date field to the MaxOfDate field.

Query4 --
SELECT [Change Requests].SomeField, Max([Change Requests].[Date open]) AS
[MaxOfDate open]
FROM [Change Requests]
GROUP BY [Change Requests].SomeField;

SELECT [Change Requests].*
FROM [Change Requests] INNER JOIN Query4 ON ([Change Requests].[Date open] =
Query4.[MaxOfDate open]) AND ([Change Requests].SomeField = Query4.SomeField);

 

[marf]

Karl,

I appreciate your answer, however, I am very very new to Access. I created
the form/subform using the wizard. I have a main form called TblProjects and
a subform called TblProjectUpdates. The subform is in a tab control on the
main form.
I am not sure how to do what you suggested...

Are you saying create a query and inbed that as a subform??

Create a totals query with record identifying fields and your date field.
Change the Group By for the date field to Max. Join this query in you
present query that is the source for the subform on those record identifying
fields and date field to the MaxOfDate field.

Query4 --
SELECT [Change Requests].SomeField, Max([Change Requests].[Date open]) AS
[MaxOfDate open]
FROM [Change Requests]
GROUP BY [Change Requests].SomeField;

SELECT [Change Requests].*
FROM [Change Requests] INNER JOIN Query4 ON ([Change Requests].[Date open] =
Query4.[MaxOfDate open]) AND ([Change Requests].SomeField = Query4.SomeField);

--
KARL DEWEY
Build a little - Test a little

I have a form with a subform (one-to-many relationship). The many side of the
relationship (subform) has a date associated with each record. When I run the
form I only want to see the entry with the newest date in the subform...

How do I do that??

 

[KARL DEWEY]

Step 1 make a copy of the form TblProjectUpdates.
Open the form in design view, click on menu VIEW - Properties. Click in the
square in the extreme upper left corner of the subform and scroll down the
properties to see the Link Child Fields and Link Master Fields.
Create a query with the field listed in the Link Child Fields and the the
date field. In design view of the query click on the icon that is the Greek
letter Epsilon (funny E). The design view grid then adds a new row titled
Total:.
Change the Group By under the date field to Max. Save the query.
Create another query with all the fields. Open in design view. Click on
the Show Tables icon that has a yellow plus sign and one page. Select the
Queries tab. Select the totals query you created. Click field listed in the
Link Child Fields in the table and drag to the same field in the totals
query. Do the same with the table date field and drag to the query
MaxOfDateField. Save the query.
Open the orignal subform and click on menu VIEW - Properties. In the Record
Source property click the down arrow and select the query you just saved.
Save.
Your subform now will display only the latest records.
--
KARL DEWEY
Build a little - Test a little

Karl,

I appreciate your answer, however, I am very very new to Access. I created
the form/subform using the wizard. I have a main form called TblProjects and
a subform called TblProjectUpdates. The subform is in a tab control on the
main form.
I am not sure how to do what you suggested...

Are you saying create a query and inbed that as a subform??

Create a totals query with record identifying fields and your date field.
Change the Group By for the date field to Max. Join this query in you
present query that is the source for the subform on those record identifying
fields and date field to the MaxOfDate field.

Query4 --
SELECT [Change Requests].SomeField, Max([Change Requests].[Date open]) AS
[MaxOfDate open]
FROM [Change Requests]
GROUP BY [Change Requests].SomeField;

SELECT [Change Requests].*
FROM [Change Requests] INNER JOIN Query4 ON ([Change Requests].[Date open] =
Query4.[MaxOfDate open]) AND ([Change Requests].SomeField = Query4.SomeField);

--
KARL DEWEY
Build a little - Test a little

I have a form with a subform (one-to-many relationship). The many side of the
relationship (subform) has a date associated with each record. When I run the
form I only want to see the entry with the newest date in the subform...

How do I do that??