join with different number of rows

S

stickandrock

I have a table(A) that is joined to 2 other tables (B and C). The Key in all
3 of these tables is Part. Part 123 in on Table A 1 time, on Table B 3 times
and on Table C 2 times.

Sample data:
Table A
Part
123

Table B
Part Qty
123 100
123 125
123 278

Table C
Part Qty
123 2500
123 4300

Current Output
123 100 2500
123 125 4300
123 278 4300

Desired Output
123 100 2500
123 125 4300
123 278 blank

Any thoughts?????

Thanks,
Don
 
K

Ken Snell

Any thoughts about what? You haven't told us what is your question, or what
is the problem that you're having?
 
S

stickandrock

based on my desired output I what to show 3 rows for the part the second
column should show 3 rows of data (Values from Table B) and the third column
should only show 2 rows (Values from Table C). The last value from Table C
is being repeated on the third row and I want it to just be blank.
 
K

Ken Snell

Do this in a Report. You can set the textbox that is bound to third column
from the query to not show duplicate values.
 
S

stickandrock

I need to sent the output to an excel file for the end users or else I would
have keeped it all on Access.

Thanks
 
J

John Spencer

Based on your published sample data I don't see how you even got the current
output you are showing. Could you post the query you would use to get that
result?

Also, what logic tells you that qty 100 goes with qty 2500 and qty 125 goes
with qty 4300 and qty 278 has no match. Is it simply order of qty in
ascending size?

You could write a query to rank each qty by part and then use the ranking to
match up. That ASSUMES there would be no duplicate qty in either table.

SELECT B1.Part, B1.Qty, Count(B2.Part) as Rank
FROM B as B1 LEFT JOIN B as B2
On B1.Part = B2.Part
AND B1.Qty <= B2.Qty
GROUP BY B1.Part, B1.Qty

SELECT C1.Part, C1.Qty, Count(C2.Part) as Rank
FROM C as C1 LEFT JOIN C as C2
On C1.Part = C2.Part
AND C1.Qty <= C2.Qty
GROUP BY C1.Part, C1.Qty

Then using those two queries and assuming that B always has as many or more
records than C.
SELECT BQuery.Part, BQuery.Qty, CQuery.Qty
FROM BQuery LEFT JOIN CQuery
ON BQuery.Part = C.Query.Part
AND BQuery.Rank = C.QueryRank

Lots of assumptions and lots of chances for errors.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
S

stickandrock

it's a hard one to explain....

There is no connection between the Qty columns in Table B and C. I have
trying to create an output that groups all the detail rows from Table B and C
by their common part number. If Part has 2 rows of data on Table B (Show
those 2 values) and if that same Part has 3 rows of data on Table C (Show
those 3 values). So that in the end I have an output of 3 rows of data from
that Part, ColumnA = Part Number, ColumnB = show the first 2 rows populated
from Table B and the 3 row Column B is blank, ColumnC = shows data in all 3
rows populated from Table C.

Not sure if I am making it more complicated that I need to, but this is what
I am trying to work through.


Thanks,
D

SQL:
SELECT [z-dds-step3-Unq-Facy-Part-Combinations].[Facility id],
[z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr], IIf([Vendor
Receipts]![Pieces]=Null,Null,[Vendor Receipts]![Pieces]) AS [1072 Pieces],
[Vendor Receipts].[Process id] AS 1072ID, IIf([Vendor
Receipts_1]![Pieces]=Null,Null,[Vendor Receipts_1]![Pieces]) AS [1079
Pieces], [Vendor Receipts_1].[Process id] AS 1079ID
FROM ([z-dds-step3-Unq-Facy-Part-Combinations] INNER JOIN [Vendor Receipts]
ON ([z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr] = [Vendor
Receipts].[Part Nbr]) AND ([z-dds-step3-Unq-Facy-Part-Combinations].[Facility
id] = [Vendor Receipts].[Facility id])) INNER JOIN [Vendor Receipts] AS
[Vendor Receipts_1] ON ([z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr] =
[Vendor Receipts_1].[Part Nbr]) AND
([z-dds-step3-Unq-Facy-Part-Combinations].[Facility id] = [Vendor
Receipts_1].[Facility id])
WHERE ((([Vendor Receipts].[Process id])="1072") AND (([Vendor
Receipts_1].[Process id])="1079"));


I tried to through in the checks for Null to maybe over-ride the in-correct
value from being displayed.

Sample Output:
Facility id Part Nbr 1072 Pieces 1072ID 1079 Pieces 1079ID
001 00003978 -200 1072 205 1079
001 00003978 -200 1072 210 1079

Desired Output:
Facility id Part Nbr 1072 Pieces 1072ID 1079 Pieces 1079ID
001 00003978 -200 1072 205 1079
001 00003978 210 1079

The Second -200 under 1072 Pieces is not present on the table. For that
condition there is only row for -200. Not 2 at the output appears to show
there is and this happens when ever there is an uneven amount of rows of data
for each detail grouping.
 
K

KARL DEWEY

Try this --
SELECT TableA.Part, TableB.QTY AS QTY_B, TableC.QTY AS QTY_C
FROM (TableA LEFT JOIN TableB ON TableA.Part = TableB.Part) LEFT JOIN TableC
ON TableA.Part = TableC.Part
ORDER BY TableA.Part, TableB.QTY, TableC.QTY;

--
Build a little, test a little.


stickandrock said:
it's a hard one to explain....

There is no connection between the Qty columns in Table B and C. I have
trying to create an output that groups all the detail rows from Table B and C
by their common part number. If Part has 2 rows of data on Table B (Show
those 2 values) and if that same Part has 3 rows of data on Table C (Show
those 3 values). So that in the end I have an output of 3 rows of data from
that Part, ColumnA = Part Number, ColumnB = show the first 2 rows populated
from Table B and the 3 row Column B is blank, ColumnC = shows data in all 3
rows populated from Table C.

Not sure if I am making it more complicated that I need to, but this is what
I am trying to work through.


Thanks,
D

SQL:
SELECT [z-dds-step3-Unq-Facy-Part-Combinations].[Facility id],
[z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr], IIf([Vendor
Receipts]![Pieces]=Null,Null,[Vendor Receipts]![Pieces]) AS [1072 Pieces],
[Vendor Receipts].[Process id] AS 1072ID, IIf([Vendor
Receipts_1]![Pieces]=Null,Null,[Vendor Receipts_1]![Pieces]) AS [1079
Pieces], [Vendor Receipts_1].[Process id] AS 1079ID
FROM ([z-dds-step3-Unq-Facy-Part-Combinations] INNER JOIN [Vendor Receipts]
ON ([z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr] = [Vendor
Receipts].[Part Nbr]) AND ([z-dds-step3-Unq-Facy-Part-Combinations].[Facility
id] = [Vendor Receipts].[Facility id])) INNER JOIN [Vendor Receipts] AS
[Vendor Receipts_1] ON ([z-dds-step3-Unq-Facy-Part-Combinations].[Part Nbr] =
[Vendor Receipts_1].[Part Nbr]) AND
([z-dds-step3-Unq-Facy-Part-Combinations].[Facility id] = [Vendor
Receipts_1].[Facility id])
WHERE ((([Vendor Receipts].[Process id])="1072") AND (([Vendor
Receipts_1].[Process id])="1079"));


I tried to through in the checks for Null to maybe over-ride the in-correct
value from being displayed.

Sample Output:
Facility id Part Nbr 1072 Pieces 1072ID 1079 Pieces 1079ID
001 00003978 -200 1072 205 1079
001 00003978 -200 1072 210 1079

Desired Output:
Facility id Part Nbr 1072 Pieces 1072ID 1079 Pieces 1079ID
001 00003978 -200 1072 205 1079
001 00003978 210 1079

The Second -200 under 1072 Pieces is not present on the table. For that
condition there is only row for -200. Not 2 at the output appears to show
there is and this happens when ever there is an uneven amount of rows of data
for each detail grouping.

John Spencer said:
Based on your published sample data I don't see how you even got the current
output you are showing. Could you post the query you would use to get that
result?

Also, what logic tells you that qty 100 goes with qty 2500 and qty 125 goes
with qty 4300 and qty 278 has no match. Is it simply order of qty in
ascending size?

You could write a query to rank each qty by part and then use the ranking to
match up. That ASSUMES there would be no duplicate qty in either table.

SELECT B1.Part, B1.Qty, Count(B2.Part) as Rank
FROM B as B1 LEFT JOIN B as B2
On B1.Part = B2.Part
AND B1.Qty <= B2.Qty
GROUP BY B1.Part, B1.Qty

SELECT C1.Part, C1.Qty, Count(C2.Part) as Rank
FROM C as C1 LEFT JOIN C as C2
On C1.Part = C2.Part
AND C1.Qty <= C2.Qty
GROUP BY C1.Part, C1.Qty

Then using those two queries and assuming that B always has as many or more
records than C.
SELECT BQuery.Part, BQuery.Qty, CQuery.Qty
FROM BQuery LEFT JOIN CQuery
ON BQuery.Part = C.Query.Part
AND BQuery.Rank = C.QueryRank

Lots of assumptions and lots of chances for errors.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combine the contents of a field so only one row is displayed 1
Query/Table question 1
Ranking Records with multiple criteria. 1
Transpose 2 rows to 2 columns 1
Excel Formula Help With Subtraction 2
Multiple Critera in Running Total Query (DSum I think) 3
optimizing sub queries 5
Update table using partial word match 3

Top