Relationship with a Primary Key?

[Jim Franklin]

Hi,

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
Invoices].OracleNumber
FROM Projects INNER JOIN [Sales Invoices] ON Projects.ProjectID = [Sales
Invoices].OracleNumber
WHERE (((Projects.ProjectID)=144182789));

returns:

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!

Jim

 

[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
(French)

 

[Fred]

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
outcome.

Cheers,
Jim

 

[Fred]

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.