Relationship with a Primary Key?

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

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)


"Jim Franklin" wrote in message
...
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

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)

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" wrote in message
...
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" wrote in message
...
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.

You checked for a lookup field?


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

"Albert D. Kallal" wrote:

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.

You checked for a lookup field?

Just a tip on that. If you're in the datasheet view click in the
field in question. If a combobox appears it's a lookup field.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

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.

Hi Guys,

I have checked and the OracleNumber field in the Sales Invoices table is a
lookup field, bound to Projects.ProjectID (i.e. in line with the one-to-many
relationship.)

The row source for [Sales Invoices].OracleNumber is:
SELECT Projects.ProjectID, Projects.OracleNumber, Projects.ProjectName,
Projects.ProjectDescription FROM Projects ORDER BY [OracleNumber] DESC;
The Bound Column is set to 1

There are no other lookup fields in either table which I could see having an
effect.

I still don't understand how this is achieving the result that it does.

Many thanks,
Jim


"Tony Toews [MVP]" wrote in message
...
"Albert D. Kallal" wrote:

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.

You checked for a lookup field?

Just a tip on that. If you're in the datasheet view click in the
field in question. If a combobox appears it's a lookup field.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
Granite Fleet Manager http://www.granitefleet.com/

"Jim Franklin" wrote in message
...
Hi Guys,

I have checked and the OracleNumber field in the Sales Invoices table is a
lookup field, bound to Projects.ProjectID (i.e. in line with the
one-to-many relationship.)


Ah ha! Ok, so it is a lookup field. That's what most posters here said would
be a likely cause...

The row source for [Sales Invoices].OracleNumber is:
SELECT Projects.ProjectID, Projects.OracleNumber, Projects.ProjectName,
Projects.ProjectDescription FROM Projects ORDER BY [OracleNumber] DESC;
The Bound Column is set to 1


The bound column setting doesn't really matter here, the bound column ONLY
affects what's going to be displayed. At the end of the day you have a
column that is based on ProjectID - end of story. There's NOTHING more to
say on this at all. A lookup field is only for ***display*** purposes, the
internal value in this case is still product ID. Any condition or any
testing or any query you use will be based on the product ID. Again, the
lookup is ONLY for display.

I still don't understand how this is achieving the result that it does.


You simply have a column that's based on product ID. What else would you
expect to occur here then?

Those lookup fields are rather nasty because they display one thing, and yet
are based on another value. You're going to have the same problem if you use
this look up value in sorting and grouping in a report, it'll display the
one value, but the sorting and Group will actually be the internal product
ID number **not** what's displayed.

So, how lookup fields work are not a surprise, but they certainly are
confusing. Again. I mean if you use a lookup and a join as you did, then the
join would have not worked. So, the internal ID number that's used here when
you manipulate it in SQL statements. It really could not work any other way,
else you join(s) would have NEVER worked in the first place. Note that when
you use sorting and grouping in a report the internal product ID will also
be used again. So, not only is the internal ID used for joints as you note,
but for sorting and grouping in any where clause, you again have to use the
internal value not the external displayed a lookup value.

So, the "key" concept here is that what the column displays is completely
irrelevant and has no bearing how this will function in sql queries. You
have a simple product ID column, and that is the end of the story.

To say that a lot of developers here don't like the use of look up fields,
and strongly mention that you should avoid them, do read the following:

http://www.mvps.org/access/tencommandments.htm

Pay attention to #2 on the above list, and that link gives:

http://www.mvps.org/access/lookupfields.htm

When you first start using lookup of fields, they make things rather easy,
but as you seen it can be a source of confusing. What's worse is now you're
stuck with this as part of your design. Removing that look up field can now
break all kinds of reports, queries, and code + forms and all kinds of
things that now have a design based on the fact that look up field is in
operation. So it's not easy to remove it this lookup field now that its
squirmed its way into your whole applciaton.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada