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
4  1455 
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.
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.
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.
"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> This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tigerlily |
last post by:
// Program to create new accounts, perform deposits, withdrawals, and bank
//inquiries
#include<iostream>
#include<fstream>
using namespace std;
void menu();
int read_accts(int , double ,...
|
by: bdisney3 |
last post by:
OK..not a total noob, but I really need help with a fairly complex (for me anyway) thing I'm trying to accomplish:
I need to create a "report" based on data in a MS SQL 2000 dataset that can be...
|
by: EManning |
last post by:
Using A2003. I'm receiving this error when returning from a
"DoCmd.OpenReport..." statement. I have a tab control with a subform
on every tab. The user selects an item from a combobox at the top...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
