Union Query Report Code????

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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 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.
Click to expand...

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 said:
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.
Click to expand...

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]
Click to expand...
 
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 said:
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 said:
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.
Click to expand...

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]
Click to expand...
Click to expand...
 
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?
Click to expand...

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 said:
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?
Click to expand...

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]
Click to expand...
 
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 said:
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 said:
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 said:
On Fri, 29 Sep 2006 12:00:02 -0700, Joseph

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]
Click to expand...
Click to expand...
Click to expand...
 
In C/C++, to call classes and its members (variables and functions), you use
the '.' :
Click to expand...

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]
 
Back
Top