How to set up this query?

[froggyface88]

I'm new to access and am struggling to set up a query to return the information I need.

Here's the scenario:
I have ~100 individuals who order items from me.
Each individual has ordered many items from me over time.
Each individual is only able to place one order per year, but multiple individuals could place an order in any given year.
I want to return the average age of each individual over their last 10 orders.

I have the following tables set up
-individual_ID, individual name
-item_ID, item name


-individual_ID & order year (indexed together), age

What is the best way to go about this?

Thank you!

[burrina]

To get an average you can use this as an example.

Avg([Freight])

To get 1 years worth of data you can use this as an example.

If used in code:
YourDateField = DateAdd("yyyy", -1, Date)


If used in a query or controlsource:


DateAdd("yyyy", -1, Date())

HTH

[froggyface88]

Thanks for your reply burrina.

My problem is that the year isn't clear. For example, I may have one customer who purchases every year (2005, 2006, 2007...etc) and another who purchases every other (2004, 2006, 2008...etc). I still need to get the past 10 order dates for each, but over a varying period of time. What approach do you recommend?

[ItsMe]

Perhaps you can implement SELECT TOP 10 in a query

[froggyface88]

Would that give me the top 10 for each customer, or the most recent 10 years where anything was ordered, regardless of customer?

Thanks!

[ItsMe]

Maybe you can create a query and then, while in SQL view, add TOP 10

So maybe something like
SELECT TOP 10 tblCustomers.CustomerID, tblCustomers.FirstName
FROM tblCustomers