How can I create a Query from a table with multiple order types and reference numbers

[tesleen2025]

Hi,
I have a table that I access through ODBC. I can not edit or modify this table in any way. This table has multiple order types. Some order types have a reference id back to another order type. I would like to get the actual order number for those reference id's. I hope this makes sense. Here is a sample of what I want to do.

I'm not sure what I need to do to get the results I'm looking for.

Current Table
Type	OrderID	OrderNumber	Amount	RefOrderID
Invoice	123456	401-1234	$$$	89645
Invoice	123789	401-1235	$$$	87658
Repair	89645	401-6785	$$$
Repair	87658	401-6786	$$$
Warranty	646803	401-0033	$$$	89645
Warranty	646899	401-0034	$$$	87658
What I want my filtered Query to look like
Type	OrderID	OrderNumber	Amount	RefOrderID	RefOrderNumber
Warranty	646803	401-0033	$$$	89645	401-6785
Warranty	646899	401-0034	$$$	87658	401-6786

<tbody>
</tbody>

Respectfully,
TSmith

 

[Micron]

The left 5 fields look easy enough - the query would filter on Type (which probably isn't the real name of the field since that's a reserved word in Access and shouldn't be used as such). As for the rightmost field, you need to include and join the other table, but I cannot advise what fields you'd join on as you provided no information about it. Perhaps it is an order reference ID field that exists in both tables.

Some order types have a reference id back to another order type

doesn't provide much info. Your results may not exactly resemble your example because of that join, so additional joins or criteria or a different join may be required. I presume you're OK with linking the ODBC tables to your database in the first place.

 

[xenou]

A self-join might work:

SELECT 
	t1.[Type], 
	t1.OrderID, 
	t1.OrderNumber, 
	t1.Amount, 
	t1.RefOrderID, 
	t2.OrderNumber as RefOrderNumber
FROM 
	MyTable AS t1
	 LEFT JOIN
	 MyTable AS t2
	 ON t1.RefOrderID = t2.OrderID

 

[tesleen2025]

Xenou, I'm not familiar with script in Access however I was able to bring the table in a second time and create a self-join and it worked. I would not have thought to do that without your suggestion. Thank you!