Union Query Report Code????

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joseph - 29 Sep 2006 20:00 GMT

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.

Reply to this Message

John Vinson - 29 Sep 2006 20:37 GMT

>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
[quoted text clipped - 11 lines]
>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]

Reply to this Message

Joseph - 29 Sep 2006 21:27 GMT

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?

> >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
[quoted text clipped - 24 lines]
>
> John W. Vinson[MVP]

Reply to this Message

Larry Linson - 30 Sep 2006 00:14 GMT

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

> Each notice letter has different wordage
>
[quoted text clipped - 49 lines]
>>
>> John W. Vinson[MVP]

Reply to this Message

Joseph - 30 Sep 2006 02:40 GMT

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

> 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
[quoted text clipped - 66 lines]
> >>
> >> John W. Vinson[MVP]

Reply to this Message

John Vinson - 30 Sep 2006 00:29 GMT

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

Reply to this Message

Joseph - 30 Sep 2006 02:37 GMT

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
}

> >In order not to have seperate reports for each notice, which will bloat the
> >database, is there a way to select the customer
[quoted text clipped - 19 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Vinson - 30 Sep 2006 06:45 GMT

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

Reply to this Message