help!!!! - rutime formatting of reports

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

I have a table with a list of clubs and their members and
the times that they have attended, e.g.
membername
clubname
date
timearr
timedep

given a couple of records eg.
dan,cluba,7/8/03,09:00,12:00
dan,cluba,8/8/03,14:00,17:00


I want to produce a report as follows:

"member name" "club name" "total hours"
dan cluba 6

Where "total hours" is the aggregation of times spent at
the club by the member.

I have a query for the report that returns the above
records. I then trap the Details Format event.

I can easily stop "dan" appearing in the report twice, by
detecting duplicate rows and adding the code:

Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False

However, I want to dynamically set the "total hours" field.
I can fairly easily store the running total in a local
variable but it's the actual setting of the correct field
that's the problem.

In the event procedure I have access to the current
record's controls (text boxes for each field), however
it's not the current record's controls that I need, it's
the previously written record's. I've tried storing the
previously written record's text box in a private
TextObject type vartiable but when I try to write to its
Text property, I get a message saying that I can't write
to it because it hasn't got focus!! I could try giving it
focus, and I will after I've sent this, but that seems
like a hack (and it may not work anyway!!).

Can you help??? Basically I need to know how to write to
the previously written (not read) record's fields.

Cheers,

Dan.
 
I would do all the calculation in the query for the report.

SELECT MemberName, ClubName,
SUM(DateDiff("n",TimeArr-TimeDep))/60
FROM TheTable
GROUP BY MemberName, ClubName
ORDER BY MemberName, ClubName
 
That certainly would work a treat if that was the whole
story...

In actuality, the report needs to be more like this:

"member name" "club name" "month1 hours" "month2
hours" "month3 hours" "total hours"

as an example, the values may be:

Dan cluba 3 4 1 8
George clubb 2 2 2 6

It's to produce a quarterly report, so "month1..3" may be
Jan, Feb, Mar or Apr, May, Jun etc.

So I need to aggregate the totals for each month as well
as create a total. I "think" I really need to do what I
was trying to do but without success - i.e, write to the
previously written records field. I have thought of a
nasty way of doing it, but I don't think I'll try just
yet, in the hope that you or someone else can tell me how
to do what I was trying.

This nasty idea involves not writing a record until the
detail format event has fired for the next member. So,
for each member I detect in the event I could store all
its fields in temporary variables until I get a new
member. When I get a new member, I store the new members
fields in temporary variables and then overwrite the
current records fields with those previously stored in the
temporary variables. Dirty, dirty, dirty. Oh, and I
haven't thought about what to do with the final record yet
because that would need to be written, possibly in the
report footer format event. Yuk.

I'm sure I'm really close to sorting this out, I just need
to write to the previously written record's fields (if
that really is possible, like I'm naively thinking it
is!). I suppose one way around this is to create a
separate VBA function that does all of these calculations
and writes to a temporary table, which would then feed the
report very simply.
 
Then it sounds as if you need to use a crosstab query as the source of your
report.

Perhaps we can get Duane Hookom in on this discussion as he has some good
techniques for handling this. OR you can try looking it up in Google groups.

A sample UNTESTED query might look something like:

TRANSFORM SUM(DateDiff("n",TimeArr-TimeDep))/60 as HoursSpent
SELECT MemberName, ClubName, Format([Date],"YYYYMM") as YrMonth
SUM(DateDiff("n",TimeArr-TimeDep))/60
FROM TheTable
WHERE <ENTER YOUR CRITERIA HERE>
GROUP BY MemberName, ClubName
PIVOT Format([Date],"YYYYMM") as YrMonth

This can still cause you problems unless you use relative months.
 
hmmm, access 97 doesn't appear to have a datediff
function... any alternatives???
-----Original Message-----
Then it sounds as if you need to use a crosstab query as the source of your
report.

Perhaps we can get Duane Hookom in on this discussion as he has some good
techniques for handling this. OR you can try looking it up in Google groups.

A sample UNTESTED query might look something like:

TRANSFORM SUM(DateDiff("n",TimeArr-TimeDep))/60 as HoursSpent
SELECT MemberName, ClubName, Format([Date],"YYYYMM") as YrMonth
SUM(DateDiff("n",TimeArr-TimeDep))/60
FROM TheTable
WHERE <ENTER YOUR CRITERIA HERE>
GROUP BY MemberName, ClubName
PIVOT Format([Date],"YYYYMM") as YrMonth

This can still cause you problems unless you use relative months.
That certainly would work a treat if that was the whole
story...

In actuality, the report needs to be more like this:

"member name" "club name" "month1 hours" "month2
hours" "month3 hours" "total hours"

as an example, the values may be:

Dan cluba 3 4 1 8
George clubb 2 2 2 6

It's to produce a quarterly report, so "month1..3" may be
Jan, Feb, Mar or Apr, May, Jun etc.

So I need to aggregate the totals for each month as well
as create a total. I "think" I really need to do what I
was trying to do but without success - i.e, write to the
previously written records field. I have thought of a
nasty way of doing it, but I don't think I'll try just
yet, in the hope that you or someone else can tell me how
to do what I was trying.

This nasty idea involves not writing a record until the
detail format event has fired for the next member. So,
for each member I detect in the event I could store all
its fields in temporary variables until I get a new
member. When I get a new member, I store the new members
fields in temporary variables and then overwrite the
current records fields with those previously stored in the
temporary variables. Dirty, dirty, dirty. Oh, and I
haven't thought about what to do with the final record yet
because that would need to be written, possibly in the
report footer format event. Yuk.

I'm sure I'm really close to sorting this out, I just need
to write to the previously written record's fields (if
that really is possible, like I'm naively thinking it
is!). I suppose one way around this is to create a
separate VBA function that does all of these calculations
and writes to a temporary table, which would then feed the
report very simply.
twice,
by giving
it write
to
Click to expand...
.
Click to expand...
 
Access 97 does have a DateDiff function. Why do you say it doesn't appear to
have one? Are you getting an error? If so, what is the error?
hmmm, access 97 doesn't appear to have a datediff
function... any alternatives???
-----Original Message-----
Then it sounds as if you need to use a crosstab query as the source of your
report.

Perhaps we can get Duane Hookom in on this discussion as he has some good
techniques for handling this. OR you can try looking it up in Google groups.

A sample UNTESTED query might look something like:

TRANSFORM SUM(DateDiff("n",TimeArr-TimeDep))/60 as HoursSpent
SELECT MemberName, ClubName, Format([Date],"YYYYMM") as YrMonth
SUM(DateDiff("n",TimeArr-TimeDep))/60
FROM TheTable
WHERE <ENTER YOUR CRITERIA HERE>
GROUP BY MemberName, ClubName
PIVOT Format([Date],"YYYYMM") as YrMonth

This can still cause you problems unless you use relative months.
That certainly would work a treat if that was the whole
story...

In actuality, the report needs to be more like this:

"member name" "club name" "month1 hours" "month2
hours" "month3 hours" "total hours"

as an example, the values may be:

Dan cluba 3 4 1 8
George clubb 2 2 2 6

It's to produce a quarterly report, so "month1..3" may be
Jan, Feb, Mar or Apr, May, Jun etc.

So I need to aggregate the totals for each month as well
as create a total. I "think" I really need to do what I
was trying to do but without success - i.e, write to the
previously written records field. I have thought of a
nasty way of doing it, but I don't think I'll try just
yet, in the hope that you or someone else can tell me how
to do what I was trying.

This nasty idea involves not writing a record until the
detail format event has fired for the next member. So,
for each member I detect in the event I could store all
its fields in temporary variables until I get a new
member. When I get a new member, I store the new members
fields in temporary variables and then overwrite the
current records fields with those previously stored in the
temporary variables. Dirty, dirty, dirty. Oh, and I
haven't thought about what to do with the final record yet
because that would need to be written, possibly in the
report footer format event. Yuk.

I'm sure I'm really close to sorting this out, I just need
to write to the previously written record's fields (if
that really is possible, like I'm naively thinking it
is!). I suppose one way around this is to create a
separate VBA function that does all of these calculations
and writes to a temporary table, which would then feed the
report very simply.

-----Original Message-----
I would do all the calculation in the query for the
report.

SELECT MemberName, ClubName,
SUM(DateDiff("n",TimeArr-TimeDep))/60
FROM TheTable
GROUP BY MemberName, ClubName
ORDER BY MemberName, ClubName

dan wrote:

I have a table with a list of clubs and their members
and
the times that they have attended, e.g.
membername
clubname
date
timearr
timedep

given a couple of records eg.
dan,cluba,7/8/03,09:00,12:00
dan,cluba,8/8/03,14:00,17:00

I want to produce a report as follows:

"member name" "club name" "total hours"
dan cluba 6

Where "total hours" is the aggregation of times spent at
the club by the member.

I have a query for the report that returns the above
records. I then trap the Details Format event.

I can easily stop "dan" appearing in the report twice,
by
detecting duplicate rows and adding the code:

Me.NextRecord = True
Me.PrintSection = False
Me.MoveLayout = False

However, I want to dynamically set the "total hours"
field.
I can fairly easily store the running total in a local
variable but it's the actual setting of the correct
field
that's the problem.

In the event procedure I have access to the current
record's controls (text boxes for each field), however
it's not the current record's controls that I need, it's
the previously written record's. I've tried storing the
previously written record's text box in a private
TextObject type vartiable but when I try to write to its
Text property, I get a message saying that I can't write
to it because it hasn't got focus!! I could try giving
it
focus, and I will after I've sent this, but that seems
like a hack (and it may not work anyway!!).

Can you help??? Basically I need to know how to write
to
the previously written (not read) record's fields.

Cheers,

Dan.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top