Query-matching data with multiple choices

  • Thread starter Thread starter Solv123
  • Start date Start date
S

Solv123

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
 
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
 
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 said:
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
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
Click to expand...
.
Click to expand...
 
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
 
Works! Thank you so much!
--
Jul


John Spencer said:
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
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
Click to expand...
.
Click to expand...
 
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 said:
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
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
Click to expand...
.
Click to expand...
 
Back
Top