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

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

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
.

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

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
.

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
.