Query Order History (1 Viewer)

[LadyDi]

I have a table that contains order information based on order number. In that table, I also have a field labeled ReplacingQuote so that when one order is entered to replace another, we can keep track of it. Now, I want to create a query that will show this history, but I can't seem to get it to work. I want it to show me any record where a ReplacingQuote equals an order order number -- then I'll be able to see the full history of the order (i.e. order 123456 is the original quote, it was replaced by order 1245678 which was replaced by order 13456789 -- in this case, I would want to see three records in my query - one for the original order and one for each of the sequential orders). Is this possible?

Right now, I have the below query, but it will only show me one record.

SELECT QuoteInfoTbl.Quote_Num, QuoteInfoTbl.Replacing_Quote, QuoteInfoTbl.Customer, QuoteInfoTbl.Quote_Date, QuoteInfoTbl.Model
FROM QuoteInfoTbl
WHERE (((QuoteInfoTbl.Quote_Num) In (DLookUp("Quote_Num","QuoteInfoTbl","Replacing_Quote"))));

 

[Ranman256]

Do NOT use DLOOKUP in a query. The query IS the dlookup.
If you need to lookup in a table, attach the table into the query.

for you problem tho, try starting simple, using 1 record that you know.
select * from table where [replacedOrder] = '1245678'

then add another table/criteria to get what you want.
When you do, remove the 1 limiting example so you get everything.

 

[arnelgp]

SELECT A.Quote_Num, B.Replacing_Quote, A.Customer, A.Quote_Date, A.Model
FROM QuoteInfoTbl A LEFT JOIN QouteInfoTbl B ON A.Qoute_Num = b.Replacing_Quote

 

[Minty]

This requires a recursive query which isn't possible in a Access using the query designer to the best of my knowledge.

You can mimic this in VBA if you are comfortable with a bit of programming.