Setting up a Multi-Table Query

[tjcusick]

I have 3 database. First Reftbl.dbf (is a reference table), 2nd Ind.dbf (is an individual person table), and 3rd Attr.dbf (is the link between the two). Reftbl.dbf contains a ref_id and ref_name.
Ind.dbf contains ind_id, ind_name and ind_phone.
Attr.dbf contains ind_id and ref_id.
I am trying to link all three into one database.

This is the first time i'm doing this and I'm looking over some code from another person. What he did was just link two tables using the addnewchild...

[b]if[/b] TmpGroupType = [teal]'I'[/teal] [b]then[/b] [b]begin[/b]
        CBDataBase := CBDBUser;
        FileExt := [teal]'UDB'[/teal];
        FileAlias := [teal]'CSFAMILY'[/teal];
        FileNameOverride := CBDBUser.DataPath+[teal]'CSFAMILY.UDB'[/teal];
        NewChild := Children.Add;
        AddNewChild(eaSkip, [teal]'Ind->'[/teal], TokenStr(MytSend.Table_Name,[teal]'.'[/teal], [purple]1[/purple]),
                    [teal]'UDB'[/teal], GetAppDir+MytSend.Table_Name, [teal]'FAMILY_ID'[/teal],
                    [teal]'FAMILY_ID'[/teal], rtExact);
       [b]end[/b]; [navy][i]// if TmpGroupType = 'I'
[/i][/navy]

But what im wondering is, is this the best way to link three tables together. I basically need them linked because we want to do custom reporting on the Reference Table with Individual details.

The end resulting database i would like to see would look something like...
reftbl_name, ind_name, ind_phone
Administration, George Went, 555-9595
Assistant, John Smith, 555-1010
Assistant, Tom Jones, 555-0003
Hospitality, Tom Jones, 555-0003
Maintenance, John Smith, 555-1010

I would appreciate any and all help. Thank you.

Tom C

 

[DelphiAaron]

you should be able to setup a master - detail relasionship or perform an sql statement to get the data from the three.

what database are you using?

Aaron

 

[towerbase]

You seem to be confusing database and table.

The example code you show seems to be accessing data stored in Microsoft Works. I suggest you ignore this.

Your requirement refers to reftabl_name. What is this? Did you mean ref_name?

Your .DBF file extension implies that you are using dBASE tables. I suggest you consider using the TQuery component and try a SELECT statement along the lines of:

SELECT ref_name, ind_name, ind_phone
FROM Reftbl AS r, Ind AS i, Attr AS a
WHERE a.ind_id=i.ind_id AND r.ref_id=a.ref_id

Andrew
Hampshire, UK

 

[tjcusick]

I guess I wasn't very clear on this. Anyhow, i figured it out and ended up using a CBMemset to create my combined databases. Yes they are databases that I am pulling the info from. But like i said I figured it out now so thanks anyway.

Tom C