Your Own Data – Pacific Aviation Database

Where is it?

Great care was taken by Barry Stuart when he programmed the database to ensure that your own personal logging data was kept separate from the database files. You will find your data in the directory that you created for your backup data. In your directory, look for a filename which looks like xx_PRIVATELOGS.MDB where xx is the highest number of the files of this form, corresponding to the latest backup data. To see your data, you need to double-click this file to open it in Access. You will see that your data is split between six tables. These are linked as necessary via the ‘ID’ value (of an individual aircraft). These contain:
Flog details of your Flight Log – the aircraft that you have flown in
logLLp the aircraft that you have logged, with date and location
logLLs the aircraft’s operating unit and codes
MyProfile profiles that you have created
PerNote your personal notes against your logged aircraft
tblPhoto your photo logYour data is therefore kept securely outside of the database software. Should you ever choose to use another database, or to manipulate your loggings outside the database, your data can be readily retrieved.

How can I retrieve it?

In order to retrieve your personal loggings data, carry out the relevant procedure given below.

This places all your data into a single Access datasheet and includes the option of exporting it into an Excel spreadsheet where you can manipulate it as you want.

Access 2003 and Access 2010 have slightly different steps.

Access 2003

Find your loggings
Go to the folder where you have saved your backups
Copy and paste your latest backup file: it’s called “xx_PRIVATELOGS.MDB” where xx is the highest number of the files of this form, corresponding to the latest backup data
IMPORTANT: Work from the copy to keep your original safe
Create and run a new Query
Open (double-click) “Copy of xx_PRIVATELOGS.MDB”
Opens with a table showing ‘Outstanding Loggings’
‘Close Form’
select ‘Create’ tab
select ‘Query Design’
in ‘Show Table’ dialogue box, ‘Tables’ tab
select ‘logLLp’
‘Add’
similarly, select ‘logLLs’
‘Add’
‘Close’
in the ‘logLLp’ box, double-click ‘*’
in the ‘logLLs’ box, double-click ‘*’
From the toolbar, click on the ruler/pencil/set square icon on the left (it’s under the File menu on mine but is a moveable toolbarso could be anywhere!) and
select ‘DataSheet View’ All your records will now be shown.

Four columns – ‘flag’, ‘LOCKK’, ‘logLLs.ID’, ‘logLLs.when’ – may not be of much use, but keep them. They can be hidden or deleted later.

You can now export your data to several different programs e.g. Excel, PDF

Access 2010

The keystrokes for Access 2007 are identical.

There are 2 methods given of retrieving all of your data:

The first extracts all your data by inputting SQL code into an Access query and running it. PREFERRED WAY– Our thanks to Al Henderson for providing the SQL code to enable this to be done
The second extracts your logging data but not your personal notes fields data by specifying an Access query and running it.
STEP 1 – Find your loggings
- Go to the folder where you have saved your backups
- Copy and paste your latest backup file: it’s called “xx_PRIVATELOGS.MDB” where xx is the highest number of the files of this form, corresponding to the latest backup data
- IMPORTANT: Work from the copy to keep your original safe
  STEP 2 – Create and run a new Query
  
  METHOD 1
- Open (double-click) “Copy of xx_PRIVATELOGS.MDB”
- Opens with a table showing ‘Outstanding Loggings’
- ‘Close Form’
- select ‘Create’ tab
- select ‘Query Design’
- close ‘Show Table’ dialogue box
- in ‘Design’ tab, select ‘Data definition’
- in the blank pane typeSELECT logLLp.when, logLLp.Where, logLLp.Registration, logLLp.Aircraft, logLLp.Operator, logLLs.logunit, logLLs.[loga/c code], logLLs.notes, pernote.mynoteFROM (logLLp LEFT JOIN logLLs ON (logLLp.when = logLLs.when) AND (logLLp.ID = logLLs.ID)) left join pernote on logllp.id =pernote.id
  WHERE (((logLLp.when) Is Not Null))
  
  ORDER BY logLLp.when, logLLp.Where, logLLp.Registration;
- ‘Run’
- All your records and the personal notes field data will now be shown Optional
- If you want a different order of variables and records, either
  - Rearrange the SELECT variables to give the order of the columns that you want & Rearrange the ORDER BY variables to give the order in which records are sorted, and run it, or
  - Manipulate the resulting datasheet using the Access facilities, or
  - Export to Excel and manipulate the data there (see STEP 3)
- If you want to save the new Query (to rerun, copy the coding into a later backup [as opposed to copying it from here] ) ;
  - ‘File’
  - ‘Save’
  - enter a name for the Query in the ‘Save As’ box
  - ‘OK’
    METHOD 2
- Open (double-click) “Copy of xx_PRIVATELOGS.MDB”
- Opens with a table showing ‘Outstanding Loggings’
- ‘Close Form’
- select ‘Create’ tab
- select ‘Query Design’
- in ‘Show Table’ dialogue box, ‘Tables’ tab
- select ‘logLLp’
- ‘Add’
- similarly, select ‘logLLs’
- ‘Add’
- ‘Close’
- in the ‘logLLp’ box, double-click ‘*’
- in the ‘logLLs’ box, double-click ‘*’
- ‘Run’
- All your records will now be shownFour columns – ‘flag’, ‘LOCKK’, ‘logLLs.ID’, ‘logLLs.when’ – may not be of much use, but keep them. They can be hidden or deleted later.You can now export your data to several different programs e.g. Excel, PDF
  STEP 3 – Export to Excel – the most flexible program option
- select the ‘External Data’ tab
- in the ‘Export’ part of the ribbon (menu bar), click ‘Excel’
- specify the destination filename and format
  - File name
  - File format
- specify export options – there are 3 options (for now, just do the first two options)
  1. check/tick the box: Export data with formatting and layout
  2. check/tick the box: Open the destination file after the export operation is complete or – if you have selected certain records before the export
  3. check/tick: Export only the selected records
- ‘OK’Your loggings now open in Excel for you to manipulate as you wish.

Where is it?

How can I retrieve it?