By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,975 Members | 1,236 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,975 IT Pros & Developers. It's quick & easy.

Please help with a transaction/comment report?

P: n/a
Ron
Hi All

I've got 2 files, transactions and comments, linked by a clientID as a FK in
the clients file and as a FK in the comments file (PK in the 'client' file
of course). The comments don't necessarily have to have anything to do
with the transactions, but usually they do (explanations about when payment
will be sent, who in the office has talked to the client about the bill and
what was said, etc). The comments, rather than being a memo field, are 250
char text fields (I've read here that memo fields can create problems and so
avoided that type of field--still, it works well).

I've got 2 reports, one that lists transactions in date order, then type
(product charge, payment, write off, etc). It looks good (even if I do say
so myself...), complete with subtotals for dates, then grand totals at the
end. I've got another report that lists the comments in date order.

What I'd rather have is one report with both, all in date order--so that the
comments would appear underneath the last transaction for the specific date
(or, if no transaction has that date, the comment would appear after the
transaction with an earlier date and/or before a transaction with a later
date. So, in date order, regardless of file. I've tried to do this with
another query (both reports work off queries with filters for clientID) but
I'm having no luck. I get all the transactions printing twice if there are
2 comments, 3 times if there are 3 comments, etc.

I know there's probably a simple solution to this, but I'm unable to come up
with one so far.

Any help would be greatly appreciated.
ron
Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ron wrote:
Hi All

I've got 2 files, transactions and comments, linked by a clientID as a FK in
the clients file and as a FK in the comments file (PK in the 'client' file
of course). The comments don't necessarily have to have anything to do
with the transactions, but usually they do (explanations about when payment
will be sent, who in the office has talked to the client about the bill and
what was said, etc). The comments, rather than being a memo field, are 250
char text fields (I've read here that memo fields can create problems and so
avoided that type of field--still, it works well).

I've got 2 reports, one that lists transactions in date order, then type
(product charge, payment, write off, etc). It looks good (even if I do say
so myself...), complete with subtotals for dates, then grand totals at the
end. I've got another report that lists the comments in date order.

What I'd rather have is one report with both, all in date order--so that the
comments would appear underneath the last transaction for the specific date
(or, if no transaction has that date, the comment would appear after the
transaction with an earlier date and/or before a transaction with a later
date. So, in date order, regardless of file. I've tried to do this with
another query (both reports work off queries with filters for clientID) but
I'm having no luck. I get all the transactions printing twice if there are
2 comments, 3 times if there are 3 comments, etc.

I know there's probably a simple solution to this, but I'm unable to come up
with one so far.

Any help would be greatly appreciated.
ron


Consider a sub report to display the comments.
Nov 13 '05 #2

P: n/a
Ron
Thanks. I had already tried that. I have a group break on the
transactiondate and that's where I put it. However, each time I run the
report, I get ALL the comments underneath each break rather than just the
comment(s) that would be equal to the last transaction date or before the
next transaction date.

"Salad" <oi*@vinegar.com> wrote in message
news:Mh*****************@newsread3.news.pas.earthl ink.net...
Ron wrote:
Hi All

I've got 2 files, transactions and comments, linked by a clientID as a FK in the clients file and as a FK in the comments file (PK in the 'client' file of course). The comments don't necessarily have to have anything to do
with the transactions, but usually they do (explanations about when payment will be sent, who in the office has talked to the client about the bill and what was said, etc). The comments, rather than being a memo field, are 250 char text fields (I've read here that memo fields can create problems and so avoided that type of field--still, it works well).

I've got 2 reports, one that lists transactions in date order, then type
(product charge, payment, write off, etc). It looks good (even if I do say so myself...), complete with subtotals for dates, then grand totals at the end. I've got another report that lists the comments in date order.

What I'd rather have is one report with both, all in date order--so that the comments would appear underneath the last transaction for the specific date (or, if no transaction has that date, the comment would appear after the
transaction with an earlier date and/or before a transaction with a later date. So, in date order, regardless of file. I've tried to do this with another query (both reports work off queries with filters for clientID) but I'm having no luck. I get all the transactions printing twice if there are 2 comments, 3 times if there are 3 comments, etc.

I know there's probably a simple solution to this, but I'm unable to come up with one so far.

Any help would be greatly appreciated.
ron


Consider a sub report to display the comments.

Nov 13 '05 #3

P: n/a
Ron,
Did you set the master/child records property? Also, as a first try I'd try
a group break but it sounds like that didn't get you what you wanted.
You might look at fiddling with the SQL for the report so the join between
transactions and comments isn't a straightforward equality but another
boolean operator like greater than or between. Joins can be done on
calculated values as well and there are coordinated subqueries (a topic I
slept through in school when it came up).

"Ron" <ro*******************@earthlink.com> wrote in message
news:hL*****************@newsread1.news.pas.earthl ink.net...
Thanks. I had already tried that. I have a group break on the
transactiondate and that's where I put it. However, each time I run the
report, I get ALL the comments underneath each break rather than just the
comment(s) that would be equal to the last transaction date or before the
next transaction date.

Nov 13 '05 #4

P: n/a
"Alan Webb" <kn*****@hotmail.com> wrote in message news:<p_********************@comcast.com>...
Ron,
Did you set the master/child records property? Also, as a first try I'd try
a group break but it sounds like that didn't get you what you wanted.
You might look at fiddling with the SQL for the report so the join between
transactions and comments isn't a straightforward equality but another
boolean operator like greater than or between. Joins can be done on
calculated values as well and there are coordinated subqueries (a topic I
slept through in school when it came up).

"Ron" <ro*******************@earthlink.com> wrote in message
news:hL*****************@newsread1.news.pas.earthl ink.net...
Thanks. I had already tried that. I have a group break on the
transactiondate and that's where I put it. However, each time I run the
report, I get ALL the comments underneath each break rather than just the
comment(s) that would be equal to the last transaction date or before the
next transaction date.


You mean correlated subqueries, Alan? Yeah, you _must_ have been sleeping. <g>
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.