Query to Average data based on date from another table

[tralbert]

I am working with a DB where I have a table (KDS_INFO) that has the following
fields:

KDSNumber - AutoNumber, primary key
ANumber - Foreign key, employee number from another table
KDSAmount - 3 digit number w/ 2 decimal places
KDSDate - short date/time

In this DB I also have a table CLASS that lists the dates of hire, placement
and 90-day completion dates (short date).

For each ANumber, I need a 30-day, 60-day and 90-day average of the
KDSAmounts where the KDSDates are compared to the PlacementDate and
90DayCompletDate in the CLASS table. So based on the PlacementDate it will
count however many KDSAmounts are between days 1-30, 31-60 and 61-90 and
average accordingly. There will be instances where there will be no
information to average in a time period. In those instances I need the query
to state "N/A".

If I need to provide any more info on this, please let me know.

Thanks!

 

[tralbert]

To update, I changed the CLASS table to include 30-, 60- and 90-day
completion dates to help make things easier to create averages based on those
time parameters.