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

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

Tatakau wrote:
>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

--
Sam

Message posted via http://www.accessmonster.com
.

Quantcast