On 4 Nov, 14:21, "Klatuu" wrote:
"What would be wrong with that?"
Almost everything.
The common design of a header and transactions in separate but related
tables is always the correct way to accomplish this.

If you had one table to hold it all, how many individual transactions could
such a table hold? *Well, with a limit of 256 fields in a table, you will
run out very quickly. *And if you are saying to repeat the header info for
each transaction, then you are breaking the redundant data rule.

And in the report, where will the header info come from?

Or maybe I am missing the point and this post is a wrote in message

...



I am designing a database that includes the simple (!) *functionality
of *producing invoices (not an accounting package). *I understand the
convention is to have a table for invoice headers and one for invoice
transactions. *My question is what are the advantages of this approach
rather than just having one table for invoice transactions. *The
printed invoice (an Access report) can have the invoice headings
(date, invoice no customer address etc) in the report header and the
invoice transactions in the report detail. *What would be wrong with
that?

Gordon- Hide quoted text -

- Show quoted text -

Wow, I feel as though I have been flamed. This is no joke. It was
only a question on which I wanted advice. I said the invoicing
function was simple, not the database itself.

Fields in the one table invoicing transactions I envisage would be:

invoice date
invoice no
customerID
transaction date
transaction ref
productID
item description
Quantity
Unit price

As I see it, only the first 3 would break the redundant data rule.
The data for the invoice report (including the header) would be built
on the fly (using VBA) at time of invoice creation, drawing data not
just from the invoice transaction table but also the related tables on
customers/products etc.

But thanks for responding - at least you confirmed the only objection
to a one table design for invoice transactions is the data redundancy
rule which in this particular case is not a great overhead if it saves
having to maintain and update 2 tables.