Link two tables: a master table and its detail
Orders and order lines, patients and their visits, contracts and payments: the data sits in two files, but you need to read it together. In Excel that means a VLOOKUP or a second window you keep scrolling to the right row. Here the tables are linked on a field: the master is on top, the detail below, and as you move through the rows above, the table below keeps only the records that belong to the current one — the same idea as SET RELATION in dBase and FoxPro.
Free for personal use. Menu “Data” → “Link to a detail table…”. Files are not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.
How to link them
- Open both tables, each in its own tab. The formats may differ: DBF with CSV, Excel with a SQLite database.
- On the master table’s tab choose “Data” → “Link to a detail table…” and pick the detail table.
- Choose the field pairs: a field of the master equals a field of the detail, for example ID = ORDER_ID. If the key is composite, add more pairs — all of them must match.
- “Link”: a second panel appears under the master. Move through the rows above with the mouse or the arrow keys — the lower panel shows only the records with the same key, and its header shows the key value and the number of records.
Drag the border between the tables to change the height of the lower panel. The lower table can be sorted by its column headers, and the button in its header opens the detail table on its own tab, in full. The cross removes the link.
What counts as a match
- Values are compared as text. “001” and “1” are different keys: codes with leading zeros are not turned into numbers the way Excel does it.
- Letter case and spaces at the ends can be ignored. This is on by default: in DBF, text fields are padded with spaces on the right, and without it “Smith” and “Smith ” would not match.
- An empty key links nothing. If the current master row has no key, the lower table is empty rather than listing every record without a key.
- The detail’s own filters work together with the link. Filter it by year on its tab and the panel keeps only that year’s records for that key.
Why this beats VLOOKUP
- VLOOKUP returns one value from the first row it finds. Here you see every related record, however many there are.
- No formulas and no helper columns: the link changes neither table.
- A row with no match is obvious: the lower table is empty. There is no “#N/A” to hunt for.
- The files are not uploaded to a server and not merged into one: each table stays itself.
What next
- Need to find differences between two exports rather than browse them — compare two CSV files.
- Find repeated keys in one table before linking — find duplicates in CSV.
- Check the keys themselves for mistakes — validate IBANs, emails and phones.
FAQ
Can I edit the detail table in the lower panel?
No, the panel is view-only. Edit the table on its own tab, where it is shown in full: the button in the panel header opens it.
How many tables can be linked?
One link: a master and a detail. A chain of three tables is not possible yet — link them one pair at a time, or combine two of them into one.
What happens if I close one of the tables?
The link is removed and the other table stays open and shows all of its rows again.
Is the link kept between sessions?
No. After a restart you set the link up again — it is one dialog with a choice of fields.
Does it work on large files?
Each move to another row selects the detail records in one pass over the detail table. On small and medium tables you will not notice it; on very large ones there is a short pause after each move.
Free for personal use. Your file is not uploaded to a server. Windows version — 3.5 MB, no installation: details. Organizations: license.