If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
join with different number of rows
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 |
#2
|
|||
|
|||
join with different number of rows
Any thoughts about what? You haven't told us what is your question, or what
is the problem that you're having? -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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 |
#3
|
|||
|
|||
join with different number of rows
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. "Ken Snell" wrote: Any thoughts about what? You haven't told us what is your question, or what is the problem that you're having? -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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 . |
#4
|
|||
|
|||
join with different number of rows
Do this in a Report. You can set the textbox that is bound to third column
from the query to not show duplicate values. -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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. "Ken Snell" wrote: Any thoughts about what? You haven't told us what is your question, or what is the problem that you're having? -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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 . |
#5
|
|||
|
|||
join with different number of rows
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 "Ken Snell" wrote: Do this in a Report. You can set the textbox that is bound to third column from the query to not show duplicate values. -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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. "Ken Snell" wrote: Any thoughts about what? You haven't told us what is your question, or what is the problem that you're having? -- Ken Snell http://www.accessmvp.com/KDSnell/ "stickandrock" wrote in message ... 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 . . |
#6
|
|||
|
|||
join with different number of rows
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 stickandrock wrote: 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 "Ken Snell" wrote: 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 |
#7
|
|||
|
|||
join with different number of rows
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" wrote: 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 stickandrock wrote: 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 "Ken Snell" wrote: 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 . |
#8
|
|||
|
|||
join with different number of rows
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" wrote: 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" wrote: 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 stickandrock wrote: 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 "Ken Snell" wrote: 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 . |
Thread Tools | |
Display Modes | |
|
|