Multiple record lookup in a query

[mbentley]

I have a two tables, one that stores basic customer information and one that stores orders which are linked to customers in the main table in a one-to-many relationship. Pretty standard.

What I need to be able to is have a query look at all the orders for each customer and find out if an order was ever cancelled by that customer after a given date. The result would be a query that has two columns; one with the customer ID and one that says "Cancelled" or "Not Cancelled" for each customer (not stored in the underlying table, just part of the query).

Can anyone help me with this?

I should clarify that the sequence of the Cancelled order is important. For example, if a customer places 4 orders and cancels the third, the query would return 4 records for that customer. The first 2 would say "Not Cancelled" and the last 2 would say "Cancelled".

Don't ask me why I need to do this. The explanation is way too long. Suffice it to say that I have to.

 

[RichMorrison]

Mike,

I assume that Order records have some kind of date and you also know the "cancel" date. Can there be more that one cancel date ? Let's hope not.

So you want to find all Orders with an order date 'GE' the cancel date.

I think this can be done with a subselect query, also called a nested query. Access Help can show you some examples. I don't know the technique well enough to give you a solution.

HTH (a little),
RichM

 

[mbentley]

Thanks for the reply. I've never heard of or used a nested query. Sounds like it's time I did. Thanks for pointing me in the right direction.

 

[RichMorrison]

Mike,

I had a second idea ( that's scary ).

Assuming you have some field for "cancel date", you could make a query that selects:
Customer ID,
Cancel Date

Then make a second query that selects
Customer ID
Order Number
Order Date

Make a third query that joins the first 2 queries on Customer ID. This will duplicate the Cancel Date in each returned row. You can compare the Cancel Date in each row to the Order Date of that row. If Order date is greater than or equal to Cancel Date then this order was cancelled.

Should work I think.

RichM

 

[mbentley]

Awesome!

Great idea Rich. That will do the trick exactly. Thanks a bunch.