Access: Field Names and Records

Hi All

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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Your problem here in this example is that you effectively need to tabulate 3 sets of data values in to a table with a single set of values which can then be exported to Excel........

This is not going to be straightforward.

How do your remote locations produce the data? That might be an easier starting point.
ScuzzyJoAuthor Commented:

Believe it or not, they use Access to query an SQL database!  Unfortunately, I can't get to it as I'm on a different domain.  I can't ask them to send me a new "report" either as I need to get this done quickly and I would have to put a systems request in, which would take too long.

Having said that, one of the sources is Excel based, so I wouldn't be able to get round the whole problem even if I could for this one.

Hi Sarah, is the structure of your spreadsheets exactly the same for columns A to G, i.e. are the columns fixed and do they have the same column header names?

Does the data always start at column H and end at column "n" i.e. unknown?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ScuzzyJoAuthor Commented:

Yes, the structure of the spreadsheets will be complete fixed in terms of columns.  I do know where the data will end, if that helps, just lobbed a load off to make the sheet simpler.  If they change it, I will have to shoot them!

The rows will change if we add/remove service centres.

Try this, it tabulates your data, fairly straightforward stuff, so should be easy for you to manipulate the code further as required.

Then simply copy the data in to a database and us a mapping table for the field "Business Map"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ScuzzyJoAuthor Commented:
Excellent!  Thank you.  I had resorted to doing the work in Excel in the meantime and had written a macro to do the same as yours, but yours is much better than mine!!!  It's a lot shorter too.  I understand your macro, but you've made much better use of ranges than I had.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.