I'm loading several tables of sales data, and using Lars Christensen's excellent link table script to generate a link table. But, if I create a table box containing fields from the link table and a field from a fact table, the data does not appear nicely in one row. The link table data is in one row, with the fact table field being blank. And the fact table fields are in their own rows, with the link table fields being blank. I will give a simple example.
Before the link table is created:
Customer table: Customer Code, Customer Name
Invoice table: Invoice Number Customer Code
In a normal scenario, without link tables, a table box containing Invoice Number, Customer Code and Customer Name would show these three fields in one row per invoice, i.e.
123456 1001 ABC Ltd
However when the link table is created, Customer Code is removed from both the Customer and Invoice tables and inserted into the link table. The table box above now shows me the following:
123456 1001 - - 1001 ABC Ltd
I also get some customer codes with blank invoice number and blank customer name, I'm not sure why these appear.
To solve this problem I tried join loading the customers table into the invoices table. A left join partly solves the problem as it puts the customer name into the same row as the invoice number and customer code, however there are still lots of blank fields and I'm not convinced that the join has all the data, nor am I convinced it is good design. Does anyone have any tips on how to solve this problem? Many thanks.
