Composite Key??? in linked data.

[bcbrock]

I am linking into some DBIV databases and I've run into a snag.
Here's the situation...

I work in a School District, and we employ a program (SASI) that uses
DBIV databases to house student information. Each school has their
own server, and their own dedicated copies of the databases.
Overnight, those databases are copied to a central server where they
are consolidated into one database.

The databases contain a field called "stulink" which acts as a primary
key in SASI, although if you open the database in Access, there is no
primary key identified. When the individual databases are
consolidated into one big file, I wind up with several records where
the stulink field is duplicated - a single record for each of the
schools. SASI is able to distinguish the records in the consolidated
file with another field called SchoolNum. So, my file may look like:

stulink | SchoolNum | FirstName | LastName |
1 | 109 | Tom | Smith |
1 | 218 | Janice | Jones |
1 | 461 | Jimmy | Jenkins |


I am now trying to match records from a second table which also has
both the stulink and schoolnum fields, but I think I must be doing
something wrong. My currently defined relationships:

ASTU:Stulink <-- one to many -> AIMM:Stulink
ASTU: SchoolNum <- one to many -> AIMM:SchoolNum

When I create my query, I can get all the records that have the
stulink in common to group together, but I've been unsuccessful in
getting the schoolnum fields to match up at the same time. So,
instead of matching only those records from AIMM that belong to the
stulink AND schoolnum, I'm getting records that match the stulink
field only, regardless of which schoolnum is indicated in AIMM.

Any suggestions???

 

[Albert D. Kallal]

It's not quite clear to me why you're talking about defineing relationships
here?

When you use the query builder, you're free to join on many fields, it's
just a matter of fact, or in a sense most of the time that you're going to
make your joins based on relationships that you've defined in your database.
However this is not a requirement for your joins.

Defining these relationships in the relationships window does not affect
your ability, or lack of ability to build a query that allows you to join
talbes on partilar fields. I guess I am pointing out that these "defined"
relationships are VERY separate issues in how you build a sql join.

You should be able to just drop in your first table in the query builder,
and then drop in the second table into the query builder, and then simply
draw join lines between the two sets of fields and you should be done.

It is not clear if you've tried the above join in the query builder, but it
should work without problems.