I have 2 tables set up in a relationship matching the customer number:
2) Coface Download: Related by customer numberdaily download containing insurance coverage amounts, credit rating, etc.
3) Invoices: All invoices by invoice #, customer #, date, etc.
I am trying to create a report that shows the total invoices for 2009 (for example), sub-summarized by quarter, customer country, and payment method (e.g., Cash, L/C, etc.) Then, I need to break this data down to show what is actually covered by our trade insurance provider. For instance, customer A had $1 million in sales, of which $750K was covered. Id like this report to have columns showing $1M in sales, $750K covered, $250K not covered. Then I need totals for these 3 categories for all customers by year, by quarter, by country, and by payment method.
Ive had a couple failed attempts&whats the best way to set this up? If I start with the invoice table and link to the Coface table via customer number, I get the correct total for each customer, but then the total covered and total not covered per year/quarter is wrong. Now I am sitting here way over-thinking the whole relationship and I am stumped. I guess its not pulling the totals because it is linked by the customer number, but do I need to create a separate relationship for each year and quarter or what? Help!
