Link to home
Start Free TrialLog in
Avatar of ScuzzyJo
ScuzzyJoFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.

Thanks
Sarah


Data-I-Receive.xls
Mapping.xls
Required-Output.xls
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

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.
Avatar of ScuzzyJo

ASKER

Hiya

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.

Thanks
Sarah
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?
Hiya

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.

Thanks
Sarah
ASKER CERTIFIED SOLUTION
Avatar of Runrigger
Runrigger
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.