What I want to do: create a Pivot in Excel where I select all trips from e.g. location A to location B.

I ran into a problem, where I have 2 tables (in a database in Excel Power Pivot). The first table (Trips) has a FROM and a TO address. These addresses must come from a known list. That is the second table: Address.

When I tried to connect to both TO and FROM to the Address-table, like this, it set one of the links to inactive.

When I try to do the same in MS Access, I cannot do it in the relationship manager, since it creates an invalid structure (no double links allowed), but in the design query it is possible. There is creates a 'virtual' copy of some sorts, like this.

How do I create the same behaviour as in MS Access? I hope the question is clear, if not please explain where I should elaborate. Hopefully someone can help me out, really stuck here!

  • I've already encountered this kind of hard copy of the data inside the .xlsx files. It seems that MS office doesn't follow it's own specification regarding pivot tables (I don't have any source to back my claim, just my own experience working on a project 2 years ago). You may try to inspect the xml structure of Access and Excel files and see if it can help. (MS office files are zip files containing xml data files) – Menkid Feb 11 at 9:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.