LastOf/FirstOf

[Guest]

I'm attempting to create a report that is pulling data from forms and
subforms. The problem is that the subforms sometimes have more than one
record. So when I run the report, the main record shows up more than once,
because the data in the subforms is different. Example. The Main form shows
the Received date, but the subform will show the expected operational date.
If that date changes, a new record in the subform is created with the updated
date. So when a report is ran, it looks like this..

Desc Received Date Operational Date
TEST 1/1/05 2/1/05
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 3/15/05
TEST II 3/1/05 4/1/05

I would like to pull only the last record in the sub form, so that the
report only shows:


Desc Received Date Operational Date
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 4/1/05

I've attempted to put "LastOf" before the Data name in the Control Source,
i.e. LastOfopdate, which does make it so that the record is only listed once,
but then the date is blank in the Operational Date field. How can I fix that?

 

[Duane Hookom]

From the example you provided, you could base your report on a totals query
like:
SELECT [Desc], Max([Received Date]) as RcvdDate, Max([Operational Date]) as
OperDate
FROM [....tables... queries...]
GROUP BY [Desc];

 

[Guest]

I don't understand where I'm supposed to do that. I'm using Access 97 if that
helps?

From the example you provided, you could base your report on a totals query
like:
SELECT [Desc], Max([Received Date]) as RcvdDate, Max([Operational Date]) as
OperDate
FROM [....tables... queries...]
GROUP BY [Desc];

--
Duane Hookom
MS Access MVP
--

I'm attempting to create a report that is pulling data from forms and
subforms. The problem is that the subforms sometimes have more than one
record. So when I run the report, the main record shows up more than once,
because the data in the subforms is different. Example. The Main form
shows
the Received date, but the subform will show the expected operational
date.
If that date changes, a new record in the subform is created with the
updated
date. So when a report is ran, it looks like this..

Desc Received Date Operational Date
TEST 1/1/05 2/1/05
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 3/15/05
TEST II 3/1/05 4/1/05

I would like to pull only the last record in the sub form, so that the
report only shows:


Desc Received Date Operational Date
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 4/1/05

I've attempted to put "LastOf" before the Data name in the Control Source,
i.e. LastOfopdate, which does make it so that the record is only listed
once,
but then the date is blank in the Operational Date field. How can I fix
that?

 

[Duane Hookom]

Forgive me. I was showing the SQL view of a totals query.

Assuming you have a query started with the required fields:
1) Select View->Totals
2) add your appropriate fields to the grid
3) select Max under both [Received Date] and [Operational Date]
4) view the datasheet view to confirm the results

--
Duane Hookom
MS Access MVP
--

I don't understand where I'm supposed to do that. I'm using Access 97 if
that
helps?

From the example you provided, you could base your report on a totals
query
like:
SELECT [Desc], Max([Received Date]) as RcvdDate, Max([Operational Date])
as
OperDate
FROM [....tables... queries...]
GROUP BY [Desc];

--
Duane Hookom
MS Access MVP
--

I'm attempting to create a report that is pulling data from forms and
subforms. The problem is that the subforms sometimes have more than one
record. So when I run the report, the main record shows up more than
once,
because the data in the subforms is different. Example. The Main form
shows
the Received date, but the subform will show the expected operational
date.
If that date changes, a new record in the subform is created with the
updated
date. So when a report is ran, it looks like this..

Desc Received Date Operational Date
TEST 1/1/05 2/1/05
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 3/15/05
TEST II 3/1/05 4/1/05

I would like to pull only the last record in the sub form, so that the
report only shows:


Desc Received Date Operational Date
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 4/1/05

I've attempted to put "LastOf" before the Data name in the Control
Source,
i.e. LastOfopdate, which does make it so that the record is only listed
once,
but then the date is blank in the Operational Date field. How can I fix
that?

 

[Guest]

Thank you.. That worked!!

From the example you provided, you could base your report on a totals query
like:
SELECT [Desc], Max([Received Date]) as RcvdDate, Max([Operational Date]) as
OperDate
FROM [....tables... queries...]
GROUP BY [Desc];

--
Duane Hookom
MS Access MVP
--

I'm attempting to create a report that is pulling data from forms and
subforms. The problem is that the subforms sometimes have more than one
record. So when I run the report, the main record shows up more than once,
because the data in the subforms is different. Example. The Main form
shows
the Received date, but the subform will show the expected operational
date.
If that date changes, a new record in the subform is created with the
updated
date. So when a report is ran, it looks like this..

Desc Received Date Operational Date
TEST 1/1/05 2/1/05
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 3/15/05
TEST II 3/1/05 4/1/05

I would like to pull only the last record in the sub form, so that the
report only shows:


Desc Received Date Operational Date
TEST 1/1/05 2/15/05
TEST I 2/1/05 3/1/05
TEST II 3/1/05 4/1/05

I've attempted to put "LastOf" before the Data name in the Control Source,
i.e. LastOfopdate, which does make it so that the record is only listed
once,
but then the date is blank in the Operational Date field. How can I fix
that?