Thanks all.
Eljo, I'm really sorry but I don't understand what you mean by that. I'm familiar with joining tables in SQL statements as I used that in MS Access.
by what I gave two different strategies please be specific.
If I understand rightly, you mean leave the DBLookupComboBox 'as is' and then the TBGrid underneath it uses a SQL string joining the route table and fares table below it?
No, the opposite. Joining the route table with dDBLookupComboBox above to create a single dataset with all the keys you need for the fares datasets.
That would work if there were only one or two routes, but if there were several (let's just say there are multiple routes all running into a City, I'd have to duplicate the fares for each and every route between two points).
I have no idea what you are objecting about, to me it looks like that longer routes should be more expensive than shorter routes on the same finish then again I haven't touched that subject at all.
I can perform a search based on "select any record where column X equals the contents of editbox1" (written in English!) and I dare say I could apply two criteria by using two editboxes... yet I can't use something similar to query based on the visible string in a lookup combo box- or at least I haven't found it yet.
em what that has to do with anything? are you telling me that there is a link between the route name and some other field in the fares table?
Can anyone recommend any good books on the subject? Something as simple as this should be easier to implement and I cannot keep bothering you good people.
Which subject? relational databases, sql language, data access library of fpc (SQLDB) something I missed? maybe it would be easier to create a small demo application with a demo database to show how your form works at the moment and a small text demonstrating what you need to remove or add, in which control of your form. I guess solving the specific case will be more beneficial to you at the moment instead of the generic guides to general directions of development.
In any case here a generic skeleton of what you strugling with.
Think of a database application as a 3 layered cake. First layer the database where everything collects. Second layer the data in your application's memory. the datasets. Third and last layer the components on your forms, which is the current way of interacting with the data nothing more.
What I'm talking in both my cases in my previous post is to change the second layer, the data in the datasets.
The datasets have two modes of operation dynamic and static schema (in the database world schema refers to the shape your database has, its tables, fields, types, sizes, stored procedures, triggers etc).
If you are using a dynamic schema you are free to change the fields that are returned by changing the select sql.
In static schema dataset, it has a list of field references defined at design time to which it gives you access to, those fields must always exist in your select sql that you are using.
if you choose to use a static schema then you can add fields that are calculated at runtime, by code you attach to oncalculate event (if I remember correctly) that can return any value you desire based on any number of parameters or rules, including the currently selected keyvalue of your dblookupCombobox.
By adding that key value in the in memory dataset of locations (and not changing the underlying table) you pretend that the master key on your master dataset is a composite key(which means a key that is based on more than one fields) and as such you are short circuit your design to accomplish your goal with out huge changes in the underline database or second layer of your application.
Good luck for a speedy resolution I will not have access to the forums for the next day or two.