|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a small business that I run, and I have used Service Time Management
wizard to build my database, with some personal changes due to the personal
preference and requirements.
I am right now stumped at creating a report from a query that sends out
notices, ie 1st, 2nd,... for payments. The problem is that I know that there
is a way to have a "template" report for all the notices, just changing the
data ( customer and which notice) but how??
I have made a union query that pulls the cutomer name and the correct
notice that I specify only thru specific sql statement. But I have to make
a seperate query for each notice for each customer ( sum(customers) x
sum(notice)), makes for alot of queries.
There has to be an easier way, but I can not figure it out. I can write
basic code, and read it very well, so coding is not a problem for me. Is
there a way to make this report possible? I have posted this in other qroups
as well.
|
|
On Fri, 29 Sep 2006 12:00:02 -0700, Joseph
<Joseph[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >I have a small business that I run, and I have used Service Time Management
>wizard to build my database, with some personal changes due to the personal
>preference and requirements.
>I am right now stumped at creating a report from a query that sends out
>notices, ie 1st, 2nd,... for payments. The problem is that I know that there
>is a way to have a "template" report for all the notices, just changing the
>data ( customer and which notice) but how??
>I have made a union query that pulls the cutomer name and the correct
>notice that I specify only thru specific sql statement. But I have to make
>a seperate query for each notice for each customer ( sum(customers) x
>sum(notice)), makes for alot of queries.
>There has to be an easier way, but I can not figure it out. I can write
>basic code, and read it very well, so coding is not a problem for me. Is
>there a way to make this report possible? I have posted this in other qroups
>as well.
I'm puzzled. Why a UNION query? That's normally used to string
together multiple like datasets.
Are you using literal criteria for each query? You certainly do not
need to do so: rather than putting 1, 2 or 3 on the criterion for
which notice you can put [Which notice:] and type 1, 2, or 3 for the
prompt; similarly for the customer.
Bear in mind we do NOT know the structure of your tables, or the
nature of your query. Perhaps you could post the SQL of the query.
John W. Vinson[MVP]
|
|
Each notice letter has different wordage
ie:
1st Notice (table.letter.letterid = 1)
" this is the first notice with intenet to recieve payment for services
rendered.
2nd Notice(table.letter.letterid = 2)
" THis is the second notice.... Futher non-payment...."
3rd Notice(table.letter.letterid = 3)
" This is the third notice.......'
4th Notice(table.letter.letterid = 4)
"Your non-payment has forced us to pursue legal action....."
In order not to have seperate reports for each notice, which will bloat the
database, is there a way to select the customer
info(table.customer.customerid) along with the notice
letter(table.letter.letterid) to a template report?
"John Vinson" wrote:
[Quoted Text] > On Fri, 29 Sep 2006 12:00:02 -0700, Joseph
> <Joseph[ at ]discussions.microsoft.com> wrote:
>
> >I have a small business that I run, and I have used Service Time Management
> >wizard to build my database, with some personal changes due to the personal
> >preference and requirements.
> >I am right now stumped at creating a report from a query that sends out
> >notices, ie 1st, 2nd,... for payments. The problem is that I know that there
> >is a way to have a "template" report for all the notices, just changing the
> >data ( customer and which notice) but how??
> >I have made a union query that pulls the cutomer name and the correct
> >notice that I specify only thru specific sql statement. But I have to make
> >a seperate query for each notice for each customer ( sum(customers) x
> >sum(notice)), makes for alot of queries.
> >There has to be an easier way, but I can not figure it out. I can write
> >basic code, and read it very well, so coding is not a problem for me. Is
> >there a way to make this report possible? I have posted this in other qroups
> >as well.
>
> I'm puzzled. Why a UNION query? That's normally used to string
> together multiple like datasets.
>
> Are you using literal criteria for each query? You certainly do not
> need to do so: rather than putting 1, 2 or 3 on the criterion for
> which notice you can put [Which notice:] and type 1, 2, or 3 for the
> prompt; similarly for the customer.
>
> Bear in mind we do NOT know the structure of your tables, or the
> nature of your query. Perhaps you could post the SQL of the query.
>
>
> John W. Vinson[MVP]
>
|
|
If, in your customer table, you have a "notice number" in which you keep
track of the level of dunning that you need to do, you should be able to
Select the customer information for the main Report, then use the "notice
number" to select the text to be displayed/printed in a Subreport Control.
With careful design, it can be made to look just like an individually
prepared letter.
Off the subject, I'd suggest a much softer tone in the "first notice" even
if it is a "late payment notice"... like "Perhaps your monthly statement was
not delivered, or you overlooked..." -- people really like to be give the
"benefit of the doubt." And I, at least, don't like it to be even implied
that I am deliberately "stiffing" someone. (And, if the "first notice" is
actually the statement, this advice goes double!)
Larry Linson
Microsoft Access MVP
"Joseph" <Joseph[ at ]discussions.microsoft.com> wrote in message
news:348EF8AA-F9AE-4A6C-A269-6C25A166592B[ at ]microsoft.com...
[Quoted Text] > Each notice letter has different wordage
>
> ie:
> 1st Notice (table.letter.letterid = 1)
> " this is the first notice with intenet to recieve payment for services
> rendered.
> 2nd Notice(table.letter.letterid = 2)
> " THis is the second notice.... Futher non-payment...."
> 3rd Notice(table.letter.letterid = 3)
> " This is the third notice.......'
> 4th Notice(table.letter.letterid = 4)
> "Your non-payment has forced us to pursue legal action....."
>
>
> In order not to have seperate reports for each notice, which will bloat
> the
> database, is there a way to select the customer
> info(table.customer.customerid) along with the notice
> letter(table.letter.letterid) to a template report?
>
> "John Vinson" wrote:
>
>> On Fri, 29 Sep 2006 12:00:02 -0700, Joseph
>> <Joseph[ at ]discussions.microsoft.com> wrote:
>>
>> >I have a small business that I run, and I have used Service Time
>> >Management
>> >wizard to build my database, with some personal changes due to the
>> >personal
>> >preference and requirements.
>> >I am right now stumped at creating a report from a query that sends out
>> >notices, ie 1st, 2nd,... for payments. The problem is that I know that
>> >there
>> >is a way to have a "template" report for all the notices, just changing
>> >the
>> >data ( customer and which notice) but how??
>> >I have made a union query that pulls the cutomer name and the correct
>> >notice that I specify only thru specific sql statement. But I have to
>> >make
>> >a seperate query for each notice for each customer ( sum(customers) x
>> >sum(notice)), makes for alot of queries.
>> >There has to be an easier way, but I can not figure it out. I can write
>> >basic code, and read it very well, so coding is not a problem for me. Is
>> >there a way to make this report possible? I have posted this in other
>> >qroups
>> >as well.
>>
>> I'm puzzled. Why a UNION query? That's normally used to string
>> together multiple like datasets.
>>
>> Are you using literal criteria for each query? You certainly do not
>> need to do so: rather than putting 1, 2 or 3 on the criterion for
>> which notice you can put [Which notice:] and type 1, 2, or 3 for the
>> prompt; similarly for the customer.
>>
>> Bear in mind we do NOT know the structure of your tables, or the
>> nature of your query. Perhaps you could post the SQL of the query.
>>
>>
>> John W. Vinson[MVP]
>>
|
|
On Fri, 29 Sep 2006 13:27:02 -0700, Joseph
<Joseph[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >In order not to have seperate reports for each notice, which will bloat the
>database, is there a way to select the customer
>info(table.customer.customerid) along with the notice
>letter(table.letter.letterid) to a template report?
Where are you getting this totally non-standard syntax
table.letter.<field>?
You can certainly build a String value using table field values. For
instance, you could have a calculated field in a Query
MsgText: "this is the " & Choose([NoticeNumber], "first", "second",
"third") & " notice with intent to recieve payment for services
rendered. " & Choose([NoticeNumber], "", "Further nonpayment...",
"Your non-payment has forced us..."
You can include this field as the Control Source of a textbox in your
Report.
I have NO idea how customerid and letterid are related to your
example.
John W. Vinson[MVP]
|
|
In C/C++, to call classes and its members (variables and functions), you use
the '.' :
class table
{
public:
int customerid;
}
void main(void)
{
table customer; //initialize the class table and call it customer
cout<<customer.customerid; //display on the screen customer id
}
"John Vinson" wrote:
[Quoted Text] > On Fri, 29 Sep 2006 13:27:02 -0700, Joseph
> <Joseph[ at ]discussions.microsoft.com> wrote:
>
> >In order not to have seperate reports for each notice, which will bloat the
> >database, is there a way to select the customer
> >info(table.customer.customerid) along with the notice
> >letter(table.letter.letterid) to a template report?
>
> Where are you getting this totally non-standard syntax
> table.letter.<field>?
>
> You can certainly build a String value using table field values. For
> instance, you could have a calculated field in a Query
>
> MsgText: "this is the " & Choose([NoticeNumber], "first", "second",
> "third") & " notice with intent to recieve payment for services
> rendered. " & Choose([NoticeNumber], "", "Further nonpayment...",
> "Your non-payment has forced us..."
>
> You can include this field as the Control Source of a textbox in your
> Report.
>
> I have NO idea how customerid and letterid are related to your
> example.
>
> John W. Vinson[MVP]
>
|
|
Yes I that will work. Why was I making it so hard? Thank you very much. And
thank you for the other suggestion of the first notice. It is much more
tactfull to say that.
Joseph
"Larry Linson" wrote:
[Quoted Text] > If, in your customer table, you have a "notice number" in which you keep
> track of the level of dunning that you need to do, you should be able to
> Select the customer information for the main Report, then use the "notice
> number" to select the text to be displayed/printed in a Subreport Control.
> With careful design, it can be made to look just like an individually
> prepared letter.
>
> Off the subject, I'd suggest a much softer tone in the "first notice" even
> if it is a "late payment notice"... like "Perhaps your monthly statement was
> not delivered, or you overlooked..." -- people really like to be give the
> "benefit of the doubt." And I, at least, don't like it to be even implied
> that I am deliberately "stiffing" someone. (And, if the "first notice" is
> actually the statement, this advice goes double!)
>
> Larry Linson
> Microsoft Access MVP
>
>
> "Joseph" <Joseph[ at ]discussions.microsoft.com> wrote in message
> news:348EF8AA-F9AE-4A6C-A269-6C25A166592B[ at ]microsoft.com...
> > Each notice letter has different wordage
> >
> > ie:
> > 1st Notice (table.letter.letterid = 1)
> > " this is the first notice with intenet to recieve payment for services
> > rendered.
> > 2nd Notice(table.letter.letterid = 2)
> > " THis is the second notice.... Futher non-payment...."
> > 3rd Notice(table.letter.letterid = 3)
> > " This is the third notice.......'
> > 4th Notice(table.letter.letterid = 4)
> > "Your non-payment has forced us to pursue legal action....."
> >
> >
> > In order not to have seperate reports for each notice, which will bloat
> > the
> > database, is there a way to select the customer
> > info(table.customer.customerid) along with the notice
> > letter(table.letter.letterid) to a template report?
> >
> > "John Vinson" wrote:
> >
> >> On Fri, 29 Sep 2006 12:00:02 -0700, Joseph
> >> <Joseph[ at ]discussions.microsoft.com> wrote:
> >>
> >> >I have a small business that I run, and I have used Service Time
> >> >Management
> >> >wizard to build my database, with some personal changes due to the
> >> >personal
> >> >preference and requirements.
> >> >I am right now stumped at creating a report from a query that sends out
> >> >notices, ie 1st, 2nd,... for payments. The problem is that I know that
> >> >there
> >> >is a way to have a "template" report for all the notices, just changing
> >> >the
> >> >data ( customer and which notice) but how??
> >> >I have made a union query that pulls the cutomer name and the correct
> >> >notice that I specify only thru specific sql statement. But I have to
> >> >make
> >> >a seperate query for each notice for each customer ( sum(customers) x
> >> >sum(notice)), makes for alot of queries.
> >> >There has to be an easier way, but I can not figure it out. I can write
> >> >basic code, and read it very well, so coding is not a problem for me. Is
> >> >there a way to make this report possible? I have posted this in other
> >> >qroups
> >> >as well.
> >>
> >> I'm puzzled. Why a UNION query? That's normally used to string
> >> together multiple like datasets.
> >>
> >> Are you using literal criteria for each query? You certainly do not
> >> need to do so: rather than putting 1, 2 or 3 on the criterion for
> >> which notice you can put [Which notice:] and type 1, 2, or 3 for the
> >> prompt; similarly for the customer.
> >>
> >> Bear in mind we do NOT know the structure of your tables, or the
> >> nature of your query. Perhaps you could post the SQL of the query.
> >>
> >>
> >> John W. Vinson[MVP]
> >>
>
>
>
|
|
On Fri, 29 Sep 2006 18:37:01 -0700, Joseph
<Joseph[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >In C/C++, to call classes and its members (variables and functions), you use
>the '.' :
Thanks. I gathered the meaning of what you were posting, but it's not
Access or VBA syntax, I was wondering if you meant it literally or
figuratively.
John W. Vinson[MVP]
|
|
|