Query is not doing what it's supposed to be doing!

[Tatakau]

Haha, of course the computer is only doing exactly what I tell it to do. But
for some reason I can't tell it to do the right thing! I mean, it looks fine
to me...

I've got a whole mess of tables & relationships, but only a few matter for
this question. First, I've got an Owners table, with fields ID, name, phone,
etc. Second, I've got a Referrals table, with fields ID, name, phone, and
Owner. A little background - Owners are people who have bought timeshares at
our resort, and they refer people to come check out our promotions. IE., the
relationship between Owners and Referrals SHOULD be one-to-many. However, I
couldn't get that to work correctly with table entry and such, so I just made
it an ambiguous relationship. All that is working fine now.

My problem is with the queries I am trying to put together. In design view
I have the Owners and the Referrals tables up, with an inner join
relationship between [Referrals].[Owner] and [Owner].[ID]. I am trying to
display the Referrals name, and the owner who referred them. So my select
statement calls on Referrals.ID, Referrals.Owner, and Owner.ID. Here's the
setup and SQL:

Table: Referrals
ID - Autonumber
Name - Text
Owner - Number

Table: Owners
ID - Autonumber
Name - Text

SELECT referrals.id, referrals.owner, owners.id
FROM owners RIGHT JOIN referrals ON owners.id = referrals.owner;

Now Referrals.Owner and Owner.ID are supposed to be exactly the same. But
they aren't! That doesn't make any sense at all!!!! This is a sample of
what I'm getting:

Referral.ID Referral.Owner Owner.ID
1 85
2 85
3 85
4 85
5 89 17
6 89 17
7 91
8 91
9 13 1
10 13 1

I've been going nuts trying to figure out what the heck is going on, and I
am beginning to seriously consider taking a shotgun to my CPU. Any
suggestions?

Thank you!

Nick

 

[OfficeDev18 via AccessMonster.com]

Hi, Nick,

and put away the smokin' thing. The anthem of all programmers, new and old,
is "The durn thing's doing what I told it to do, not what I want it to do."
Now where does that place the blame..... Just kidding.

Your problem seems to be multi-faceted, and your solution is likewise multi-
step. First of all, as you already know, the tables' relationship is on the
rocks. Let's re-do it. First of all, fix up the tables manually so Owners.ID
and Referrals.Owner equal each other correctly. Close the table(s).

Create a new form for data entry for referrals. If you already have one, make
a new one. Save and close it. Now, open the data entry form for the Owners
table in design mode. Using the subform wizard, put the new referrals form on
the owners form as a subform, and create the relationship right there. Put a
command button on the owners form to control the opening of the subform to
add/modify referral data.

What that does is when you open the subform to add a referral, it puts the
correct number (foreign key) in the correct field in the referrals table,
allows you to add records, and keep them under control of the main (owners)
form, as it should be.

Oh, yes, once this is all in place, slap the hands of anybody who attempts to
add referral information any other way.

HTH

Haha, of course the computer is only doing exactly what I tell it to do. But
for some reason I can't tell it to do the right thing! I mean, it looks fine
to me...

I've got a whole mess of tables & relationships, but only a few matter for
this question. First, I've got an Owners table, with fields ID, name, phone,
etc. Second, I've got a Referrals table, with fields ID, name, phone, and
Owner. A little background - Owners are people who have bought timeshares at
our resort, and they refer people to come check out our promotions. IE., the
relationship between Owners and Referrals SHOULD be one-to-many. However, I
couldn't get that to work correctly with table entry and such, so I just made
it an ambiguous relationship. All that is working fine now.

My problem is with the queries I am trying to put together. In design view
I have the Owners and the Referrals tables up, with an inner join
relationship between [Referrals].[Owner] and [Owner].[ID]. I am trying to
display the Referrals name, and the owner who referred them. So my select
statement calls on Referrals.ID, Referrals.Owner, and Owner.ID. Here's the
setup and SQL:

Table: Referrals
ID - Autonumber
Name - Text
Owner - Number

Table: Owners
ID - Autonumber
Name - Text

SELECT referrals.id, referrals.owner, owners.id
FROM owners RIGHT JOIN referrals ON owners.id = referrals.owner;

Now Referrals.Owner and Owner.ID are supposed to be exactly the same. But
they aren't! That doesn't make any sense at all!!!! This is a sample of
what I'm getting:

Referral.ID Referral.Owner Owner.ID
1 85
2 85
3 85
4 85
5 89 17
6 89 17
7 91
8 91
9 13 1
10 13 1

I've been going nuts trying to figure out what the heck is going on, and I
am beginning to seriously consider taking a shotgun to my CPU. Any
suggestions?

Thank you!

Nick

 

[Tom Ellison]

Dear Tata:

Watch where you're pointing that shotgun! In pointing it at your CPU, I
believe you are mistaken as to the cause of the problem. On the other hand,
suicide solves nothing (at least technically).

Looking first at referrals. Here you have (potentially) a many-to-many
relationship. Any referee (not the striped shirt version) could be referred
by one or more owners. However, establishing an identity for a referee
(ree-fer-ee) may not be probable, or necessary. Do you have a procedure in
mind that will establish an identity for referees? That is, if two owners
refer the same person, do you expect to be able to recognize reliably that
it is the same person? Will this be necessary, or even useful, in what you
expect the database to be doing?

Yet you say the relationship is one-to-many. I infer that any owner may
refer an indefinite number of referees. Never would an owner refer someone
who (possibly unknown to that owner) had already been referred by another
owner. Or, perhaps, you will treat multiple referals of the same person as
though they were independent referees and you're not interested in
eliminating any duplication. This is certainly acceptable and may indeed be
desirable, but such decisions should be made consciously, and the
ramifications explored and understood. For example, a mailing to all
referees would then possibly contain multiple pieces generated to the same
person. Another difficulty is that the same owner could refer the same
referee repeatedly.

I don't know if this has anything to do with your difficulty in creating and
managing a one-to-many relationship between owners and referals. You
conclude, "All that is working fine now." I'm not sure I agree with that.
Failure to create and maintain the relationships can cause not only database
problems but also "real world" problems that may not surface immediately.
Again, such decisions should not be made in a vacuum without regard to their
likely repercussions later.

As to your specific question, I'd first like you to post the SQL that gave
you the data you posted. Next, I suggest you look at the actual data in
your table to see whether it is the JOIN that is malfunctioning, or is it
the query response returning the Referal.Owner value. Please consider that,
having failed to create a relationship, it is possible that an owner was
entered, a referal for that owner was created, the owner was deleted, and
that owner was subsequently re-entered. That's just one example of how a
logically corrupted database could occur because no relationship was created
and enforced.

So, just a bit of snooping and analysis may answer your problems.

Instead of posting just the query results, could you post the relevant rows
of the tables as well. That's where some resolution may be found.

Tom Ellison

Haha, of course the computer is only doing exactly what I tell it to do.
But
for some reason I can't tell it to do the right thing! I mean, it looks
fine
to me...

I've got a whole mess of tables & relationships, but only a few matter for
this question. First, I've got an Owners table, with fields ID, name,
phone,
etc. Second, I've got a Referrals table, with fields ID, name, phone, and
Owner. A little background - Owners are people who have bought timeshares
at
our resort, and they refer people to come check out our promotions. IE.,
the
relationship between Owners and Referrals SHOULD be one-to-many. However,
I
couldn't get that to work correctly with table entry and such, so I just
made
it an ambiguous relationship. All that is working fine now.

My problem is with the queries I am trying to put together. In design
view
I have the Owners and the Referrals tables up, with an inner join
relationship between [Referrals].[Owner] and [Owner].[ID]. I am trying to
display the Referrals name, and the owner who referred them. So my select
statement calls on Referrals.ID, Referrals.Owner, and Owner.ID. Here's
the
setup and SQL:

Table: Referrals
ID - Autonumber
Name - Text
Owner - Number

Table: Owners
ID - Autonumber
Name - Text

SELECT referrals.id, referrals.owner, owners.id
FROM owners RIGHT JOIN referrals ON owners.id = referrals.owner;

Now Referrals.Owner and Owner.ID are supposed to be exactly the same. But
they aren't! That doesn't make any sense at all!!!! This is a sample of
what I'm getting:

Referral.ID Referral.Owner Owner.ID
1 85
2 85
3 85
4 85
5 89 17
6 89 17
7 91
8 91
9 13 1
10 13 1

I've been going nuts trying to figure out what the heck is going on, and I
am beginning to seriously consider taking a shotgun to my CPU. Any
suggestions?

Thank you!

Nick

 

[Tatakau]

Happy New Year! My New Year's Resolution is: Never drink again!!! Or
barring that, lay off the UV Blue...

Anyway, I read both of your posts fully several times and I am working on
redesigning the tables and relationships completely. Only I'm having some
problems defining the relationships, which originally led me to create
incorrect relationships the last time around. When I try to create a
relationship between the owners and the referrals tables, the relationship
just... isn't working! I don't know how else to explain it.

I make tables "owners" and "referrals", each with an autonumber primary key.
Then I make the lookup for the Referrals.OwnerID field to a combo box of
Owners.Index (the primary key of the Owners table, autonumbered). That works
fine. But when I try to create a relationship (Enforce Referential Integrity
& Cascade Updates, no cascading deletes), Access starts to rebel. If the
referrals table is empty, it will let me make the relationship, but when I
try to add entries I get the error:

"You cannot add or change a record because a related record is required in
table 'owners'. > OK/Help

So I definitely have a relationship problem. If I try to create the
relationship After adding data to the Refferals table, I get this error
instead:

Microsoft Access Cannot create this relationship and enforce referential
integrity. Data in the table 'referrals' violates referential integrity
rules. For example, there may ...... blah blah blah.... Edit the data so
that records in the primary table exist for all related records.... > OK

imo, Access is mixing up which fields are involved in the relationship, or
which fields it should be using as the primary table... or whatever. I don't
know. Maybe my database is corrupt? Which is odd... because I'm trying to
create new tables....

Gotta run.

Nick

Dear Tata:

Watch where you're pointing that shotgun! In pointing it at your CPU, I
believe you are mistaken as to the cause of the problem. On the other hand,
suicide solves nothing (at least technically).

Looking first at referrals. Here you have (potentially) a many-to-many
relationship. Any referee (not the striped shirt version) could be referred
by one or more owners. However, establishing an identity for a referee
(ree-fer-ee) may not be probable, or necessary. Do you have a procedure in
mind that will establish an identity for referees? That is, if two owners
refer the same person, do you expect to be able to recognize reliably that
it is the same person? Will this be necessary, or even useful, in what you
expect the database to be doing?

Yet you say the relationship is one-to-many. I infer that any owner may
refer an indefinite number of referees. Never would an owner refer someone
who (possibly unknown to that owner) had already been referred by another
owner. Or, perhaps, you will treat multiple referals of the same person as
though they were independent referees and you're not interested in
eliminating any duplication. This is certainly acceptable and may indeed be
desirable, but such decisions should be made consciously, and the
ramifications explored and understood. For example, a mailing to all
referees would then possibly contain multiple pieces generated to the same
person. Another difficulty is that the same owner could refer the same
referee repeatedly.

I don't know if this has anything to do with your difficulty in creating and
managing a one-to-many relationship between owners and referals. You
conclude, "All that is working fine now." I'm not sure I agree with that.
Failure to create and maintain the relationships can cause not only database
problems but also "real world" problems that may not surface immediately.
Again, such decisions should not be made in a vacuum without regard to their
likely repercussions later.

As to your specific question, I'd first like you to post the SQL that gave
you the data you posted. Next, I suggest you look at the actual data in
your table to see whether it is the JOIN that is malfunctioning, or is it
the query response returning the Referal.Owner value. Please consider that,
having failed to create a relationship, it is possible that an owner was
entered, a referal for that owner was created, the owner was deleted, and
that owner was subsequently re-entered. That's just one example of how a
logically corrupted database could occur because no relationship was created
and enforced.

So, just a bit of snooping and analysis may answer your problems.

Instead of posting just the query results, could you post the relevant rows
of the tables as well. That's where some resolution may be found.

Tom Ellison

Haha, of course the computer is only doing exactly what I tell it to do.
But
for some reason I can't tell it to do the right thing! I mean, it looks
fine
to me...

I've got a whole mess of tables & relationships, but only a few matter for
this question. First, I've got an Owners table, with fields ID, name,
phone,
etc. Second, I've got a Referrals table, with fields ID, name, phone, and
Owner. A little background - Owners are people who have bought timeshares
at
our resort, and they refer people to come check out our promotions. IE.,
the
relationship between Owners and Referrals SHOULD be one-to-many. However,
I
couldn't get that to work correctly with table entry and such, so I just
made
it an ambiguous relationship. All that is working fine now.

My problem is with the queries I am trying to put together. In design
view
I have the Owners and the Referrals tables up, with an inner join
relationship between [Referrals].[Owner] and [Owner].[ID]. I am trying to
display the Referrals name, and the owner who referred them. So my select
statement calls on Referrals.ID, Referrals.Owner, and Owner.ID. Here's
the
setup and SQL:

Table: Referrals
ID - Autonumber
Name - Text
Owner - Number

Table: Owners
ID - Autonumber
Name - Text

SELECT referrals.id, referrals.owner, owners.id
FROM owners RIGHT JOIN referrals ON owners.id = referrals.owner;

Now Referrals.Owner and Owner.ID are supposed to be exactly the same. But
they aren't! That doesn't make any sense at all!!!! This is a sample of
what I'm getting:

Referral.ID Referral.Owner Owner.ID
1 85
2 85
3 85
4 85
5 89 17
6 89 17
7 91
8 91
9 13 1
10 13 1

I've been going nuts trying to figure out what the heck is going on, and I
am beginning to seriously consider taking a shotgun to my CPU. Any
suggestions?

Thank you!

Nick

 

[OfficeDev18 via AccessMonster.com]

Hi, Tata,

HNY to you too and see how you like it!

That's what you get for not following my advice.... just kidding.

Seriously, though, your first step, if you want this relationship to work, is
to fix your tables manually, and make SURE that there is an owner record for
every referral. If you have a referral without an owner, you have two choices:
1- delete the referral, or 2- create an owner. Also, you must delete any
duplicate owners. Your second step MUST be to generate the autonumbers
(Primary Key) for the owners table, and MANUALLY populating the referral
tables (Foreign Key) with the correct owners. If you don't take these
critical first steps, your relationship is definitely headed for the rocks.
And I'm not kidding!

HTH

Happy New Year! My New Year's Resolution is: Never drink again!!! Or
barring that, lay off the UV Blue...

Anyway, I read both of your posts fully several times and I am working on
redesigning the tables and relationships completely. Only I'm having some
problems defining the relationships, which originally led me to create
incorrect relationships the last time around. When I try to create a
relationship between the owners and the referrals tables, the relationship
just... isn't working! I don't know how else to explain it.

I make tables "owners" and "referrals", each with an autonumber primary key.
Then I make the lookup for the Referrals.OwnerID field to a combo box of
Owners.Index (the primary key of the Owners table, autonumbered). That works
fine. But when I try to create a relationship (Enforce Referential Integrity
& Cascade Updates, no cascading deletes), Access starts to rebel. If the
referrals table is empty, it will let me make the relationship, but when I
try to add entries I get the error:

"You cannot add or change a record because a related record is required in
table 'owners'. > OK/Help

So I definitely have a relationship problem. If I try to create the
relationship After adding data to the Refferals table, I get this error
instead:

Microsoft Access Cannot create this relationship and enforce referential
integrity. Data in the table 'referrals' violates referential integrity
rules. For example, there may ...... blah blah blah.... Edit the data so
that records in the primary table exist for all related records.... > OK

imo, Access is mixing up which fields are involved in the relationship, or
which fields it should be using as the primary table... or whatever. I don't
know. Maybe my database is corrupt? Which is odd... because I'm trying to
create new tables....

Gotta run.

Nick

Dear Tata:

[quoted text clipped - 112 lines]

 

[Tatakau]

Wow, I think I just figured out was was wrong. All my problems stemmed from
setting my Bound Column to 0 with combo boxes. I could have sworn that when
you increased the column count you had to set it to zero! Ugh... what a
headache over such a small thing (first items equal to 0 or to 1). I think I
got it confused with the form combo boxes (Control
Source=[guest].[Column](0)).

Anyway, I feel stupid. At least this problem is finally over and done with.
Thanks Tom and... umm... OfficeDev18 for helping me solve this problem!

Nick

Hi, Tata,

HNY to you too and see how you like it!

That's what you get for not following my advice.... just kidding.

Seriously, though, your first step, if you want this relationship to work, is
to fix your tables manually, and make SURE that there is an owner record for
every referral. If you have a referral without an owner, you have two choices:
1- delete the referral, or 2- create an owner. Also, you must delete any
duplicate owners. Your second step MUST be to generate the autonumbers
(Primary Key) for the owners table, and MANUALLY populating the referral
tables (Foreign Key) with the correct owners. If you don't take these
critical first steps, your relationship is definitely headed for the rocks.
And I'm not kidding!

HTH

Happy New Year! My New Year's Resolution is: Never drink again!!! Or
barring that, lay off the UV Blue...

Anyway, I read both of your posts fully several times and I am working on
redesigning the tables and relationships completely. Only I'm having some
problems defining the relationships, which originally led me to create
incorrect relationships the last time around. When I try to create a
relationship between the owners and the referrals tables, the relationship
just... isn't working! I don't know how else to explain it.

I make tables "owners" and "referrals", each with an autonumber primary key.
Then I make the lookup for the Referrals.OwnerID field to a combo box of
Owners.Index (the primary key of the Owners table, autonumbered). That works
fine. But when I try to create a relationship (Enforce Referential Integrity
& Cascade Updates, no cascading deletes), Access starts to rebel. If the
referrals table is empty, it will let me make the relationship, but when I
try to add entries I get the error:

"You cannot add or change a record because a related record is required in
table 'owners'. > OK/Help

So I definitely have a relationship problem. If I try to create the
relationship After adding data to the Refferals table, I get this error
instead:

Microsoft Access Cannot create this relationship and enforce referential
integrity. Data in the table 'referrals' violates referential integrity
rules. For example, there may ...... blah blah blah.... Edit the data so
that records in the primary table exist for all related records.... > OK

imo, Access is mixing up which fields are involved in the relationship, or
which fields it should be using as the primary table... or whatever. I don't
know. Maybe my database is corrupt? Which is odd... because I'm trying to
create new tables....

Gotta run.

Nick

Dear Tata:

[quoted text clipped - 112 lines]

Nick