Stupid Inner Join...

[Guest]

Well, actually, I'm the one who must be stupid, but anyway... This query
gives me blank columns:

SELECT [Order Details].[Order Number], [Order Details].[Part Number], [Order
Details].Qty, tblPartNumbers.Alloy, tblPartNumbers.Thick FROM [Order Details]
RIGHT JOIN tblPartNumbers ON [Order Details].[Part Number] =
tblPartNumbers.[Part Number] ORDER BY [Order Details].[Order Number];

Both tables are populated properly, but I can't see the problem. On the one
side the table [Order Details] has a primary key of [Order Number] while on
the many side, tblPartNumbers has a multi field index as the primary key of
[Order Number] and [Part Number]. Please give me a clue as to what's the
dealie-o.

 

[John Spencer]

Perhaps you want an INNER JOIN instead of the RIGHT JOIN.

The right join is going to return every record in thtblPartNumbers whther or
not it has a matching record in [Order Details]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks for the reply, John! Actually, when I replace the right join with an
inner join, I get no records returned. I can't seem to think my way around
this one, but it helps to know that you're interested in the posting... You
are the man when it comes to this stuff. Off the top of my head, I'd say
that the relationship between the two tables must be screwy.
--
Why are you asking me? I dont know what Im doing!

Jaybird

Perhaps you want an INNER JOIN instead of the RIGHT JOIN.

The right join is going to return every record in thtblPartNumbers whther or
not it has a matching record in [Order Details]

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Well, actually, I'm the one who must be stupid, but anyway... This query
gives me blank columns:

SELECT [Order Details].[Order Number], [Order Details].[Part Number],
[Order
Details].Qty, tblPartNumbers.Alloy, tblPartNumbers.Thick FROM [Order
Details]
RIGHT JOIN tblPartNumbers ON [Order Details].[Part Number] =
tblPartNumbers.[Part Number] ORDER BY [Order Details].[Order Number];

Both tables are populated properly, but I can't see the problem. On the
one
side the table [Order Details] has a primary key of [Order Number] while
on
the many side, tblPartNumbers has a multi field index as the primary key
of
[Order Number] and [Part Number]. Please give me a clue as to what's the
dealie-o.

 

[Guest]

Your answer to John's INNER JOIN suggestion caused me to re-read your
original post, and now I am more confused than ever.

1. Why would tblPartNumbers contain an [Order Number] field? In most
databases tblPartNumbers whould be a table of parts and their
characteristics, and should have nothing to do with orders.

2. Given that it does contain an [Order Number], then I think your join
clause should look like:

FROM [Order Details]
LEFT JOIN tblPartNumbers
ON [Order Details].[Part Number] = tblPartNumbers.[Part Number]
AND [Order Details].[Order Number] = tblPartNumbers.[Order Number]

I don't think it makes much sense to use a Right Join in this case. Maybe a
LEFT JOIN, but not a Right.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Well, actually, I'm the one who must be stupid, but anyway... This query
gives me blank columns:

SELECT [Order Details].[Order Number],

[Order Details].[Part Number],
[Order Details].Qty,
tblPartNumbers.Alloy,
tblPartNumbers.Thick
FROM [Order Details]
RIGHT JOIN tblPartNumbers
ON [Order Details].[Part Number] = tblPartNumbers.[Part Number]
ORDER BY [Order Details].[Order Number];

Both tables are populated properly, but I can't see the problem. On the one
side the table [Order Details] has a primary key of [Order Number] while on
the many side, tblPartNumbers has a multi field index as the primary key of
[Order Number] and [Part Number]. Please give me a clue as to what's the
dealie-o.

 

[Guest]

Try changing the RIGHT JOIN to LEFT JOIN.

 

[Guest]

Hey, thanks to everyone for responding... But, as I thought, my brain is
addled... I was trying to use the wrong table in my query. I was also not
describing my problem at all well, or the relationships between the tables!
It works fine now that I've had my second cup of coffee...
--
Why are you asking me? I dont know what Im doing!

Jaybird

Try changing the RIGHT JOIN to LEFT JOIN.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Well, actually, I'm the one who must be stupid, but anyway... This query
gives me blank columns:

SELECT [Order Details].[Order Number], [Order Details].[Part Number], [Order
Details].Qty, tblPartNumbers.Alloy, tblPartNumbers.Thick FROM [Order Details]
RIGHT JOIN tblPartNumbers ON [Order Details].[Part Number] =
tblPartNumbers.[Part Number] ORDER BY [Order Details].[Order Number];

Both tables are populated properly, but I can't see the problem. On the one
side the table [Order Details] has a primary key of [Order Number] while on
the many side, tblPartNumbers has a multi field index as the primary key of
[Order Number] and [Part Number]. Please give me a clue as to what's the
dealie-o.