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
|
|||
|
|||
Query-matching data with multiple choices
I have two very large tables of data. One is a table of vehicles that are on
order and have an order number. The other is a table of vehicles that are being produced at a future date and have a special vehicle number. I need to match these tables by their model and then to take lowest order number and vehicle number and give me this as a match. There could be more orders then there are vehicles being produced or vice versa. i will also need to see the production date which is in the vehicles table. For example Table1.Vehicle on order Table2.Vehicles to be produced field1-Model Field1-Model field2-Order number Field2-Vehicle Number Field3-Production Date Table1 Table2 Field1 Field2 Field1 Field2 Production Dt A 1110005 A BTX0001 12-1-09 A 1110006 A BTX0004 12-3-09 B 1110010 B BTX0008 11-30-09 C 1110020 B BTX0010 1-1-10 D 1111560 B BTX0011 12-15-09 C BTX0013 12-1-09 The result I want: A 1110005 BTX0001 12-1-09 A 1110006 BTX0004 12-3-09 B 1110010 BTX0008 11-30-09 C 1110020 BTX0013 12-1-09 Hope this makes sense. Appreciate any help -- Jul |
#2
|
|||
|
|||
Query-matching data with multiple choices
It looks to me as if you would need two ranking queries to rank the Vehicle on
Order and the vehicles to be produced by model and Order Number / vehicle number respectively. Then you can join the two ranking queries on model and rank to get the desired results. Ranking query to for Vehicle on Order SELECT Vo1.Model, Vo1.[Order Number], Count(Vo2.Model) as Rank FROM [Vehicle on Order] as Vo1 LEFT JOIN [Vehicle on Order] as Vo2 On Vo1.Model = Vo2.Model AND Vo1.[Order Number] Vo2.[Order Number] GROUP BY Vo1.Model, Vo1.[Order Number] You need a similar query for Vehicles to be Produced. Then use these two queries as they were tables and join the two queries on Model and Rank. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Solv123 wrote: I have two very large tables of data. One is a table of vehicles that are on order and have an order number. The other is a table of vehicles that are being produced at a future date and have a special vehicle number. I need to match these tables by their model and then to take lowest order number and vehicle number and give me this as a match. There could be more orders then there are vehicles being produced or vice versa. i will also need to see the production date which is in the vehicles table. For example Table1.Vehicle on order Table2.Vehicles to be produced field1-Model Field1-Model field2-Order number Field2-Vehicle Number Field3-Production Date Table1 Table2 Field1 Field2 Field1 Field2 Production Dt A 1110005 A BTX0001 12-1-09 A 1110006 A BTX0004 12-3-09 B 1110010 B BTX0008 11-30-09 C 1110020 B BTX0010 1-1-10 D 1111560 B BTX0011 12-15-09 C BTX0013 12-1-09 The result I want: A 1110005 BTX0001 12-1-09 A 1110006 BTX0004 12-3-09 B 1110010 BTX0008 11-30-09 C 1110020 BTX0013 12-1-09 Hope this makes sense. Appreciate any help |
#3
|
|||
|
|||
Query-matching data with multiple choices
Ok, I created the Query to Rank the Vehicles on Order by Model and a query to
rank by Vehicle number. Here is the SQL for ranking the Models: SELECT [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON, Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] INNER JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]=[VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON; However, the result of each ranking query is not showing the lowest number. In this query it is not showing the lowest VON#(Order number). If I have a 3 of the same models, AAA-01-C-GG1, and von #'s 111130000, 111130001,111130002, the ranking result just counts 111130001 and 111130002. The same thing happens with the VTN ranking query -- Jul "John Spencer" wrote: It looks to me as if you would need two ranking queries to rank the Vehicle on Order and the vehicles to be produced by model and Order Number / vehicle number respectively. Then you can join the two ranking queries on model and rank to get the desired results. Ranking query to for Vehicle on Order SELECT Vo1.Model, Vo1.[Order Number], Count(Vo2.Model) as Rank FROM [Vehicle on Order] as Vo1 LEFT JOIN [Vehicle on Order] as Vo2 On Vo1.Model = Vo2.Model AND Vo1.[Order Number] Vo2.[Order Number] GROUP BY Vo1.Model, Vo1.[Order Number] You need a similar query for Vehicles to be Produced. Then use these two queries as they were tables and join the two queries on Model and Rank. John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Solv123 wrote: I have two very large tables of data. One is a table of vehicles that are on order and have an order number. The other is a table of vehicles that are being produced at a future date and have a special vehicle number. I need to match these tables by their model and then to take lowest order number and vehicle number and give me this as a match. There could be more orders then there are vehicles being produced or vice versa. i will also need to see the production date which is in the vehicles table. For example Table1.Vehicle on order Table2.Vehicles to be produced field1-Model Field1-Model field2-Order number Field2-Vehicle Number Field3-Production Date Table1 Table2 Field1 Field2 Field1 Field2 Production Dt A 1110005 A BTX0001 12-1-09 A 1110006 A BTX0004 12-3-09 B 1110010 B BTX0008 11-30-09 C 1110020 B BTX0010 1-1-10 D 1111560 B BTX0011 12-15-09 C BTX0013 12-1-09 The result I want: A 1110005 BTX0001 12-1-09 A 1110006 BTX0004 12-3-09 B 1110010 BTX0008 11-30-09 C 1110020 BTX0013 12-1-09 Hope this makes sense. Appreciate any help . |
#4
|
|||
|
|||
Query-matching data with multiple choices
You need an outer join LEFT JOIN (or RIGHT JOIN) instead of an INNER JOIN.
SELECT [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON , Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] LEFT JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]= [VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Solv123 wrote: Ok, I created the Query to Rank the Vehicles on Order by Model and a query to rank by Vehicle number. Here is the SQL for ranking the Models: SELECT [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON, Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] INNER JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]=[VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON; However, the result of each ranking query is not showing the lowest number. In this query it is not showing the lowest VON#(Order number). If I have a 3 of the same models, AAA-01-C-GG1, and von #'s 111130000, 111130001,111130002, the ranking result just counts 111130001 and 111130002. The same thing happens with the VTN ranking query |
#5
|
|||
|
|||
Query-matching data with multiple choices
Works! Thank you so much!
-- Jul "John Spencer" wrote: You need an outer join LEFT JOIN (or RIGHT JOIN) instead of an INNER JOIN. SELECT [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON , Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] LEFT JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]= [VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Solv123 wrote: Ok, I created the Query to Rank the Vehicles on Order by Model and a query to rank by Vehicle number. Here is the SQL for ranking the Models: SELECT [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON, Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] INNER JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]=[VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON; However, the result of each ranking query is not showing the lowest number. In this query it is not showing the lowest VON#(Order number). If I have a 3 of the same models, AAA-01-C-GG1, and von #'s 111130000, 111130001,111130002, the ranking result just counts 111130001 and 111130002. The same thing happens with the VTN ranking query . |
#6
|
|||
|
|||
Query-matching data with multiple choices
I have now realized another scenario. I have VON #'s (orders) in my
VPT_Totals Formatted Query but I have zero vehicles being produced in my VTN table. When I compare the two ranking tables it is only showing results if there is a model item in each table/query and then I am showing the difference (VTN-VPT). I also need to see in my result if there is no match. Example, VPT_Totals Formatted has model item AAA-01-C-CBS but the VTN table does not have that model. I need to see included in my results the model AAA-01-C-CBS=-1. I know how to show this in an unmatch query but i would like this to be incorporated into the same query showing the ranks and the differences. -- Jul "John Spencer" wrote: You need an outer join LEFT JOIN (or RIGHT JOIN) instead of an INNER JOIN. SELECT [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON , Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] LEFT JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]= [VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted] , [VPT_Totals Formatted].VON; John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Solv123 wrote: Ok, I created the Query to Rank the Vehicles on Order by Model and a query to rank by Vehicle number. Here is the SQL for ranking the Models: SELECT [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON, Count([VPT_Totals Formatted_1].[Model Code Formatted]) AS RANK FROM [VPT_Totals Formatted] INNER JOIN [VPT_Totals Formatted] AS [VPT_Totals Formatted_1] ON ([VPT_Totals Formatted].VON[VPT_Totals Formatted_1].VON) AND ([VPT_Totals Formatted].[Model Code Formatted]=[VPT_Totals Formatted_1].[Model Code Formatted]) GROUP BY [VPT_Totals Formatted].[Model Code Formatted], [VPT_Totals Formatted].VON; However, the result of each ranking query is not showing the lowest number. In this query it is not showing the lowest VON#(Order number). If I have a 3 of the same models, AAA-01-C-GG1, and von #'s 111130000, 111130001,111130002, the ranking result just counts 111130001 and 111130002. The same thing happens with the VTN ranking query . |
Thread Tools | |
Display Modes | |
|
|