Only print last detail line for primary key, suppress all others

[lorirobn]

Hi,

I have a report that has a query as its record source.
The query picks up records from a table that meet certain criterion.
The report then counts how many records there are for each key (room
number) - but does some calculating by date to see whether to increment
the count.

Then the report lists the Room Number, and the Count.
The way it is now, if the count for a room number is 4, it will
display:
Room Count
-------- --------
200 1
200 2
200 3
200 4

What I'd like to do is ONLY print the last line.

I don't think I can do this via my query, because Count is not on my
table and is calculated in my report's VBA. The logic I use is: if
one record and the next have an ActivityDate within 11 days of each
other, the count is 1. If it is over 11 days, the count is 2. And so
on with the next record, etc. In my VBA, I check record-by-record. I
don't think this logic can be done in a query, can it?

Is there a way to do this, either by my report (and only print the last
detail line for a room number), or by query?

Thanks so much for any help!
Lori

 

[Al Campagna]

lorirobn,
Several ways to do it. Via the query, or on the report. On the report...
Assuming that each RoomNo record counts as 1... (you imply that with your exampe data)
Remove your fields from the detail section, create a RoomNo Group Footer, and place
RoomNo in that Footer
Then , add an unbound calculated field, =Count(RoomNo)
RoomNo =Count(RoomNo)
200 4
201 11
202 7 etc...

 

[lorirobn]

Hi Al,

Thanks for the response.
Each RoomNo record may or may not count as 1, however. I calculate it
via logic in my VBA code. It depends on ActivityDate on the record, as
compared to the date on the prior record. If a record's ActivityDate
is > 9 days after the ActivityDate on the prior record, it counts as 1.
Otherwise, it does not count.

Since I already have that logic set up, and I already have a RoomNo
Group Footer, can I place this calculated count on the footer as well?
I do that, but it is not working. It always shows up as '1'.

I know I am doing something wrong in my VBA. When I look at the value
of ActivityDate, it is always the latest one. For example, if a room
has 2 records:
Room 200 ActivityDate 10/15/06
Room 200 ActivityDate 10/19/06,
when I examine the contents of ActivityDate during my Detail_Format
event, I read:
200 10/19/06
200 10/19/06

Would it help if I posted my code? I will do so. I have never used
the Footer section before, so I may be doing something wrong. I also
have a detail section, with only ActivityDate and a txtCount. If I did
not have ActivityDate here, how would I check the value and add to my
counter accordingly?

Here is my code, along with some comments:

Option Compare Database
Dim gpriordate As Date
Dim gPrevRoom As String
Dim gPosCount As Integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If gpriordate = 0 Then 'this is for the first time....
gpriordate = ActivityDate
gPosCount = 1 'reset the count for each room
If IsNull(gPrevRoom) Or gPrevRoom = "" Or gPrevRoom = " " Then
gPrevRoom = RoomID
End If
ElseIf ActivityDate > gpriordate + 9 Then 'This is the condition that
adds to the count....
gPosCount = gPosCount + 1
End If

If ActivityDate <> gpriordate Then 'I compare to the prior date, but
this does not work...
'ActivityDate is
always the latest date...
gpriordate = ActivityDate
End If

txtCount = gPosCount 'this is the count on the detail section of the
report
End Sub

Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As
Integer)
gpriordate = 0
txtRoomTotalCount = gPosCount 'this is the count to display on the
report
End Sub

 

[lorirobn]

Oops, now that I've posted all that, I just got it to work. My 'order
by' wasn't correct.
Thanks for the help!