| Author |
 Topic  |
|
|
oldgreygary
United Kingdom
40 Posts |
 Posted - 10/05/2011 : 10:41:21
|
Hello,
I am trying to understand the relationship between Mapmaker and Excel.
The basic issue is around the question of flexibility. Some scenarios I can think of are: If the Excel file got updated/recreated say, weekly how would that be refelected in MM? If the Excel contains all the data how can I control only display some of that data.
I am presuming that if the Excel file changes I would have to re-create the .dra file? I also presume that I have to do 'snapshots' of the data to display only part of it?
Can anyone contribute to my questions?
Thanks
Gary |
|
|
Hugh Macnair
United Kingdom
20 Posts |
Posted - 10/05/2011 : 16:39:02
|
Much depends on just what you are wanting to display, and how your excel table is organised. When you link a dra to xls file, and you have designated your link column, you are invited to select which column you want to display, whether that be as a table, chart, label or fill. As long as the new information is shown in the same column, the update should appear automatically in the MM display.
It might be worh reading chapters 4 and 5 of the manual carefully and experimenting with them.
I hope this helps |
 |
|
|
oldgreygary
United Kingdom
40 Posts |
Posted - 16/05/2011 : 12:26:24
|
Following up on this post. I understand that the selection manager allows you to take 'snapshots' of data and then you can save them as another layer i.e. a .dra file. However, can you dynamically update the .dra with selections? For instance the data might be broken down into species and within species it could be broken down into record types. So, if I created an extract which had all the data and then created a .dra file which was associated with that data i.e. an excel spreadsheet.
Can i then do selective queries that dynamically update the .dra or as above do I have to create another .dra with this selective data and add to project?
Cheers for now
Gary Pocklington |
|
|
|
johnnew
United Kingdom
75 Posts |
Posted - 16/05/2011 : 15:13:22
|
quote:
The basic issue is around the question of flexibility. Some scenarios I can think of are: If the Excel file got updated/recreated say, weekly how would that be reflected in MM? If the Excel contains all the data how can I control only display some of that data.
Hi Garry
This isn't a direct answer and sorry it is a lot of words but the logic process described may help. I had a similar problem before I retired of getting snapshot data out of a constantly varying live database and producing quarterly returns that required complex calculations. It reads as if you have a similar issue here.
Thinking backwards you want to display data in MM, but that may vary, and you don't want to be constantly recreating the links. So I am guessing step 1 is to get MM reading/writing the data to/from the maximum number of fields that you are ever likely to need in a worksheet of an Excel spreadsheet. That Excel worksheet never changes but you link it to the data that does vary by using Excel's linking features into a worksheet which has copying links to every field in the changing one.
In Excel the cheat is to do the data manipulation, filtering etc., in linked sheets. You can update and save archive version files as many times as you like provided the live one's keep the same file and field names.
My process was -
(1) standard 3 monthly data extract of the same data fields from the non-Excel database into a substitute_your_name.xls file overwriting the last version. Then archive a copy of that for the future with the period name so the data extract was saved.
(2) Open the Excel spreadsheet within which was the calculations worksheet getting Excel to update it with a mirror copy in worksheet 1 of all that quarter's varied data from substitute_your_name.xls.
(3) In the 2nd worksheet do any selection calculations etc., with Excel's filters or formulae. In my real case most of it was standard separations into individual columns selected by case officer name code (which could just as easily be plant or animal species codes), application type and some other performance figures also created with standard formulae based on time taken from receipt of application to decision date.
(4) Open the 3rd worksheet, again all fully linked within Excel, which added up the columns from sheet 2 and had the totals and text laid out in a format suitable for printing off the two A4 summary analysis sheets for the Unit manager and for submitting to the regulator.
I'm guessing that what is needed for the links you want to create to /from MM is the equivalent based on your data of this end result worksheet i.e., a worksheet where the fields etc will always be the same but the data within it, whether text or numbers, is what varies.
Once set up my quarterly returns were all automated taking about five to ten minutes to run the process each quarter, and replacing what had previously been two or three days of manual calculations.
John New - Transport historian/researcher and Stephenson Locomotive Society Publicity Officer. |
Edited by - johnnew on 16/05/2011 15:27:34 |
|
|
| |
Topic |
|
|
|
Using Excel as a database
Forum Locked
