Help please with multi-table query

Help please with multi-table query

Post by Julian Mille » Mon, 15 Mar 1999 04:00:00



Hello,

I am having trouble designing a query linking four tables:

The master table contains personal details of each student (name, address
etc) with a key field  "trainee ref"

There are three separate tables for three separate subject courses, each of
which contain details of which course units have been completed,

trainee ref,    unitA, Unit B, etc....

Not all students do all three subjects so only a subset of the trainee refs
appear in each of the subject tables.

I want to produce a query which gives details of all units completed by each
student in all subjects. I have tried to do this by linking the master table
to the subject tables (via the trainee ref field) but can only link one
table at a time, not all three. I have also tried using the "!" operator on
the trainee ref field but Paradox will only allow use of this operator in
one table not in all three.

I am sure that there is a simple solution to this in a one-step query. ( I
have also tried using a sequence of queries attached to a pushButton method
but this does not work either (Paradox locks the first answer table so that
it cant be used in the next query step)

Any ides gratefully received

Thanks

 
 
 

Help please with multi-table query

Post by Michael Juul Hanse » Mon, 15 Mar 1999 04:00:00


"have also tried using a sequence of queries attached to a pushButton method
but this does not work either (Paradox locks the first answer table so that
it cant be used in the next query step)"

You can change the name of the answer-table, e.g. executeQBE(":priv:__temp.db")
;// The two underscores preceeding the table name makes Paradox delete the table
on exit.

"I want to produce a query which gives details of all units completed by each
student in all subjects. I have tried to do this by linking the master table"

What happens if you use the Data Model option to create the query. Basically I
understand this as a form with one master table and three detail tables. If you
can create a form displaying what you want, then create a query based on that
form. HTH

Quote:>From the Borland Paradox 7 online help:

You can create a query based on the existing data model of a form or report, or
you can create a data model and run a query on it immediately afterward.

To create a query based on an existing data model,

1. Do one of the following:

Right-click the Open Query  button and choose New.
 Choose File|New|Query.
The Select File dialog box appears.

2. Choose Forms or Reports in the Files Of Type drop-down list. Or, choose Data
Models in the Files Of Type drop-down list to select an existing data model
directly.
3. Type the name of the form or report whose data model you want to use or
select a form or report from the list of files.
 If you chose Data Models in the Files Of Type list, choose the data model you
want to use as the basis for a query.
4. Choose Open.
 Paradox places in the Query window an image of each table used in the data
model of the chosen file (or the chosen data model). It also places example
elements (and inclusion operators, if necessary) to join the tables according to
the document's data model. Multiple tables in a query must be joined in this
way.

5. You can now enter selection conditions and/or additional example elements and
specify fields to display in the Answer table. For an overview, see Working with
the query image.


> Hello,

> I am having trouble designing a query linking four tables:

> The master table contains personal details of each student (name, address
> etc) with a key field  "trainee ref"

> There are three separate tables for three separate subject courses, each of
> which contain details of which course units have been completed,

> trainee ref,    unitA, Unit B, etc....

> Not all students do all three subjects so only a subset of the trainee refs
> appear in each of the subject tables.

> I want to produce a query which gives details of all units completed by each
> student in all subjects. I have tried to do this by linking the master table
> to the subject tables (via the trainee ref field) but can only link one
> table at a time, not all three. I have also tried using the "!" operator on
> the trainee ref field but Paradox will only allow use of this operator in
> one table not in all three.

> I am sure that there is a simple solution to this in a one-step query. ( I
> have also tried using a sequence of queries attached to a pushButton method
> but this does not work either (Paradox locks the first answer table so that
> it cant be used in the next query step)

> Any ides gratefully received

> Thanks

--
Michael

 
 
 

1. HELP PLEASE: URGENT: Need help forming a multi table query

Hello,

I'm using SQL SERVER 7.0. I'm very new to Multi-Table querying, and
can't seem to figure this one out. Any help would be appreciated.

I have 2 tables. one is called RATES and one is called BILLING

In the BILLING table, there are 2 columns, one is COUNTRY and
the other is ACTUAL.  In the RATES table, there are two columns
COUNTRY (which you would use to match the rate to) and RATE.

This is for an ASP page. It has to go row by row of the BILLING
table and calculate the total of a phone call, by multiplying the
ACTUAL of BILLING column with the RATE for that COUNTRY.
It sounds simple, but I get strange (wrong) results everytime.

Any help would be appreciated.  Thanks in advance.

Ayaz Asif

2. NJ-DEVELOPER

3. URGENT help please - need to limit results with multi table join

4. Sending Emails automaticaly

5. APPROACH QUERY-PLEASE PLEASE PLEASE PLEASE HELP

6. Problem when using dates in SQL statements with ADO

7. Help Multi table Query

8. Backup Oracle with Networker -Problem

9. HELP: URGENT: MULTI-TABLE Query (SQL7)

10. help with multi-table query