>"You dont even really need a total in the parent table. When you want to report on this data, you should use sql select to group as you please using calculated fields, but thats for another thread."
>
>Carlos, thanks for your reply. What I was wondering, until you added the quote above, is why am I duplicating entries in the parent table and child table, ie if there is no split in the transaction then exactly the same information is being put in both tables. It appears, based on what you indicate above, that the parent table is not necessary. As long as the entries in the "child" table have the same transaction id per entry, then the parent table is not necessary.

You do it that way so you have a "system" that covers all the alternatives, 1 to n items in an invoice, and when you do it that way, you dont even stop to think if an invoice has 1 or 1000 items, you treat all cases in the same way.

Also you do it that way so that data that belongs to the parent table (the invoice as a whole), like the invoice date, is not repeated in each of the child table (invoice items) records.

>Just comfirming that this is what you're saying. Thanks!
>

Paul:

OK, maybe a little background will be useful here, recommended reading, just to start:

http://en.wikipedia.org/wiki/Database_normalization
http://support.microsoft.com/default.aspx?scid=kb;en-us;100139

You would need the parent table to store data that is unique to each invoice, for example the invoice date. You need the child table to store unique data about each item that belongs to that invoice. You need the invoice number in each child record so you know to wich invoice each item belongs to. You need the parent table to store the information that a invoice has been paid (maybe).

Think of when you go to the supermarket, at the check out, they do the same steps no matter how many items you are buying, 1 or 20, they dont say, oh! one item, i have to do things this way, 5 items, i have to do things this other way.

All this is just a basic very simplified explanation regarding data normalization, you should also see what works best for you, take normalization as a guideline, not as carved in stone rules (my view)

Carlos