Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
How to do master-detail table analysis (Read 3658 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
How to do master-detail table analysis
11.11.06 at 23:18:50
How to do master-detail table analysis
Frequently I need to do data analysis. I start with some master table. Then I search for detail data and detail data for that, etc.
How to do this effectively with SQL*XL?
The nice thing about SQL*XL is that you can dump the data into a spreadsheet. It is easy to look back without having to requery.
My starting point usually is to retrieve some data to start with. I recall having to do analysis on gas meters for an electricity company. I retrieved some 100 gas meters into sheet 1. The gas meters have an ID field and I defined a name for the cell containing the meter ID I wanted to use.  
When the name doesn't exist yet you can type it into Excel where it displays the address of the cell you have selected. Just type meter_id in the field and it will attach that name to the selected cell. To redefine it you must use Insert, Name, Define.
Next I wanted to select all meter readings for that meter in sheet 2. I used the SQL:
select * from meter_readings where meter_id = :meter;
This dumped all the meter readings for that meter into sheet2. I realised I had taken a test meter. I needed to use another meter instead.  This was quickly done by naming another cell in Sheet1 to meter. In sheet2 I just needed to refresh the query.
Looking at the meter readings in sheet2 I needed to find out who made a particular meter reading. The customer themselves or a manual reading by a professional. I had to query the meter_reading_details table for a particular reading at a certain date.
I named the cell containing the meter_reading_id I wanted to investigate: reading_id. I then queried: select * from meter_reading_details where reading_id = :reading_id;
I dumped this information in sheet3.
Having completed the analysis I usually save the spreadsheet for future use. I can refresh the meters query in sheet1, just to update it. Pick another meter and requery the other querries without modifying them. It quickly shows the details I want without any further hassle.  
If quite often occurs I need to look again at the analysis I have made.  Sometimes it helps to dump detail information below a previously retrieved set of detail information. This way historical outcomes can be documented and archived.
This provides a very easy and quick way to say: last month the situation was like this and the current situation is like that, pointing at the two sections of dumped query results.
Excel is great at holding information. SQL*XL adds really easy database access to it. Combined it provides a great way to store (historical) (master-detail) data for databases. Quite essential for analysts and programmers.  
I can't work without it.
Back to top
« Last Edit: 11.11.06 at 23:33:27 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1