Storing Periodic One-Time Report Invoice Data

[Wayne]

I have a billing database that also creates invoice reports. 95% of
the time it runs smoothly. I enter data into the subform and an
invoice is created from the combination of the Main and Subform data
filling about one page. About 1 or 2 times a year I have to create a
one-off invoice for multiple clients. This one-off invoice requires
much more data than my Main/subform can provide so I end up creating
the 100+ row invoice in Excel and exporting it to my Billing db. I
then have a special program that reads in the one-off custom data and
creates the custom report invoice. All the custom invoices have the
same basic fields – Description and Amount fields. This works fine
but I would like to store this custom invoice data and link it to the
correct client. I would have to greatly expand my current subform to
accommodate these one-ff invoices which makes no sense. I thought
about creating a custom button on the subform that links to the custom
table but after awhile I may have many custom tables. Is there a
better way to design my Main/Subform tables and forms to accommodate
these one-off invoices? Any help would be appreciated.

 

[Larry Daugherty]

I have the distinct impression that those "Invoice Reports" you are
talking about are really printed Forms. That would present the kinds
of problems you posted.

Forms are for the entry and editing of data, not for printing.
Reports are for presentation and printing of data.

In fact, just about any output you intend to print as part of any
production process should be presented on Reports. There are some
extra wizards and controls available in the Report Designer to tailor
the behavior of Reports. See the "Sorting and Grouping" wizard it is
really helpful.

With very little effort, Reports can be designed that intelligently
span several pages and that also intelligently provide Title page
formatting, continuing page headers and footers as you desire and page
totals and grand totals as you desire. Also, you can design your
reports such that all of the "one-off" invoice Reports can be done
with a single command. Each Report will be just the size it needs to
be in order to display it's data. I'm not aware of any arbitrary
limit on either individual Report size or the size of the aggregated
Reports. Watch the paper and ink supplies.

No Excel is required for any of this.

Post back as you have questions.

HTH
--
-Larry-
--

I have a billing database that also creates invoice reports. 95% of
the time it runs smoothly. I enter data into the subform and an
invoice is created from the combination of the Main and Subform data
filling about one page. About 1 or 2 times a year I have to create a
one-off invoice for multiple clients. This one-off invoice requires
much more data than my Main/subform can provide so I end up creating
the 100+ row invoice in Excel and exporting it to my Billing db. I
then have a special program that reads in the one-off custom data and
creates the custom report invoice. All the custom invoices have the
same basic fields – Description and Amount fields. This works fine
but I would like to store this custom invoice data and link it to the
correct client. I would have to greatly expand my current subform to
accommodate these one-ff invoices which makes no sense. I thought
about creating a custom button on the subform that links to the custom
table but after awhile I may have many custom tables. Is there a
better way to design my Main/Subform tables and forms to accommodate
these one-off invoices? Any help would be appreciated.

 

[RedRider]

I have the distinct impression that those "Invoice Reports" you are
talking about are really printed Forms.  That would present the kinds
of problems you posted.

Forms are for the entry and editing of data, not for printing.
Reports are for presentation and printing of data.

In fact, just about any output you intend to print as part of any
production process should be presented on Reports.  There are some
extra wizards and controls available in the Report Designer to tailor
the behavior of Reports.  See the "Sorting and Grouping" wizard it is
really helpful.

With very little effort, Reports can be designed that intelligently
span several pages and that also intelligently provide Title page
formatting, continuing page headers and footers as you desire and page
totals and grand totals as you desire.  Also, you can design your
reports such that all of the "one-off" invoice Reports can be done
with a single command.  Each Report will be just the size it needs to
be in order to display it's data.  I'm not aware of any arbitrary
limit on either individual Report size or the size of the aggregated
Reports.  Watch the paper and ink supplies.

No Excel is required for any of this.

Post back as you have questions.

HTH
--
-Larry-
--


I have a billing database that also creates invoice reports.  95% of
the time it runs smoothly.   I enter data into the subform and an
invoice is created from the combination of the Main and Subform data
filling about one page.  About 1 or 2 times a year I have to create a
one-off invoice for multiple clients.  This one-off invoice requires
much more data than my Main/subform can provide so I end up creating
the 100+ row invoice in Excel and exporting it to my Billing db.  I
then have a special program that reads in the one-off custom data and
creates the custom report invoice.  All the custom invoices have the
same basic fields – Description and Amount fields.  This works fine
but I would like to store this custom invoice data and link it to the
correct client.  I would have to greatly expand my current subform to
accommodate these one-ff invoices which makes no sense.  I thought
about creating a custom button on the subform that links to the custom
table but after awhile I may have many custom tables.  Is there a
better way to design my Main/Subform tables and forms to accommodate
these one-off invoices?  Any help would be appreciated.

Thanks for the reply Larry. First off I am creating reports, not
printing forms for these invoices. There are about 10 bound textboxes
in the Main form and 6 bound textboxes in the Subform (1 to many). I
can create 95% of my reports (invoices) from these 16 fields but a few
times a year, every year, I really need 100 bound textboxes to create
these one-off reports.

The 6 Subform fields would look like this:

Billing Date
Date of Service
Type of Service
Total Service Hours
Service Rate
Total Cost of Service

Usually Type of Service, Total Service Hours, Service Rate are
concatenated and put on one line of the report. So 95% of the time I
use one record in the Subform to create my report.

The 2 or 3 times a year I have to do special reports. These are
usually adjustments to previous reports (invoices)

Original Date of Service (different for every month)
Type of Service
Total Service Hours (hours vary per monthly bill)
Original Service Rate
Adjusted Service Rate
Original Total Cost of Service
Adjusted Total Cost of Service
Credit Due

I have to repeat the above for maybe 10 months, which means I would
need about 80 textbox fields to do this one special invoice report.
Sometimes I have to go back years to Adjust incorrect invoices.

My question is how do I design the Subform so these special one-off
invoice reports can be stored and made displayable at a later date?
Do I expand my Subform table by 200 fields just to accommodate these
odd invoice reports?

 

[Wayne]

Thanks for the reply Larry.  First off I am creating reports, not
printing forms for these invoices.  There are about 10 bound textboxes
in the Main form and 6 bound textboxes in the Subform (1 to many).  I
can create 95% of my reports (invoices) from these 16 fields but a few
times a year, every year, I really need 100 bound textboxes to create
these one-off reports.

The 6 Subform fields would look like this:

Billing Date
Date of Service
Type of Service
Total Service Hours
Service Rate
Total Cost of Service

Usually Type of Service, Total Service Hours, Service Rate are
concatenated and put on one line of the report.  So 95% of the time I
use one record in the Subform to create my report.

The 2 or 3 times a year I have to do special reports. These are
usually adjustments to previous reports (invoices)

Original Date of Service (different for every month)
Type of Service
Total Service Hours (hours vary per monthly bill)
Original Service Rate
Adjusted Service Rate
Original Total Cost of Service
Adjusted Total Cost of Service
Credit Due

I have to repeat the above for maybe 10 months, which means I would
need about 80 textbox fields to do this one special invoice report.
Sometimes I have to go back years to Adjust incorrect invoices.

My question is how do I design the Subform so these special one-off
invoice reports can be stored and made displayable at a later date?
Do I expand my Subform table by 200 fields just to accommodate these
odd invoice reports?

RedRider and Wayne are the same person.

 

[Fred]

Dear RedRider/Wayne,

I noticed that the thread faded out. Please excuse my directness which is
an attempt to be helpful.

For a question like this the foundation for you explaining the situation and
for any solution is going to be in the structure of your real world data and
the structure of your tables. There was little or nothing on this in your
posts and so there is not enough there to even understand what is going on
much less how to fix it.

As a side note, if I had to take a guess from your choice of what you did
and didn't discuss in your posts, it would be that you would benefit from a
lot more focus and attention on thre structure of your data and tables. If
you imagine your application as a 3 story building, these items are the
foundation and the first floor. Getting those right makes everything else
easier, and getting those wrong or not-so-good will make everything else
difficult and sometimes impossible.

This is probably not what you were expecting, but hope that that helps a
little!

Sincerely,

Fred