I'm reposting my question and deleting my old one as nobody replied. I've cut the data in the attachments down. I hope you can help as, if I can do this, it's going to save incredible amounts of time!
To give you the background, so that you understand what I'm trying to do, I have 3 different "databases" (actually Excel files) which have different field names, etc. I'm trying to use Access to effectively merge them so that I can spit out reports via simple queries. There is a LOT of data!
I'm only working on one of these at the moment, but am keeping the others in mind in terms of how I structure things. I've attached a file called Data I Receive, which shows how the data will come to me.
I've also attached a file called Mapping to show how I've assigned possible mapping types to each of the "fields" in Data I Receive. The idea behind all this is that it will eliminate duplication across the 3 and will make things a lot quicker. My aim is to do all the work in Access and effectively spit out a report that looks something like Output Required, which I've also attached. The basis is that, with that data, I can create a pivot table which people can manipulate easily themselves whereas, at the moment, we're sending about a million reports out to make sure we cover everything they need. They could, e.g. only show the Midlands region or only show Cost and not Volume.
My problem is that if you look at the structure in the first two files, the field name in Data I Receive(e.g. PUD Salary) is actually part of a record in Mapping (under Business Map).
How can I create a relationship between these so that I can run queries to effectively spit out what's in Data Export? I really don't want to manipulate the data in Excel as there are 55 service centres and 108 "fields" in just this one (though the other two are a little smaller) and this is a daily report.
All the data values have been randomised, so there's nothing commercially cofidential in them.
Thanks for your help. I'm awarding 500 points.