Gossamer Forum
Home : Products : DBMan : Discussions :

Relational - two databases - linked

Quote Reply
Relational - two databases - linked
Hi

Sorry I have tried to work this out over the past few days and made numerous posts asking about specific sections of this task, but I am getting nowhere - well just more confused, I'll try and explain what my goal is.

I am setting up a virtual airline for pilots using MS flight sim.

I have set up a database with the usual pilot records

name
Pilot ID (a id number specific to that pilot eg AJ001)
email address
and things like this

in this database I also have categorised aircraft into five categories

Cat A - propeller aircraft

Cat E - Concorde

each category (These categories are simply based on aircraft type and appear as fields in the db) has two fields one for hours flown and a second for a rank based on the number of hours flown.

example

0-20 pilot
21-40 1st officer
41-60 Snr first officer

and so on,

I then have a field for total hours which is the total of these categories, I have this working.


This database is only viewable, and only the webmaster will have access to add modify delete etc.

I have a second database I call flight history, I want the pilots to be able to add to this database, only on their record using their id number

they will enter the following

date
departure time
arrival time
total flying time
fuel used
aircraft used
cat of aircraft a...e

and a few other details

This is my problem I need the data taken from their entry and added to the relevant field in the main database, so if they report:-

That they flew a flight for 2 hours in a category b aircraft, then 2 hours is added to the cat b hours field in the main database, which in turn is totaled into the total hours.

When these 2 hours are added to cat b, I also need to run something to ascertain if this has moved them up a rank by now having say 21 hours so they are now a 1st officer.

The database is viewed on the site as follows

Enter main database

view short records

name / pilot id / total hours

link to long record showing all details of every category, then a link to flight history (second database listing all flights that the pilot has made)

I know this is long winded, but I have the two seperate databases working, and need to discover the best way of linking them so that they add the hours and calculate the rank. I have looked at several different ways including trying to write my own script that takes the data daily and requires a manual build every 24 hours or so similar to the build function in links.

I was wondering if anybody had any views on how this might be acheived.

Hope that you can understand this rough copies of both databases are at

http://www.amijet.com/...lots&uid=default


http://www.amijet.com/...tory&uid=default


many thanks if you have even read this far, and thanks to all those who have already helped me in this project, I'm sure I'll be bald before its over, either by age or by pulling it out

thanks again

DavyC
http://www.amijet.com
Life's more fun in a virtual world