>Thanks Ken and Anton. I appreciate your response and it makes sense to me. I just want to confirm a couple of things. First of all, let me explain this to make sure I understand what you guys are saying:
>
>I have a parent table. It holds the information about the transaction (ie date, total amount, bank, etc.). Within this table, I also have a transaction ID and a logical field to say whether or not it is split by client.
>
You dont really need a field indicating that it's split by client.
>I should then create a separate child table that has a field for transaction id along with the amount and client id.
>
>when I want to go back later to see how much we charged per client, i would add up all invoices in the parent table that are not split and group them by client, then if a transaction is split, add the amount from the child table that has that client id.
>
Invoices that belong to only one client will have only one child record
>If this understanding is correct, I have one further question. There are a couple of other ways that a transaction can be split. It could be split also by year and quarter. For example, part of an invoice can be from 2005 and part from 2006, but it is on one invoice and we would expect 1 payment for it. For tax purposes, i would need to separate it by year. Would you have another child table, in addition to the split-by-client child table, that has the split by year? If I kept it all in 1 child table (ie splits by client and splits by year), then it would be possible to double count if 1 invoice was split both ways.
>
Just use the same logic, if you have an invoice for one client split by period, add 2 child records with the same client id, just have a field with the date that applies to each item
Lets say you have a $200.00 invoice that is split into 2 clients 50/50 and the part of the second client has to be split 20/80 between 2005/2006:
Parent table:
transid dateinv total
9999 99/99/9999 200.00
Child table:
transid item client total dateperiod
9999 001 AAAA 100.00 99/99/2006
9999 002 BBBB 20.00 99/99/2005
9999 003 BBBB 80.00 99/99/2006
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