Relationship with a Primary Key?


Jim Franklin


I am looking into a .mdb which has been developed for one of my clients by a
novice developer. The db has some relationships which I cannot fathom out
how they are working, for example:

Table "Projects" contains 2 fields (amongst others), ProjectID and
OracleNumber. ProjectID is an autonumber field and is the Primary Key.

Table "Sales Invoices" also contains 2 fields amongst others, ID
(=AutoNumber, Primary Key) and OracleNumber.

In both tables, the index is set on OracleNumber to allow duplicates.

The developer has added a One-To-Many relationship between Project.ProjectID
and Sales Invoices.OracleNumber. There is no referential integrity enforced
and there are NO records in Projects where the ProjectID matches any
OracleNumber in the Sales Invoices table.

And yet, if I run the following query, records are returned, as though the
join was on the two OracleNumber fields.

SELECT Projects.ProjectID, [Sales Invoices].ID, [Sales
FROM Projects INNER JOIN [Sales Invoices] ON Projects.ProjectID = [Sales
WHERE (((Projects.ProjectID)=144182789));


Project ID ID OracleNumber
144182789 26 77050005
144182789 32 77050005
144182789 33 77050005
144182789 69 77050005
144182789 78 77050005
144182789 97 77050005
144182789 104 77050005
144182789 230 77050005
144182789 242 77050005
144182789 248 77050005
144182789 249 77050005

If I look at the Projects record for Project ID 144182789, it has a value in
the OracleNumber field of 77050005.

Likewise, a main form with recordsource table Projects has a subform with a
recordsource table Sales Invoices. The subform property for 'Link Master
Fields' is set to "ProjectID" and 'Link Child Fields' is "OracleNumber". And
yet the same records are returned by the subform when Project ID 144182789
is viewed in the main form.

Can anyone explain to me how this is happening? Is Access ignoring the join
and making a join between the OracleNumber fields in each table?

Thank you for reading. Any help is very much appreciated!


Sylvain Lafontaine

Not sure of what I'm saying here but it's possible that OracleNumber show
you a different thing in the Select statement if it's also a Lookup Field.
The base number will be used for performing the join but the result of the
lookup will be displayed.

Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server


One thing 40 years of troubleshooing has taught me is that when I have it
narrowed down that the impossible appears to be happenning, to recheck my
observations and assumptions.

Including, cound something that the developer did be confusing the
observations? (such as field captions/titles which are different than the
field names)

Jim Franklin

All checked I am afraid Fred. There are a couple of captions etc that were
changed (e.g. ProjectID is "Project ID") but nothing that could cause this



I was more thinking about something that could have caused a mis-observation
of what's happening.

I'm sure you've already thought about (after backing up)

- plan B (compact and repair)

-plan C recreate the tables with "make table" queries, link the new ones,
and ditch the old ones.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question